Use of SqlBulkInsert in production applications

bulkinsert c# database sql sqlbulkcopy

Question

We are currently developing an application that generates upwards of 5-10,000 rows of data in a particular table for each user session. Currently we are using sql text commands to insert each row of data at a time so a save operation could take up to a minute. We are playing around with the use of SqlBulkInserts and have seen the time go down to less than 500ms. Does anyone have any objection with the use of SqlBulkInserts in a production application where many users will be using the system?

Accepted Answer

I have never ran into an issue with SqlBulkCopy with the tableLock option set and another user being blocked due to it. The TableLock option increases the efficiency of the insert from what many people have talked about and just plain using it have shown me.

My typical method:

public void Bulk(String connectionString, DataTable data, String destinationTable)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy
            (
            connection,
            SqlBulkCopyOptions.TableLock |
            SqlBulkCopyOptions.FireTriggers |
            SqlBulkCopyOptions.UseInternalTransaction,
            null
            ))
        {
            bulkCopy.BatchSize = data.Rows.Count;
            bulkCopy.DestinationTableName = String.Format("[{0}]", destinationTable);
            connection.Open();
            bulkCopy.WriteToServer(data);
        }
    }
}

Popular Answer

Before implementing using SqlBulkInsert, try creating your INSERT query dynamically to look like this:

insert into MyTable (Column1, Column2)
select 123, 'abc'
union all
select 124, 'def'
union all
select 125, 'yyy'
union all
select 126, 'zzz'

This will be only one database call, which should run much more quickly. For the SQL string concatenation, make sure you use the StringBuilder class.



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