读取500万条记录并最终更新一列。

Question

读取500万条记录并最终更新一列。

c#c#-4.0ado.net

3

我需要在表T1中更新500万条以上的记录。这是一个C#工具，它将从表T1中读取（选择）一列，例如T1.col1，然后根据逻辑从该列中提取一个值，并最终必须使用此处理过的值更新同一表中的另一列T1.col2并更新数据库。

想要了解在C# / ADO.NET中实现这一目标的最佳/优化方法的意见？

请注意：提取逻辑不能是SQL的一部分。该逻辑嵌入在COM DLL中，我正在从.NET进行互操作，并将其应用于Col1列的值以生成一个新值，最终必须将其保存在T1.Col2中。

- Angshuman Agarwal

1

如果您能将逻辑转换为SQL表达式，那么您应该能够使用一个UPDATE语句完成此操作。 - cdhowie

@Henk - 不可能的。请看我的评论。 - Angshuman Agarwal

@sees 那你就得付款了。但请明确：在设置col2之前，您是否需要获取所有col1的值？逐行处理会更具吸引力。 - H H

如果你不能使用单个UPDATE语句，那么我不会指望它在两天内完成...而且我敢打赌需要更长的时间。 - cdhowie

我不能在SQL中使用它。这是肯定的。我需要使用Col1值，在这些值上应用COM/Interop逻辑，然后使用这些新值更新相应的Col2。 - Angshuman Agarwal

显示剩余6条评论

7个回答

0

迄今为止，最快的方法是在SQL代码本身中执行更新：

UPDATE T1
SET col2 = [some function based on col1]

请注意，根据数据库平台的不同，这可能会导致事务日志爆炸。对于 MS SQL，我建议您每次以较小的批量进行更新，例如100K行或更少。

如果函数逻辑过于复杂，则请确保您基于主键发出所有500万个更新：

UPDATE T1
SET col2 = @newval
WHERE tableID = @id

- BradC

提取逻辑不能成为SQL的一部分。该逻辑嵌入在一个COM DLL中，我正在从.NET进行互操作，并应用于列值以生成新值。 - Angshuman Agarwal

0

你真的需要用新值更新col2吗？

如果每一行都得到相同的值，我会将其写入一个单独的表中，只有1行，并在需要返回结果时交叉连接该行。

像这样：

update t2 
set col2 = 1234 -- the computed value over all rows in t1

select t1.col1, 
       t2.col2 
from   t1
       cross join t2 -- t2 only has 1 row

更新操作相对较为昂贵，写入一行数据肯定比写入五百万行要便宜得多。

否则我会把磨坊建在木材堆旁，所以如果可能的话请使用TSQL。不过，500万行也不是什么大问题，您能够在服务器上处理它们吗？还是需要通过网络传输？如果是后者，那么成本就会增加。

敬礼，Gert-Jan

- gjvdkamp

是的，我必须更新Col2，因此我才会问这个问题。我知道这将是困难的，所以我正在寻求C# / ADO.NET中最优雅/最佳的解决方案。 - Angshuman Agarwal

好的，我现在明白了，虽然它是所有行中的一个值。尝试在服务器上运行您的代码，并查看任务管理器以查看瓶颈所在（处理器还是磁盘）。此外，也许这将使用老式的oledb游标最快，您可以读取、计算和更新一行，然后移动到下一行。你们公司有本地编码人员吗？老员工会知道如何使用oledb，而且它会节省在 .net 和本地代码之间来回切换的时间，因为dll是本地的。 - gjvdkamp

另一个加速的提示：尝试让服务器磁盘进行连续读/写操作。一种方法是将表分成两个部分，其中col2移动到另一个文件组上的另一个磁盘上的单独表中。这样，您可以在一个磁盘上连续读取，计算值并在另一个磁盘上连续写入。键将按相同顺序排列，因此SQL可以使用合并连接来获取所需的结果，该连接执行成本非常低。结果表将是这两个表上的视图，这种技术称为垂直分区。 - gjvdkamp

0

