在SQL Server中插入100万行的最快方法

Question

我正在编写一个存储过程，用于将行插入到表中。问题是，在某些操作中，我们可能希望插入超过100万行，并且我们希望使它快速完成。另一件事是，其中一个列是 Nvarchar(MAX)。我们可能希望在此列中放置平均1000个字符。

首先，我编写了一个逐行插入的存储过程。然后，我生成了一些随机数据进行插入，其中 NVARCHAR(MAX) 列为1000个字符的字符串。然后使用循环调用存储过程插入这些行。如果我使用SQL服务器登录数据库服务器进行插入，则性能非常差，需要48分钟。如果我使用C＃连接到桌面上的服务器（这通常是我们想要做的），则需要超过90分钟。

然后，我更改了存储过程以接受表类型参数作为输入。我以某种方式准备了这些行并将它们放入表类型参数中，然后通过以下命令进行插入：

INSERT INTO tableA SELECT * from @tableTypeParameterB

我尝试过将批量大小设置为1000行和3000行（将1000-3000行放入@tableTypeParameterB中，一次性插入）。但性能仍然很差。如果我在SQL服务器上运行它，插入100万行需要约3分钟，如果我使用C#程序从我的桌面连接，则需要大约10分钟。

tableA具有两列的聚集索引。

我的目标是尽可能快地进行插入（我的理想目标是在1分钟内完成）。是否有任何方式可以优化它？

---

更新：

我尝试了一些人建议的批量复制插入。我尝试使用SQLBULKCOPY一次性插入1000行和10000行。性能仍然需要10分钟来插入100万行（每一行都有一个长度为1000个字符的列）。没有提高性能。是否有其他建议？

---

基于评论的更新：

实际上，数据来自UI。用户将使用UI批量选择，例如选择一百万行并将其中一列从旧值更改为新值。此操作将在单独的过程中完成。但是这里我们需要做的是使中间层服务从UI获取旧值和新值，并将它们插入表中。旧值和新值可能长达4000个字符，平均为1000个字符。我认为长字符串的旧/新值会降低速度，因为当我将测试数据的旧值/新值更改为20-50个字符并插入时，无论使用SQLBulkCopy还是表类型变量，速度都非常快。

- Mandy

---

你应该了解一下 SqlBulkCopy。 - cost

目前我只是在做示例。我从另一个表中读取了1000行数据，并将其存储在一个datatable对象中。然后通过类似于“Insert into tableA Select @i, Column1, Column2, Column3... ColumnN from @tmpTableTypePrameter”的命令将它们插入到tableA中1000次。 - Mandy

明天我会看一下我在工作中的一些代码，但我非常确定我已经用几千条记录填充了一个DataTable，然后使用了SqlBulkCopy，只花了几秒钟。你不需要通过DataTable循环遍历。你只需要确保字段映射正确（名称相同）。明天会发布代码示例。 - Randy R

@Mandy，我喜欢你分享的帖子。这是我的问题，请看一下并帮助我：我在MSSQL数据库中有一个包含1.5亿条记录的16列表，平均每天插入3.25万行。在插入每个新的单行之前，我会检查表中的5个列值。如果基于这5个列值找不到任何行，则只能插入新行。请建议我如何以高效的方式完成它？我正在从CSV文件中逐个读取项目并将该行插入表中。目前需要9-10小时才能插入3.24万行。提前致谢。 - dilipkumar1007

@BonanzaOne 我有一个包含1.5亿条记录的MSSQL数据库中的16列表，每天平均插入3.25万行。在插入每一行新数据之前，我都会检查表中5个列的值。如果基于这5个列的值没有找到相应的行，则只能插入新行。请建议如何以更有效率的方式实现。当前我是从csv文件中逐一读取项目并将其插入表中，插入3.24万行需要9-10小时。提前感谢您的答复。 - dilipkumar1007

Answer 1

我认为您要找的是批量插入，如果您喜欢使用SQL的话。

或者还有ADO.NET批处理操作选项，这样您就可以在C#应用程序中保留逻辑。这篇文章也非常全面。 更新 是的，恐怕批量插入只适用于导入的文件（从数据库内部导入）。

我有一个Java项目的经验，我们需要插入数百万行数据（数据来自应用程序外部）。

数据库是Oracle，所以我们当然使用了Oracle的多行插入。结果证明，Java批量更新比Oracle的多值插入（所谓的“批量更新”）快得多。

我的建议是：

• 比较SQL Server代码中的多值插入（您可以从数据库内部读取，如果喜欢，可以使用存储过程）与ADO.NET批量插入之间的性能差异。

如果要操作的数据来自应用程序外部（如果它尚未在数据库中），我建议只使用ADO.NET批量插入。我认为这是您的情况。

注意：请记住，批量插入通常使用相同的查询。这就是它们如此快的原因。

- Evandro Pomatti

---

@Blorgbeard，我认为曼迪指的是SQL命令，而不是.NET API。 - Evandro Pomatti

@Evandro，如果BulkCopy很快，我可以要求中间层使用它来代替SQL命令存储过程。但是根据我的最后几个命令，似乎还不够快。 - Mandy

SqlBulkCopy具有内置的批处理功能 - 只需设置BatchSize属性即可。您使用了它，还是自己实现了批处理？无论如何，通过网络传输1M行数据肯定会导致一些流量问题。 - Blorgbeard

@Blorgbeard 在我的实验中，我做了一件非常简单的事情，从一个现有的表中选择1000行，将其放入一个datatable对象中。然后使用“for”循环将其插入到表中1000次，只需操作一些行使每行都是唯一的。我只计算了插入时间总和，总和超过了10分钟。 - Mandy

