bulkcopy batch size affecting the insert

c# database datatable sqlbulkcopy sql-server

Question

I am using an SqlBulkCopy to insert the contents of a datatable of one million records (each record has around 10 columns) into a database table. I see some unstable values for the duration of the bulk copy as I change the batch size property (bulkCopy.BatchSize).

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.CheckConstraints))
{
    bulkCopy.DestinationTableName = destinationTableName;
    bulkCopy.BatchSize = 100000;
    bulkCopy.BulkCopyTimeout = 1800;
    if (matchingColumns != null || matchingColumns.Count > 0)
    foreach (KeyValuePair<string, string> kv in matchingColumns)
        bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(kv.Key, kv.Value));
    bulkCopy.WriteToServer(dt);
}

When I used no batch size, the process was done in 7 minutes. Then I set the batch size to 100,000 and the time was around 5:30 and then I set it to 50,000 and the time increased to 10 minutes.

I would like to know the effect of batch size on the INSERT speed. Does it, in general, make things faster or slower?

Accepted Answer

Take a look at this whitepaper that tests batch sizes extensively:

SQLBulkCopyPerformance

There is a lot of data in there regarding network utilisation etc using different batch sizes, but the conclusion is as follows:

We did not find anything mentioned in the Books OnLine nor have we ever seen anything interesting during our experience, this lead us to say that the best thing to do with BatchSize is to leave it to zero, which is its default value, since any value different from that will decrease the performance of the load process

Which seems to concur with your testing.

EDIT:

Whenever I have used SqlBulkCopy in the past, I tend to control the batch size myself by doing multiple inserts, then just the NotifyAfter property and SqlRowsCopied event to get progress updates as the copy progresses.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why