使用 Ado.NET 插入 750 条记录的最快方法是什么？

Question

我们已经使用 orm 映射器工具尝试过，但它打开和关闭连接 750 次。然后我们尝试构建一个批量插入，但是速度更慢......

编辑：

CREATE TABLE [dbo].[DataWarehouse](
[DataWarehouseId] [int] IDENTITY(1,1) NOT NULL,
[ColumnName] [nvarchar](max) NOT NULL,
[ColumnValue] [nvarchar](max) NOT NULL,
[RRN] [nvarchar](50) NOT NULL,
[PackageSessionId] [int] NOT NULL,
[AccessCode] [nvarchar](100) NOT NULL,
[Selectie] [nvarchar](150) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[PackageId] [int] NOT NULL,
[Category] [nvarchar](500) NULL,
[OrderId] [int] NOT NULL,
[Category2] [nvarchar](500) NULL,
[TestCode] [nvarchar](200) NULL,
[Category3] [int] NULL,
[QuestionSpecificCategory] [nvarchar](max) NULL,
 CONSTRAINT [PK_DataWarehouse] PRIMARY KEY CLUSTERED 
(
    [DataWarehouseId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

使用简单的 SqlCommand 对于 750 行应该足够快，除非您有一些非常重的字段，或者数据库中有一些非常昂贵的索引/约束：

var insert = new SqlCommand("INSERT INTO ...", connection);
var fooParam = insert.Parameters.Add("Foo", SqlType.Int);

for (int i = 0; i < 750; i++)
{
    fooParam.Value = i;
    insert.ExecuteNonQuery();
}

Answer 2

在我的（远非最先进的）机器上，这需要 200 到 400 毫秒。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;

namespace InsertSpeedTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var sw = new Stopwatch();
            sw.Start();
            using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=SpeedTests;Integrated Security=True;"))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "insert into [dbo].[DataWarehouse] ( [ColumnName] , [ColumnValue] , [RRN] , [PackageSessionId] , [AccessCode] , [Selectie] , [Date] , [PackageId] , [Category] , [OrderId] , [Category2] , [TestCode] , [Category3] , [QuestionSpecificCategory]) values  ( @ColumnName , @ColumnValue , @RRN , @PackageSessionId , @AccessCode , @Selectie , @Date , @PackageId , @Category , @OrderId , @Category2 , @TestCode , @Category3 , @QuestionSpecificCategory)";
                        cmd.CommandType = CommandType.Text;
                        cmd.Transaction = tran;
                        cmd.Parameters.Add("@ColumnName", SqlDbType.NVarChar,-1);
                        cmd.Parameters.Add("@ColumnValue", SqlDbType.NVarChar, -1);
                        cmd.Parameters.Add("@RRN", SqlDbType.NVarChar, 50);
                        cmd.Parameters.Add("@PackageSessionId", SqlDbType.Int);
                        cmd.Parameters.Add("@AccessCode", SqlDbType.NVarChar, 100);
                        cmd.Parameters.Add("@Selectie", SqlDbType.NVarChar, 150);
                        cmd.Parameters.Add("@Date", SqlDbType.SmallDateTime);
                        cmd.Parameters.Add("@PackageId", SqlDbType.Int);
                        cmd.Parameters.Add("@Category", SqlDbType.NVarChar, 500);
                        cmd.Parameters.Add("@OrderId", SqlDbType.Int);
                        cmd.Parameters.Add("@Category2", SqlDbType.NVarChar, 500);
                        cmd.Parameters.Add("@TestCode", SqlDbType.NVarChar, 200);
                        cmd.Parameters.Add("@Category3", SqlDbType.Int);
                        cmd.Parameters.Add("@QuestionSpecificCategory", SqlDbType.NVarChar, -1);
                        cmd.Prepare();
                        for (int i = 0; i < 750; i++)
                        {
                            cmd.Parameters["@ColumnName"].Value = "Column " + i;
                            cmd.Parameters["@ColumnValue"].Value = "value " + i;
                            cmd.Parameters["@RRN"].Value = "prn" + i;
                            cmd.Parameters["@PackageSessionId"].Value = i;
                            cmd.Parameters["@AccessCode"].Value = "access code" + i;
                            cmd.Parameters["@Selectie"].Value = "selectio " + i;
                            cmd.Parameters["@Date"].Value = DateTime.Now.AddMinutes(i);
                            cmd.Parameters["@PackageId"].Value = i;
                            cmd.Parameters["@Category"].Value = "category " + i;
                            cmd.Parameters["@OrderId"].Value = 100000 + i;
                            cmd.Parameters["@Category2"].Value = "category2 " + i;
                            cmd.Parameters["@TestCode"].Value = "test code " + i;
                            cmd.Parameters["@Category3"].Value = 200000 + i;
                            cmd.Parameters["@QuestionSpecificCategory"].Value = "whatever " + i;
                            cmd.ExecuteNonQuery();
                        }
                        tran.Commit();
                    }
                }
            }
            sw.Stop();
            Debug.WriteLine(sw.ElapsedMilliseconds);
        }
    }
}

Answer 3

最快：

• 安排插入 32 个项目的块。为此，请使用 Workitem。
• 在每个处理程序中，打开连接，提交一条包含所有 32 个插入的 sql 语句。

结果：

• 往返次数减少。请求的同步性质使得提交请求的成本有点高——批量提交 32 个请求比逐个提交 32 个请求要快。
• 如果您的 SQL Server 配置正确（遗憾的是，配置不多），则可以使用并行插入。这也意味着多个请求到达服务器而无需等待下一批。