What is the recommended batch size for SqlBulkCopy?

Question

What is the recommended batch size for SqlBulkCopy ? I am looking for a general formula that I can use as a starting point for tuning performance.

Answer 1

I have an import utility sitting on the same physical server as my instance of SQL Server. Using a custom IDataReader , it parses flat files and inserts them into the database using SQLBulkCopy . A typical file has about 6M qualified lines, on average 5 columns of decimal and short text, about 30 bytes per line.

Given this scenario, I found that a batch size of 5000 would be the best compromise in speed and memory consumption. I started with 500 and experimented with big ones. I found that 5000 will be 2.5 times faster, on average, than 500. Inserting 6 million rows takes about 30 seconds with a lot size of 5000 and about 80 seconds with a lot size of 500.

10,000 was not significantly faster. Moving to 50,000 improved speed by a few percentage points, but it was not worth the increase in server load. Above 50,000 showed no improvement in speed.

This is not a formula, but another data point for you.

Answer 2

This is a problem that I also spent some time studying. I want to optimize the import of large CSV files (16+ GB, 65 million records and growth) into a SQL Server 2005 database using the C # console application (.Net 2.0). Since Jeremy has already been indicated, you will need to fine-tune it for your specific circumstances, but I would recommend that you have an initial batch size of 500 and test values ​​both above and below that.

I got a recommendation for testing values ​​from 100 to 1000 for a batch size from this MSDN forum post and was skeptical. But when I tested the batch size from 100 to 10000, I found that 500 is the optimal value for my application. A value of 500 for SqlBulkCopy.BatchSize also recommended here .

For further optimization of the SqlBulkCopy operation, check out the MSDN tip; I believe that using SqlBulkCopyOptions.TableLock helps reduce load times.

Answer 3

As others have argued, this depends on your environment, in particular on line volume and network latency.

Personally, I would start by setting the BatchSize property to 1000 lines and see how it works. If it works, I continue to double the number of lines (for example, to 2000, 4000, etc.) until I get a timeout.

Otherwise, if the timeout occurs at 1000, I reduce the number of lines by half (for example, 500) until it works.

In each case, I continue to double (if necessary) or halve (if not succeed) the difference between each of the last two sizes of the batch of purchases until I find a sweet spot.

Another factor to consider is how long it takes to copy one batch of lines. Timeouts will be executed if the batch of lines copied exceeds the BulkCopyTimeout property, which is 30 seconds by default. You can try doubling the BulkCopyTimeout property to 60 seconds. This allows a longer period of time for copying a large set of batch lines. For example, a packet of 50,000 lines can take about 40 seconds, exceeding only a 30-second period, so a load of 60 seconds can help in performance.

Answer 4

It all depends on your implementation.

What speed can you expect on your network? Do you use it in Forms or ASP.Net? Do you need to warn the user about progress? What is the size of the overall work?

In my experience, working with a bulk copy without a specified batch size will cause timeout problems. I like to start with something like 1000 records and make some adjustments from there.