将数据批量插入SQL Server

本文转载自：http://richorama.github.io/2017/11/28/sql-bulk-copy/

将数据批量插入SQL Server

假设在SQL Server中有一个简单的表

CREATE TABLE [dbo].[test](
    [Value] [varchar](36) NOT NULL
)

…我们要在其中插入.NET应用程序中的数千个值（Guid）。

逐个

天真的方法是一次插入一个，如下所示：

public void OneByOne()
{
    const string sql = "INSERT INTO [Test] ([Value]) Values (@Value)";
    for (var i = 0; i < count; i++)
    {
        connection.Execute(sql, new { Value = Guid.NewGuid().ToString()});
    }
}

（请注意，此代码使用Dapper）

在本地SQL Express数据库上插入10,000条记录需要54,533毫秒，即每秒183条记录。慢：¬（

一次1000

SQL Server允许您在单个插入语句中插入多个记录，实际上我们一次最多可以插入1,000条。

public void BatchOf1000()
{
    foreach (var batch in Enumerable.Range(0, count).Chunk(1000))
    {
        if (batch.Length == 0) continue;
        var sql = "INSERT INTO [Test] ([Value]) VALUES \r\n" + string.Join(",\r\n", batch.Select(x => $"('{Guid.NewGuid().ToString()}')"));
        connection.Execute(sql);
    }
}

 

在本地SQL Express数据库上插入1,000,000条记录需要22,256毫秒，即每秒44,931条记录。必须更快。

批量复制

我们可以快点走吗？当然，我们可以。

SQL Server（和Azure中的SQL数据库）支持批量插入，您过去可能使用过bcp。

本SqlBulkCopy类提供了从.NET方便地访问此。

public void BulkCopy()
{
    var table = new DataTable();
    table.Columns.Add("Value", typeof(string));

    for (var i = 0; i < count; i++)
    {
        table.Rows.Add(Guid.NewGuid().ToString());
    }

    using (var bulk = new SqlBulkCopy(this.connection))
    {
        bulk.DestinationTableName = "test";
        bulk.WriteToServer(table);
    }
}

在本地SQL Express数据库上插入1,000,000条记录需要9,315毫秒，即每秒107,353条记录。甚至更快。

关于SqlBulkCopy的说明

上面的代码示例显示，在C＃中，您必须首先创建一个DataTable，然后将其告知目标表的架构。然后，根据值在表中的位置添加值。

这段代码对表结构的更改有些脆弱，因此另一种方法是从数据库中加载表结构，然后按名称而不是位置插入值：

 

public void BulkCopy()
{
    var table = new DataTable();

    // read the table structure from the database
    using (var adapter = new SqlDataAdapter($"SELECT TOP 0 * FROM test", this.connection))
    {
        adapter.Fill(table);
    };

    for (var i = 0; i < count; i++)
    {
        var row = table.NewRow();
        row["Value"] = Guid.NewGuid().ToString(); 
        table.Rows.Add(row);
    }

    using (var bulk = new SqlBulkCopy(this.connection))
    {
        //设置超时时间=1000秒
        bulk.BulkCopyTimeout = 1000;

        bulk.DestinationTableName = "test";
        bulk.WriteToServer(table);
    }
}

 

我注意到使用SqlBulkCopy会产生少量开销，因此我不会将其用于插入少量记录（即少于100条），但是您的里程可能会有所不同。

插入愉快！