We're doing some testing and this involves adding 10.000 rows every 10seconds in a table that has 4 int columns and one of them is clustered index. When we run the app the first bulkcopy takes around 1 minute to complete. The second takes 40 seconds to complete and after about 8 bulkcopy the time to complete the transaction is 1-2 seconds. Every transaction has 10.000 rows with random numbers. Is there anyway to prepare the database for fast bulkcopy from the first transaction?
1/ The first I have in mind is that time is spent on checking FK constraint in your destination table while new rows are inserted. Firstly, I would check if referenced tables have PKs defined (or even clustered from performance point of view). Give it a try by performing some bulk inserts after you have disabled those FKs.
2/ Statistics might not be updated on referenced table, case when a statement like this should be executed for each referenced table:
UPDATE STATISTICS tablename;
3/ Long term, you should take into consideration partitioning that table. This is the perfect scenario for doing this.
4/ Disable indexes on destination table, perform bulk insert then re-enable the indexes. Not-recommended, but give it try to see the results and to see if this is the root cause of your poor performance for bulk inserts.
5/ Have a look at waiting threads while a bulk insert is executing.
6/ Post the result of wait statistics in order to see which types of waits are commonly arrive on your server.
Tell us the result and we'll come with more details depending on the context.
As people have pointed out, the discrepancy between bulk inserts is hard to explain. The only possible scenario when mdf file needs to grow and takes extra time to allocate the space but that should only happen for 1 bulk insert instead of first few.
Below is few possible way of speeding up bulk insert operation
- Insert into heap table - table without clustered index defined.
- Enable "Instant File Initialization" - should help time it takes to grow database files
- utilize multiple data files for database
These are in addition of best practices for database maintenance that does help with performance of the box.