单个记录上的SqlBulkCopy? [英] SqlBulkCopy on a single record?

问题描述

我的软件可以按两种不同的配置进行部署:一种在一天中偶尔收到点滴的单个数据点，另一种在一天结束时我得到所有数据的转储.对于一天结束的转储，显然，我将使用具有可配置批处理大小的 SqlBulkCopy()命令.

但是，为简单起见，我想通过将批处理大小设置为1来在点滴式供稿中使用相同的代码.这样做是否会产生大量开销?我会更好地执行一次 INSERT 的点进式调用吗?

工作流程如下:

  ICollection< MyClass>dataPoints = ...;公共无效AddDataPoint(MyClass数据){dataPoints.Add(data);如果(dataPoints.Count> = ConfigurableBatchSize){DoBulkCopy(dataPoints);//将MyClass对象转换为DataTable的行，等等}} 

解决方案

您会乘火车去附近的杂货店吗?您可以将批量复制用于偶尔的单行插入，但是我建议如果单行是规则而不是例外(例如您的用例)，则建议您不要这样做.就是说，如果插入的内容很少，您可能根本不会注意到开销.您要避免的主要事情是连续的单行批处理流.

几年前，我在有关最大化SQL Server插入性能的SQL Saturday会话中进行了各种插入方法和大小的许多性能测试.使用singe-thread应用程序，单行参数化插入的速率约为每秒2200，而带有DataTable源的singe-row SqlBulkCopy约为500/sec(服务器CPU时间增加了一倍).您可以从 sqlsaturday> com/154/schedule.aspx 下载Power Point.>

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
    }
}

解决方案

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.

这篇关于单个记录上的SqlBulkCopy?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！