2

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?

  • One minute to insert 10k rows?! That is very suspicious. Is the database under load from other queries at the time? Is there something else going on on the server? Are you sure of the timing and of the number of rows? You should get 50-400k rows/sec with a table with 4 int columns according to my experience. Can you script out the table with indexes please. Are there triggers? – usr Jan 3 '14 at 22:38
  • 1
    Inserting into a heap table (no clustered index) will be the fastest. Try rebuilding it after the load or using staging database/table. See link stackoverflow.com/questions/7184067/… – sam yi Jan 3 '14 at 22:46
  • Inserting into a heap might be faster but why does it take 100 times longer than it should?! That is far more important than optimizing it by 20%. – usr Jan 3 '14 at 23:06
  • The table already has about 300.000.000 records inside! Yes the numbers are correct and no other transaction is made on the server. The problem is that the first 5-10 bulkinserts are slow but after that the time falls to 1-2 seconds for each bulkcopy – George Jan 3 '14 at 23:06
  • Oh ok that makes more sense now. Probably your inserts are touching certain database pages that are brought into cache over time. Please respond to the other questions I asked. – usr Jan 3 '14 at 23:08
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy