SQLBulkCopy Performance Statistics

The following C # code uses SqlBulkCopy to upload large data volumes:

Public void SaveDataTable (string connectionString, String tableName, int batchSize, DataTable table)
{
Using (SqlConnection sqlConnection = new SqlConnection (connectionString ))
{
SqlConnection. Open ();
Var sqlTransaction = sqlConnection. BeginTransaction ();
Try
{
// The SqlBulkCopyOptions and ExternalTrasaction parameters can be used to control transactions.
Using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy (sqlConnection, SqlBulkCopyOptions. TableLock, sqlTransaction ))
{
SqlBulkCopy. BatchSize = batchSize;
SqlBulkCopy. BulkCopyTimeout = 900;
SqlBulkCopy. DestinationTableName = tableName;
SqlBulkCopy. WriteToServer (table );
SqlTransaction. Commit ();
}
}
Catch (Exception)
{
SqlTransaction. Rollback ();
Throw;
}
}
}

The following is the statistical data (in milliseconds) used for this method ):

10000 data records each time:

Bulkcopy: 4718.6594

Bulkcopy: 1921.8381

Bulkcopy: 2437.4532

Bulkcopy: 1906.2134

Bulkcopy: 2640.5743

Bulkcopy: 2578.0755

Bulkcopy: 2734.3225

Bulkcopy: 2406.2038

Bulkcopy: 2671.8237

Bulkcopy: 2578.0755

Bulkcopy: 2531.2014

Bulkcopy: 2046.8357

Bulkcopy: 2078.0851

Bulkcopy: 2421.8285

Bulkcopy: 2593.7002

Bulkcopy: 2656.199

Bulkcopy: 2640.5743

Bulkcopy: 2421.8285

Bulkcopy: 2578.0755

Bulkcopy: 2593.7002

Bulkcopy: 2890.5695

 

50000 data records each time:

Bulkcopy: 22030.827

Bulkcopy: 6640.4975

Bulkcopy: 7156.1126

Bulkcopy: 8171.7181

Bulkcopy: 7109.2385

Bulkcopy: 6718.621

Bulkcopy: 6734.2457

Bulkcopy: 6187.3812

Bulkcopy: 6734.2457

Bulkcopy: 6937.3668

Bulkcopy: 6796.7445

Bulkcopy: 6812.3692

Bulkcopy: 10327.9267

Bulkcopy: 7859.2241

Bulkcopy: 7624.8536

Bulkcopy: 7390.4831

 

100000 data records each time:

Bulkcopy: 6296.7541

Bulkcopy: 6843.6186

Bulkcopy: 32311.8796

Bulkcopy: 8062.3452

Bulkcopy: 7702.9771

Bulkcopy: 7577.9795

Bulkcopy: 8109.2193

Bulkcopy: 8577.9603

Bulkcopy: 7734.2265

Bulkcopy: 7499.856

Bulkcopy: 7593.6042

Bulkcopy: 7656.103

Bulkcopy: 7765.4759

 

200000 data records each time:

Bulkcopy: 16138.3021

Bulkcopy: 13833.7382

Bulkcopy: 14786.1739

Bulkcopy: 17549.7988

Bulkcopy: 15629.3137

Bulkcopy: 7135.4609

Bulkcopy: 7307.2116

Bulkcopy: 9368.22

Bulkcopy: 8931.0364

Bulkcopy: 10008.3817

Bulkcopy: 8571.9213

Bulkcopy: 9055.946

Bulkcopy: 37832.6585

 

500000 data records each time:

Bulkcopy: 31163.097

Bulkcopy: 26992.3968

Bulkcopy: 26211.3668