Inserting taking longer on Sql Server 2005 table

Question

I have a table with about 45 columns and as more data goes in, the longer it takes for the inserts to happen. I have increased the size of the data and log files, reduced the fill factor on all the indexes on that table, and still slower and slower insert times. Any ideas would be GREATLY appreciated.

Answer 1

• For inserts, you want to DECREASE the fillfactor on the indexes on the table in order to reduce page splitting.

• It is somewhat expected that it will take longer to insert as more data goes in, because your indexes just plain get bigger.

• Try putting in data in batches instead of row-by-row. SQL Server is more efficient that way.

• Make sure you don't have too many indexes on your tables.

• Consider using SQL Server 2005's INCLUDE statement on your indexes if you are just including columns in your indexes because you want them covered in your queries.

Answer 2

How big is the table?

What is the context? Is this a batch of many new records?

Can you post the schema including index definition?

Can you SET STATISTICS IO ON, SET STATISTICS TIME ON, and post the display for one iteration?

Is there anything pathological about the data, or the context? Is this on a server or a laptop (testing)?

Answer 3

Why dont you drop index before inserting and recreate index back on to table so you no need to do update statistics