请注意，对于许多数据库，可以通过在连接上使用数据压缩来加速多值插入。通常限制因素是网络连接的上传带宽。我曾经在MySQL中使用过这个功能，但一般来说其他数据库也应该可以实现。 - sandimschuh

Answer 2

在循环中调用prc会导致许多往返SQL的操作。不确定您使用了什么批处理方法，但是您应该研究表值参数:文档在这里。您仍需要进行批量写入。您还需要考虑服务器上的内存。批量处理(例如每次10K)可能会慢一些，但它可以使服务器的内存压力更低，因为您正在缓冲和一次处理一组数据。

表值参数提供了一种简单的方法，可以在不需要多次往返或特殊的服务器端逻辑来处理数据的情况下，将客户端应用程序中的多行数据组合到SQL Server中。您可以使用表值参数在客户端应用程序中封装数据行，并在单个参数化命令中将数据发送到服务器。输入的数据行存储在一个表变量中，然后可以使用Transact-SQL对其进行操作。

另一种选择是批量插入。然而，TVP的重复使用有益处，这取决于您的使用模式。第一个链接中有关于比较的注释:

使用表值参数与其他使用基于集合变量的方式相当；然而，对于大型数据集，使用表值参数经常比批量操作更快。与启动成本大于表值参数的批量操作相比，表值参数对于插入少于1000行的数据表现良好。

重用的表值参数受益于临时表缓存。这个表缓存使得比等效的BULK INSERT操作具有更好的可扩展性。

另一个比较在这里: bcp / BULK INSERT和表值参数的性能。

- bryanmac

Answer 3

这是我以前使用SqlBulkCopy的一个例子。虽然我只处理了大约10,000条记录，但它在查询运行后几秒钟内插入了它们。我的字段名称相同，所以很容易。您可能需要修改DataTable字段名称。希望这可以帮助到您。

private void UpdateMemberRecords(Int32 memberId)
    {

    string sql = string.Format("select * from Member where mem_id > {0}", memberId);
    try {
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(sql, _sourceDb))) {
            da.Fill(dt);
        }

        Console.WriteLine("Member Count: {0}", dt.Rows.Count);

        using (SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.AppSettings("DestDb"), SqlBulkCopyOptions.KeepIdentity)) {
            sqlBulk.BulkCopyTimeout = 600;
            sqlBulk.DestinationTableName = "Member";
            sqlBulk.WriteToServer(dt);
        }
    } catch (Exception ex) {
        throw;
    }
}

- Randy R

---

有人会将 int32 改成 string（例如，为了采用此代码用于 guid），然后你好 SQL 注入 :) - Alex Zhukovskiy

@Alex，如果你连自己的程序员都不信任，那么这只是你最不用担心的事情之一。 - PRMan

@PRMan 很不成熟的结论 - Alex Zhukovskiy

Answer 4

如果您使用的是SQL2014，那么内存中OLTP的速度是惊人的; http://msdn.microsoft.com/en-au/library/dn133186.aspx

- Darren S

Answer 5

根据您的最终目标，研究Entity Framework（或类似工具）可能是一个不错的主意。这将抽象出SQL，使得您在客户端应用程序中无需过多关注它，这正是事情应该发展的方向。

最终，您可能会得到像这样的东西：

using (DatabaseContext db = new DatabaseContext())
{
    for (int i = 0; i < 1000000; i++)
    {
        db.Table.Add(new Row(){ /* column data goes here */});
    }
    db.SaveChanges();
}

这里的关键部分（也是其他答案的核心）是Entity Framework处理构建实际插入语句并将其提交到数据库的过程。

在上述代码中，直到调用SaveChanges并提交所有内容之前，没有任何东西会真正发送到数据库。

我记不太清我在哪里找到它，但有研究表明每隔一段时间调用SaveChanges是值得的。从记忆中，我认为每1000条记录是提交到数据库的一个好选择。与每100条记录相比，每个记录的提交并不能提供太多性能优势，而10000条则超出了限制。不过，这些数字可能是错误的。你似乎很擅长测试方面的工作，所以可以尝试一下。

- Trent

---

抽象化SQL是否会导致最佳性能？您的代码与BCP或TVP相比如何？我认为您会发现代码量较少，但不够优化（这是讨论的问题）。 - bryanmac

@bryanmac 我没有对情况进行基准测试。但是，从个人经验/研究中我知道，在循环外使用SaveChanges()（或在循环内使用条件，例如每添加1000次）会比在循环内使用它导致显著的性能提升。正如你所说，我相信EF将其简化为T-SQL，这也在你的答案中提到了。 - Trent

http://stackoverflow.com/questions/13702756/which-one-is-faster-entity-framework-vs-stored-procedures/13703039#13703039 - bryanmac

请阅读上面的第三条评论。是的，它生成T-SQL，但是生成的是通用的T-SQL，而不是TVPS所需的特定类型的T-SQL。 - bryanmac

好的，那么这是一个在100k个Add()调用后调用SaveChanges()的基准测试。所以一次插入了100k条记录。我发现的研究表明这是次优的，最好更频繁地进行操作。我认为每隔一段时间重新创建上下文也是一个不错的选择，因为它可以减少上下文中存储的内容。我并不是说这是最好的解决方案，但出于可维护性的考虑，我更喜欢这个选项，并且我认为它应该能够获得OP想要的性能提升。 - Trent