CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005database-design
CSharpAtl
CSharpAtl

Reputation: 7522

Inserting taking longer on Sql Server 2005 table

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.

Upvotes: 0

Views: 231

Answers (4)

plenderj
plenderj

Reputation: 573

You could also ensure that the indexes on that table are defragmented

Upvotes: 0

Soma
Soma

Reputation:

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

Upvotes: 0

dkretz
dkretz

Reputation: 37655

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)?

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97851

  • 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.

Upvotes: 2

Related Questions