Table insert times

[grimwyre]

On one of our databases, an insert of 50 rows takes 60-90 seconds for the first insert. The rows are then deleted. Subsequent inserts of the same data take almost no time, when repeated soon afterwards.

If this sequence is repeated the next day, then the initial delay occurs again.

All done via SQL Query Analyser. The server is running 24X7.

 

[roccorocks]

Do any indexes get dropped during this insert? If so, that would probably be the issue. What does your insert qry or SP look like? What indexes do you have on the table? Remember, the more indexes you have the potential for slower inserts.

Rocco

 

[SQLBill]

In layman's terms, here's how SQL Server processes queries, etc.

A query is run (SELECT, UPDATE, DELETE, INSERT),
SQL Server processes the query,
SQL Server pulls the data into memory (RAM),
it then holds that data in memory for a period of time (no, I don't know how long) in case you run the same or similar query.
Eventually it releases the data, for example if a different query is run.

In your case, I think you are running the INSERT. The information is being held in RAM. You DELETE the data. Again held in RAM. For a short while that information is available so that a subsequent INSERT will be quicker.

But then the data 'goes away' and other data is placed in RAM so the next 'repeat INSERTs' take longer.

I know it's not a technical explanation and I hope I got it all correct.

-SQLBill

 

[grimwyre]

Thanks for the input. We suspected it might be something along those lines.

The table has 5 indexes (1 PK on 6 columns), about 225000 rows. This database is not used much, but resides on a server with a very live database, which exhibits the same symptoms.

The insert is of the form INSERT INTO

([col1], [col2], [col3] etc)
VALUES('x', 'x', 0 etc')

Repeat N times changing the "0" value as that column must be unique.