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();
}
SqlBulkCopy is already VERY fast.
20k records should not take 40 seconds.
Here is some technique to improve SqlBulkCopy 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.