改进 UPDATE/DELETE 性能 SQL Server 2012

Question

我们的项目中有流程，其中删除具有特定标志的表中的记录并更新剩余记录的标志。

表有大约 4500 万条记录，其中一半记录带有 flag='C'，其余一半带有 flag='P'。

进程每天运行一次，删除所有带有标志"P"的记录，然后更新所有带有标志"C"的剩余记录

下面是通过 SSIS 包运行的两个语句。

DELETE FROM dbo.RTL_Valuation WITH (TABLOCK) 
WHERE Valuation_Age_Flag = 'P';
UPDATE dbo.RTL_Valuation WITH (TABLOCK) 
SET Valuation_Age_Flag = 'P' 
WHERE Valuation_Age_Flag = 'C';

目前，该过程需要 60 分钟才能完成。有什么方法可以改善处理时间吗？

谢谢

Answer 1

您需要一次执行 10000 行。您正在创建一个占用事务日志中大量空间的巨大事务(因此可以回滚(。

set nocount on
DELETE  top (10000) FROM dbo.RTL_Valuation   WHERE valuation_Age_Flag = 'P';    
while @@rowcount()>0
begin
DELETE  top (10000) FROM dbo.RTL_Valuation   WHERE valuation_Age_Flag = 'P';    
end

您可以尝试 1,000、5,000 或其他一些数字，以确定哪个是最好的"魔术"数字，以便在安装 SQL Server 时快速删除大型表中的行。但它会比做一个大删除快得多。相同的逻辑适用于更新。

Answer 2

好的。我假设，当您执行删除和更新语句时，它会导致对整个表进行两次扫描(一次用于标识要删除的行，另一个用于标识要更新的行(，然后您必须对其执行完全记录的删除和更新操作。

如果您的数据库处于简单恢复模式，则对于此类情况，有一个很好的技巧。但是，这是否适合您取决于其他情况(例如，您表有多少索引，是否有一些引用，数据类型......(，我无法从您的描述中评估。它需要更多的编码，但它通常会导致更好的性能。您必须测试它是否比原始方法更适合您。

无论如何，这个技巧是这样的：

1. 无需删除和更新操作，只需使用"SELECT INTO"构造在新表中选择要保留的行(包括标志的更改(。这会导致最少记录的插入操作和单个表扫描。您也可以使用"插入到选择"构造，但您必须满足一些附加条件才能获得最少记录的插入。
2. 一旦数据进入新表，您必须在其上构建所有必需的索引。
3. 构建完所有索引后，只需截断原始表，然后使用 SWITCH 命令将数据切换回原始表并删除"新表"。它也适用于SQL Server的标准版本。