[SQL] Why an insert on a certain table is slow?


It is a good topic database question to discuss. There are many reasons why an insert on a certain table takes a little longer than usual. The online article that I found has listed a few reasons. Let me look into it:

  • Existence of INSERT TRIGGERs on the table
  • Lots of enforced constraints that have to be checked (usually foreign keys)
  • Page splits in the clustered index when a row is inserted in the middle of the table
  • Updating all the related non-clustered indexes
  • Blocking from other activities on the table
  • Poor IO write response time

In the same article, some of the replies suggested doing the following:

  • Reduce the batch size from 10000 to something smaller, like 2000 or 1000. In SSIS, reducing the batch size according to the allocated buffer memory size is a great help. It may take a while but at least it lowers the chances of getting batch processing errors that resulting from the failed insertion.
  • Try turning on IO Stats to see just how much IO the FK lookups are taking.
  • Run the query with SET STATISTICS IO ON and SET STATISTICS TIME ON to see the execution time and read/write io for each insert.

References

https://dba.stackexchange.com/questions/482/how-can-i-tell-why-an-insert-on-a-certain-table-is-slow

Author: liyenz

A bit about myself, I love to eat and take pictures of food and share it with everyone. I do some writing on my food hunting experience but I am not a blogger who write reviews for the restaurants or websites. Just have fun while viewing and reading my blog.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: