Clever Geek Handbook

Insert will take longer in Sql Server 2005 table

I have a table containing about 45 columns and as more data comes in, the longer it will take to insert. I have increased the size of the data and log files, decreased the fill factor on all indexes on this table, and still slower and slower insert times. Any ideas would be much appreciated.

0


source to share


4 answers


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

  • It is expected to take longer to insert as more data comes in, because your indexes are just getting bigger.

  • Try putting data in batches instead of line by line. SQL Server is more efficient this way.

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

  • Consider using the SQL Server 2005 INCLUDE statement in your indexes if you are just including columns in your indexes because you want them to be covered in your queries.



+2


source


How big is the table?

What's the context? Is this a batch of many new recordings?

Can you post the schema including the index definition?



Can you set the IO STATS ON, SET THE STATS ON TIME and bring the display one iteration?

Is there something pathological about data or context? Is it on a server or laptop (testing)?

0


source


Why don't you drop the index before inserting and re-creating the index back to the table, so you don't need to update the statistics.

0


source


You can also make sure the indexes on this table are defragmented

0


source






More articles:


All Articles