MS SQL Server插入速度使用SqlCommand

Question

我有两个表，有时我必须添加很多行。最后一个例子是表1中的800000行，表2中的800000行是表2的3倍。

我使用以下存储过程插入行，因为考虑到表有自动id字段和外键关系，我看不到使用大容量复制的方法。

CREATE PROCEDURE dbo.AddOrderBookEntry 
 @Moment datetime,
 @LocalTime datetime,
 @BB decimal(18,4),
 @BO decimal(18,4),
 @QBB float,
 @QBO float,
 @SumTr float = NULL,
 @QSumTr float = NULL,
 @IV float = NULL,
 @InstrumentId bigint,
 @AverageValues Averages READONLY
AS
BEGIN
 INSERT INTO dbo.OrderBook
 VALUES (@Moment,@LocalTime,@BB,@BO,@QBB,@QBO,@SumTr,@QSumTr,@IV,@InstrumentId)
 DECLARE @OBID bigint
 SELECT @OBID = SCOPE_IDENTITY()
 INSERT INTO dbo.OrderbookAverages
 select N, BN, [ON], @OBID from @AverageValues
END
GO

它工作，但困扰我的是速度。根据我的测量，添加一条记录需要1.75毫秒。我正在测量从。net应用程序写入数据到数据库的速度。此应用程序与SQL Server在同一台计算机上。

所以问题是-这个速度对我使用的方法来说合适吗?还是可以改进?

Answer 1

大约20分钟记录800.000条记录不是很快，但是只有您可以决定它是否足够快。

您可以通过使用两步过程来避免使用批量插入。首先使用大容量插入将数据加载到两个表中，然后通过查找第一个表的自动id将它们连接起来。也许可以给匹配的行分配一个事先生成的id (guid也可以)。

这可能会快得多，但您必须考虑是否值得花时间在上面。例如，您多久运行一次这些导入?如果你每天做五次，那么任何加速都是值得的。如果你每个月做一次，那么可能不会:-)

Answer 2

通常需要将业务代码中的大量行发送到数据库。有很多方法可以做到这一点:

对整个数据一次一行地调用插入语句将数据序列化为某种平面格式(CSV或XML)，将其作为大字符串发送到存储过程，在存储过程TSQL中对字符串进行反序列化，然后执行插入操作。在数据库服务器上将数据保存为平面文件格式。运行DTS包或喜欢读取文件。SqlBulkCopy !

自从我发现了SqlBulkCopy，我就爱上了它。MS SQL Server包含一个名为bcp的流行命令，用于在服务器上或服务器之间将数据从一个表移动到另一个表。SqlBulkCopy是一个提供类似功能的类。

SqlBulkCopy比多个插入语句、序列化/反序列化数据或将数据保存到文件系统并运行导入要快得多。它对你可以发送的数据也没有限制，并且在处理插入的方式上非常有效。

使用它就是这么简单。在这个例子中，我们有一个函数将DataTable的副本写入一个名为"tblFooBar"的MS SQL数据库表。

 using System.Data.SqlClient;
…
Function WriteToDB(DataTable dt)
{
    SqlBulkCopy sqlBC = new SqlBulkCopy(dbconnectionstring);
    sqlBC.BatchSize = 25000;
    sqlBC.BulkCopyTimeout = 60;
    sqlBC.DestinationTableName = “dbo.tblFooBar” ;
    sqlBC.WriteToServer(dt);
}
…

MSDN链接为:

大容量插入的详细信息