更新sql server中的大型表

Question

我有一个宽表(80列)，其中有7000万行。目前，我可以尝试在表中的所有行中设置外键。

在整个表上使用update语句，例如：

BEGIN TRAN
UPDATE Cx
SET Cx.CohortId = Chx.CohortId
FROM Customers Cx
INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField
COMMIT TRAN

我被告知这有可能填充事务日志(请注意，恢复模式目前设置为simple)，最好对更新进行批量处理：

分批进行两次更新

DECLARE @Rows INT,
    @BatchSize INT; 

SET @BatchSize = 20000;
SET @Rows = @BatchSize; 

WHILE (@Rows = @BatchSize)
BEGIN
UPDATE TOP(@BatchSize) Cx
SET Cx.CohortId = Chx.CohortId
FROM Customers Cx
INNER JOIN Cohorts Chx ON Chx.NameField = Cx.NameField
WHERE Cx.CohortId IS NULL -- Do not update records that have already been uplifted to include the FK

SET @Rows = @@ROWCOUNT
END

第二种方法对大型表更新的事务日志有什么好处吗？

Answer 1

我被告知这有可能填充事务日志。。。更好的方法是批量更新

如果它不填充事务日志，情况会更好。但建议的补救措施是错误的。

您应该调整事务日志的大小以支持事务，而不是相反。

第二种方法对大型表更新的事务日志有什么好处吗？

是。只要所有批处理都不在事务中，事务日志就可以在批处理之间重用。

增加磁盘空间以支持这将是很麻烦的

是的，有时候你确实需要围绕空间问题进行编码，但是编码并不是免费的。将资金花在基础设施上以减少编码时间通常是明智的做法。

另外，如果您试图最小化日志空间，请考虑在查询中加载一个空表，然后执行ALTER…。换一下。加载新表可以最少记录，而ALTER是元数据操作。

这还将使您能够在事务中执行操作，而不是提交中间结果。