My application is very INSERT heavy. What can I do to speed up the performance of INSERTs?

Here are a variety of tips that can help speed up INSERTs.

1) Use RAID 10 or RAID 1, not RAID 5 for the physical disk array that stores your SQL Server database. RAID 5 is slow on INSERTs because of the overhead of writing the parity bits. Also, get faster drives, a faster controller, and consider turning on write caching on the controller if it is not already turned on (although this has its disadvantages, such as lost data if your hardware fails).

2) Ensure that your server is not starved for memory.

3) The fewer the indexes on the table, the faster INSERTs will be.

4) Try to avoid page splits. Ways to do this include having an appropriate fillfactor and pad_index for you indexes, rebuilding indexes often, and consider adding a clustered index on an incrementing key for the table (this forces pages to be added one after another, and page splits are not an issue).

5) Keep the columns widths as narrow as possible.

6) If data length in a column is consistent, use CHAR columns, or if data length varies a lot, use VARCHAR columns.

7) Try to batch INSERTs rather than to INSERT one row at a time. But this can also cause problems if the batch of INSERTs is too large.

None of these suggestions will radically speed up your INSERTs by themselves, but put together, they all will contribute to overall faster INSERTs.


No comments yet... Be the first to leave a reply!