一次性在内存中拥有如此多的数据是很多的。如果可能的话，我建议从COM DLL中以较小的记录批量获取数据并处理。使用PLinq to objects可以让您最大化处理器使用率。两者之间，您应该能够找到一个良好的平衡点。

- Charles Lambert

0

在执行这些操作时，您是否希望用户等待直到它们结束？或者您可以在后台运行此任务吗？甚至可以在某些计划处理期间每晚运行？如果后者是真的，您可以使用您的方法并简单地执行所有数百万次更新，只需确保不让用户等待操作完成即可。否则，您确实需要考虑一种将所有更新逻辑移植到数据库中的方法。

因此，根据要求，选项如下：

1）启动一个单独的线程（甚至几个线程）来执行更新，而主线程将向用户返回类似于“正在运行冗长的操作。请稍后返回此页面以查看其状态”的内容。

2）每晚在单独的进程中运行更新

3）重新组织项目，以便您可以承担在数据库中执行更新的成本。

更新：

我看到您不断说无法将更新逻辑移植到数据库中。如果您不负责项目架构，您能否影响管理层重新组织整个项目？如果需要进行此类操作，则看起来像是一个糟糕的架构。

- Michael Sagalovich

Michael，它是一个升级工具，由于某些传统原因需要在客户数据库上运行一次。 - Angshuman Agarwal

0

一些基本指针:

使用 DataReader 而不是 DataSet。DataSet 的内存开销可能会在处理这么多行数据时带来麻烦。
如果可能的话，将计算部分并行运行在几个线程中。你可以使用 TPL 来做到这一点，但由于你正在使用 COM 组件，可能会有一些访问多个线程的问题。请咨询 COM 专家（或开启另一个 SO 问题）以确定你的 COM 组件是否支持多线程。
在计算结果时，不要保持单个大型事务处于打开状态。如果适用于你的语义，请使用 "with(nolock)" 提示。这将有助于防止你的任务影响其他读写器。

- Chris Shain

计算不会耗费太多时间。我的主要关注点是SELECT和UPDATE部分。Col1（nText类型）包含二进制数据。我将反序列化数据，提取值，然后使用此值更新表-Col2。反序列化和提取逻辑位于COM DLL中，我正在从C#进行互操作。 - Angshuman Agarwal

在这种情况下，它听起来像是一个令人尴尬的并行问题：http://en.wikipedia.org/wiki/Embarrassingly_parallel。如果您将其作为一系列并行进程运行，则很可能会受到 SQL 服务器磁盘上的 I/O 速度和 SQL 服务器与应用程序服务器之间的网络带宽的限制。 - Chris Shain

0

如果这只是一次性的操作，那么将您的500万+记录转储到文件中。运行逻辑以生成新记录。转储和文件逻辑应该快速且不耗时。然后将更新的数据批量插入到暂存表中。

在那时，废弃先前的表，并使用一些DDL语句将暂存表作为真实表，放置适当的索引、FK等。

这将是处理此数量级记录的最快方法。其他任何方法都可能需要至少几天时间来处理所有内容。

- Jon Raynor

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Yahia · Accepted Answer

由于您需要通过COM对象传输数据进行某些操作，这是我会做的事情：

使用内存较大的机器-将数据分块加载到内存中（例如每次5000或50000行），处理它并在SQL Server上进行更新...

对于UPDATE部分，请使用事务，并将5000-20000个UPDATE放入一个事务中...

[编辑]：通过适当地分区工作并将500000或1000000行分配给一个“工作机器”，您可以将其加速到SQL Server的最大限制... [/编辑]

另一种选择-虽然不建议（仅因为在此特定情况下由COM对象引入的理论上可能存在的安全和/或稳定性问题）：

尽管这是关于SQL Server的描述，但在Windows上使用Oracle也有类似的可能性

您可以通过编写和安装公开存储过程的.NET程序集，将此转换的逻辑放入SQL Server中，以调用执行转换... .NET程序集反过来访问该COM对象... 如何请参见http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

此内容的MSDN参考链接为http://msdn.microsoft.com/en-us/library/ms131094.aspx