CODEWITHSUNDEEP
c#.netsql-serverperformance
djs
djs

Reputation: 1690

SqlBulkCopy on a single record?

My software can be deployed in two different configurations: one where I receive a trickle-feed of single data points sporadically throughout the day, and another where I get a dump of all data at the end of the day. For the end-of-day dump, obviously, I will use a SqlBulkCopy() command with a configurable batch size.

However, for simplicity, I'd like to just use the same code in the trickle-feed by setting the batch size to 1. Is there significant overhead by doing this? Would I be better off doing single INSERT calls for a trickle-feed?

The workflow looks like this:

ICollection<MyClass> dataPoints = ...;
public void AddDataPoint(MyClass data)
{
    dataPoints.Add(data);
    if (dataPoints.Count >= ConfigurableBatchSize)
    {
        DoBulkCopy(dataPoints); // converts MyClass objects into rows of a DataTable, etc
    }
}

Upvotes: 4

Views: 1408

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Would you take a train to the corner grocery store? You could use bulk copy for an occasional single-row insert but I recommend against that if the single row is the rule rather than the exception, as appears to be your use case. That said, you might not notice the overhead at all if the inserts are few. The main thing you want to avoid is a continuous stream of single-row batches.

I ran many performance tests of various insert methods and sizes for at a SQL Saturday session on Maximizing SQL Server Insert Performance a couple of years ago. With a singe-thread app, single-row parameterized inserts achieved a rate of about 2,200 per second whereas singe-row SqlBulkCopy with a DataTable source was about 500/sec (with double the server CPU time). You can download the Power Point from sqlsaturday.com/154/schedule.aspx.

Upvotes: 5

Related Questions