How to improve performance during Insertion

  • Article

Here are the following guidelines to increase performance during INSERT operation:

 1. Inserting data into a table with a single clustered index is slightly better than inserting the same data into the same table with a corresponding nonclustered index

2. If you are doing bulk copy of data, when possible, use the BULK INSERT statement instead of the bcp utility

3. Reduce potential of page splits choosing the right fill factor. If you see that page splits are high (using Performance Monitor for example), then try to set the fill factor on 90 or 80, and if you still see the counter high, try 70. Zero is the default fill factor. Don't modify that default, if you are adding data to the end of the table

4. RAID 5 is slow on insert operations. RAID 1 and RAID 1+0 do much better

5. Avoid long-running transactions. Keep transactions as short as possible

6. Adjust the size of the data and log files to avoid "autogrowth" events. If these files grow during a transaction, the operation of allocating new space will affect performance

7. Consider removing unused indexes or duplicated indexes. Updating unneeded indexes have an impact on write operations because they need to be maintained even though they are not used. The fewer indexes on the tables you are adding data, the better.

8. Consider moving the log file of the database to a separate drive from the data files. Better if that separate drive is a RAID 1 or RAID 1+0

 -Sreekar