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

本文关键字：一个 更新 500万 记录 读取 | 更新日期: 2023-09-27 18:06:24

我必须为表T1在数据库中更新 500万+记录。这是一个C# tool，它将READ (Select)表T1中的一个列，例如T1.col1，然后根据该列的逻辑提取一个值，最后必须在同一表中UPDATE另一个列T1.col2与这个处理过的值并更新Db。

想要一些关于在c#/ADO中实现此功能的最佳/优化方法的意见。净?

注意:提取逻辑不能是SQL的一部分。这个逻辑就是嵌入在一个COM DLL，我从。net和应用

将列Col1的值生成一个新值，该值最终必须保存在T1.Col2中。

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

由于您需要通过COM对象传输某些操作的数据，因此我将这样做:

使用具有大量内存的机器-将数据块(例如一次5000或50000行)加载到内存中，处理它并在SQL Server上进行更新…

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

[EDIT]:通过正确划分工作并分配500000或1000000行到一个"worker-machine"，您可以将此速度提高到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

到目前为止，最快的方法是在SQL代码中执行更新:

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

(请注意，根据数据库平台的不同，这可能会导致事务日志激增。特别是对于MS SQL，我建议您以较小的批量更新，可能一次更新100k行或更少)

如果函数逻辑太复杂，那么请确保基于主键发布所有500万次更新:

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

您真的需要用新值更新col2吗?

如果每一行都得到smmae值，我会把它写进一个单独的表中，只有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

更新相对昂贵，写1行肯定比写500万行便宜得多。

否则，我将把磨坊的木材是，所以使用TSQL，如果可能的话。500万也不是什么大问题，你能在服务器上处理它们吗，还是需要把它们拖过网络?在最后一种情况下，它确实加起来了。

Rgds Gert-Jan

一次在内存中有这么多数据。如果可能的话，我建议从COM DLL中提取小批量记录的数据并处理它们。对对象使用PLinq将允许您最大限度地使用处理器。在这两者之间，你应该能够找到一个有效的折中方案。

在执行这些操作时，您是否希望用户等待操作结束?或者你可以在后台运行这个任务?甚至可能是在某些预定的处理期间的夜间?如果后者是正确的，您可以使用您的方法并简单地执行所有数百万更新，只需确保您不会让用户等待操作完成。否则，您真的需要考虑将所有更新逻辑移植到数据库中的方法。

因此，根据需求，选项将是:

1)启动一个单独的线程(甚至几个线程)来执行更新，而主线程将返回给用户一些类似"长时间操作正在运行"的信息。

请稍后返回此页面查看其状态。

2)在一个单独的进程中每晚运行更新

3)重新组织项目，以便您可以负担得起在数据库中执行的更新。

我看到你一直在说你不能把你的更新逻辑移植到数据库中。如果你不负责项目架构，你能影响管理层重组整个项目吗?如果需要这样的操作，它看起来像是一个糟糕的体系结构。

一些基本的指针:

    使用一个数据阅读器，而不是一个数据集。数据集的内存开销可能会对这么多行造成麻烦
  1. 如果可能的话，在几个线程中并行运行计算部分。您可以使用TPL来实现这一点，但是由于您使用的是COM组件，因此从多个线程访问它可能会有一些问题。关于如何确定你的COM组件是否线程安全，请咨询COM专家(或打开另一个SO问题)。
  2. 在计算结果时不要保持单个大规模事务打开。如果适合您的语义，请使用"with(nolock)"提示。这将有助于防止您的任务影响其他读/写程序。

如果这是一次性交易，那么将500万条以上的记录转储到一个文件中。针对文件运行逻辑以生成新记录。转储和文件逻辑应该是快速的，而不是耗时的。然后将更新后的数据BULK插入到staging表中。

此时，弃用前面的表，并使用一些DDL语句将staging表作为真实表，以放入适当的索引、fk等。

这将是处理这么多记录的最快方式。

相关文章：