This code is working but it is so slow for 10k records. Is there any faster way to bulk insert data into SQL Server?

c# excel sqlbulkcopy sql-server

Question

This code is working. And it is better than saving records row by row, but it is so slow for 10K records. Is there any faster way to bulk insert data into SQL Server?

Adding bulk data into database

using (var scope = new TransactionScope())
{
    string connectionString = "Data Source=94.73.148.5;" +
                              "Initial Catalog=YirmibesYazilimMutabakat;" +
                              "User id=id;Password=password;";

    var sqlConnection = new SqlConnection(connectionString);
    var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
    {
        DestinationTableName = "FaturaExcel",
        BulkCopyTimeout = 6000
    };

    var dataTable = copyExcel;
    sqlConnection.Open();

    sqlBulkCopy.WriteToServer(dataTable);

    scope.Complete();
    sqlBulkCopy.Close();
    sqlConnection.Close();
    sqlConnection.Dispose();
}

EDIT: Added BatchSize

bulkCopy.WriteToServer(dataTable); This statement takes 40 seconds. The others just fine. Additionally i could'nt use transaction.

 string connectionString ="Data Source=94.73.148.5;" +
                          "Initial Catalog=YirmibesYazilimMutabakat;"               
 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
 {
   var sqlConnection = new SqlConnection(connectionString);
   bulkCopy.BatchSize = 4000;
   bulkCopy.DestinationTableName = "FaturaExcel";
   var dataTable = copyExcel;
   sqlConnection.Open();
   bulkCopy.WriteToServer(dataTable);
   bulkCopy.Close();
   sqlConnection.Close();
   sqlConnection.Dispose();
 }

Expert Answer

SqlBulkCopy is already VERY fast.

20k records should not take 40 seconds.

Here is some technique to improve SqlBulkCopy performance:

  • Using a BatchSize may increase the performance. For example 4000
  • Using the option SqlBulkCopyOptions.TableLock will increase the performance.

EDIT: Answer comment

I already used SqlBulkCopy. Did i do something wrong

Yes, I know. My point is you will not find anything really faster than SqlBulkCopy for inserting.

How can i use BatchSize

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, transaction))
{
    // SET BatchSize value.
    bulkCopy.BatchSize = 4000;

    bulkCopy.DestinationTableName = "TheDestinationTable";
    bulkCopy.WriteToServer(dt);
}

Source: http://www.sqlbulkcopy-tutorial.net/batchsize

As said, the BatchSize may or not help. This is a bad practice to let the default batch size value (0 = unlimited) since inserting multiple batches is often faster than inserting 1 million rows at once. In your case, you only have 20k but since it takes 40s, it could worth a try.

Could it be a situation related to the SQL database

I believe your issue is perhaps more a network issue or something like this. Inserting 20k rows should take less than 1 seconds.

I recommend you to try and benchmark a few "INSERT" statement to check how much time take a database roundtrip. For example by inserting one row. I have seen some people on Azure that inserting one row was taking more than 100ms which is insanely slow.



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