更新数千条记录的最有效方法是什么？

Question

我们有一个C#应用程序，从文本文件中解析数据。然后，我们需要根据文本文件中的信息更新我们的SQL数据库中的记录。将数据从应用程序传递到SQL服务器的最有效方法是什么？

目前，我们使用分隔字符串，然后在存储过程中循环遍历该字符串以更新记录。我还在测试使用TVP（表值参数）。还有其他选项吗？

我们的文件包含数千条记录，我们希望找到一个消耗最少时间的解决方案。

- Anita

---

另一个选项是使用BCP.EXE或SSIS或BULK INSERT关键字将文件批量插入SQL Server中的暂存表，然后使用存储过程将其从暂存表转换到最终表。与C#相比，SQL在排序、查找、分组、汇总方面效率高，但在文本处理方面不太好。您的文本文件是什么格式，需要进行什么样的处理？ - Nick.McDermaid

对于 SQL Server 来说，成千上万的记录只是小菜一碟。您可以按照任何方式进行操作，重复10次，它仍然会非常快速。 - Andomar

TVP 对于你的 C# 代码可能更加高效，因为你很可能以某种低效的方式构建逗号分隔字符串，但最终基于像 DataTable 这样更好的结构。对吧？ - Aaron Bertrand

感谢@AaronBertrand。我在我的应用程序中使用了一个数据表，这样将其传递给SP会更容易。我应该限制一次向SQL服务器传递多少记录吗？ - Anita

我认为并没有一个神奇的数字 - 这将取决于各种因素。默认情况下，如果您触及> 5000行，则SQL Server通常会升级到表锁定，但是根据许多因素，它可能会再次在其他阈值上做出决策。表锁定将导致阻塞并限制并发性。然而，退一步来说，我无法想象您需要一次更新数千行的场景，而您唯一能够做到这一点的方式就是逐个识别它们。即使有点抽象，您能解释一下真实世界的场景吗？他们如何到达DataTable？ - Aaron Bertrand

Answer 1

请不要使用DataTable，因为这只是浪费CPU和内存，没有任何好处（除了可能更熟悉）。我在回答以下问题时详细介绍了一种非常快速和灵活的方法，该方法与此问题非常相似：如何在最短时间内插入1000万条记录？。该回答中显示的示例仅适用于INSERT，但可以轻松地改为包括UPDATE。此外，它会一次性上传所有行，但也可以轻松地改为为X条记录设置计数器，并在传递了那么多记录后退出IEnumerable方法，然后在没有更多记录时关闭文件。这需要将文件指针（即流）存储在静态变量中，以便将其传递给IEnumerable方法，以便在下一次回合时可以在最近的位置上推进并捕获它。我在以下回答中展示了这种方法的工作示例，尽管它使用SqlDataReader作为输入，但技术是相同的，并且需要很少的修改：如何将具有100万数据的一个大表拆分为多个表？。对于一些透视，50k条记录甚至还不接近“巨大”。我一直在使用我在这里展示的方法上传/合并/同步数据，该方法适用于400万行文件，并且命中了包含1000万（或更多）行的多个表格。

---

不要做的事情：

• 使用DataTable：如果你只是为了将数据填充到TVP中而使用它，那么这将浪费CPU、内存和时间。
• 一个并行更新一次（如问题评论中建议的）：这太疯狂了。关系型数据库引擎已经被大量优化以便与集合最高效地工作，而不是单个操作。50,000个插入操作甚至不如每次插入100行的500个插入操作更加高效。逐个操作仅会在表上产生更多争用，即使仅是行锁定（这是10万次锁定和解锁操作）。尽管可能比升级到表锁的单个50,000行事务更快（正如Aaron提到的），但你需要将其分成较小的批次进行，只要小不意味着1行 ;)。
• 任意设置批大小。保持在5000行以下有助于减少锁升级的机会，但不要仅选择200。尝试使用多个批量大小（100、200、500、700、1000）并每个批量大小测试几次。你将看到对于你的系统哪种方法最好。只需确保批处理大小可通过app.config文件或其他方式进行配置（DB中的表、注册表设置等），以便可以在无需重新部署代码的情况下进行更改。
• SSIS（功能强大，但非常臃肿且不便于调试）

以下方法也可以使用，但是不如正确处理TVP（例如传递返回IEnumerable<SqlDataRecord>的方法）灵活。这些方法可以使用，但为什么要将记录转储到临时表中，然后再将它们解析到目标中呢？你可以在内部完成所有操作。

• BCP / OPENROWSET(BULK...) / BULK INSERT
• .NET的SqlBulkCopy

- Solomon Rutzky

---

我处理复杂的 ETL 过程，我很喜欢使用 SSIS，但同意你的看法，调试并不好玩。 - HLGEM

不要误会，我肯定尊重SSIS的能力，特别是可以执行并行任务和编写自定义的C# / VB.Net任务。但是，除非您经常使用SSIS并因此熟悉其细微差别，否则它是一个陡峭的学习曲线（因此非常耗时），如果一个项目将是唯一使用它的东西,通常不值得花费这么多时间去学习。至少这是我的经验。维护/管理这些项目并不容易。 - Solomon Rutzky

我也同意陡峭的学习曲线。这是一个很棒的工具，但非常复杂。在面试时，我们会问他们不喜欢SSIS的什么，这个答案将告诉我们他们是否真正使用过它！任何人说“没有什么”都被认为是该产品的新手。 - HLGEM

@HLGEM 这是一个好的、聪明的技巧。我喜欢它。我做类似的事情，更多地是以一般的方式，通过询问候选人他们对游标和/或触发器的看法。回答通常不仅表明他们有处理这个问题的经验，并且实际上已经思考过它而不是重复他们所读到的内容，而且还表明他们的沟通能力如何。 - Solomon Rutzky

我可以和你聊上几个小时关于触发器和游标的话题。好的、坏的、使用案例、避免使用它们的地方，以及真实生活中出现的事情的例子（你能在触发器中使用游标吗？）以及事情成功的真实生活例子和如何调试触发器等等。如果在适当的时候不使用触发器，最糟糕的事情是什么等等。我永远也不会结束这次面试。 - HLGEM

Answer 2

在我看来，最好的方法是创建一个临时表，然后使用SqlBulkCopy将数据插入到该临时表中（https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy%28v=vs.110%29.aspx），然后根据临时表更新主表。

根据我的测试（使用Dapper和LINQ），批量更新或分批更新比仅创建临时表并向服务器发送命令以根据临时表更新数据要慢得多。这个过程更快，因为SqlBulkCopy以快速的方式本地填充数据，其余部分在SQL服务器端完成，经过较少的计算步骤，此时数据驻留在服务器端。

- Hozikimaru