SQL Delete语句执行过慢优化咨询：2万条记录耗时超12分钟

你的问题我太熟悉了——用ROW_NUMBER()处理重复数据时，嵌套子句加IN的写法很容易让性能拉胯，尤其是临时表没加索引的情况下。下面给你几个亲测有效的优化方向：

1. 改用CTE直接关联删除，替代IN子句 #

IN子句在处理大量数据时，数据库往往会执行多次全表扫描，而CTE（公共表表达式）可以一次性计算出所有要删除的行，直接和临时表关联删除，效率提升非常明显。修改后的语句如下：

WITH DuplicateRecords AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY Dep, NrInvoiceLine, OfficeId, pol, pod, PreCarr, WarehouseId, 
                        DestinationPrecarr, DestinationWarehouseId, PortTerminalId, Product, 
                        ISNULL(description, ''), ScaleCalcuationId, ISNULL(scalefrom, 0), 
                        ISNULL(scaleto, 0), CurrencyCode, Base, ShippingLineID, ContainerId, AgentId
            ORDER BY type
        ) AS RecordNum
    FROM @TempTable
    WHERE CAST(Date AS smalldatetime) = '2078-12-31 00:00:00' 
      AND IsDeleted = 0 
      AND (RO = 1 OR RO = 2) 
      AND (PP = 0 OR PP = 2)
)
DELETE FROM DuplicateRecords
WHERE RecordNum != 1;

这种写法的优势是CTE只扫描一次符合条件的数据，然后直接删除对应行，避免了原语句中子查询多层嵌套带来的重复扫描损耗。

2. 给临时表添加针对性索引 #

临时表默认没有索引，全表扫描2万条数据看似不多，但加上复杂的分区和过滤条件，速度就会慢下来。你可以在创建临时表后，添加一个覆盖索引：

CREATE NONCLUSTERED INDEX IX_TempTable_DuplicateFilter 
ON @TempTable (Date, IsDeleted, RO, PP)
INCLUDE (Dep, NrInvoiceLine, OfficeId, pol, pod, PreCarr, WarehouseId, 
         DestinationPrecarr, DestinationWarehouseId, PortTerminalId, Product, 
         description, ScaleCalcuationId, scalefrom, scaleto, CurrencyCode, Base, 
         ShippingLineID, ContainerId, AgentId, type, id);

这个索引能让数据库快速定位到需要处理的行，不用扫描整个临时表；同时INCLUDE列包含了分区和排序需要的字段，避免了额外的“书签查找”开销。

3. 分批删除，降低事务压力 #

哪怕是2万条数据，一次性删除也可能占用大量事务日志，甚至导致锁表。可以用循环分批删除，比如每次删1000条：

DECLARE @DeletedRows INT = 1;

WHILE @DeletedRows > 0
BEGIN
    WITH DuplicateRecords AS (
        SELECT TOP 1000
            id,
            ROW_NUMBER() OVER (
                PARTITION BY Dep, NrInvoiceLine, OfficeId, pol, pod, PreCarr, WarehouseId, 
                            DestinationPrecarr, DestinationWarehouseId, PortTerminalId, Product, 
                            ISNULL(description, ''), ScaleCalcuationId, ISNULL(scalefrom, 0), 
                            ISNULL(scaleto, 0), CurrencyCode, Base, ShippingLineID, ContainerId, AgentId
                ORDER BY type
            ) AS RecordNum
        FROM @TempTable
        WHERE CAST(Date AS smalldatetime) = '2078-12-31 00:00:00' 
          AND IsDeleted = 0 
          AND (RO = 1 OR RO = 2) 
          AND (PP = 0 OR PP = 2)
    )
    DELETE FROM DuplicateRecords
    WHERE RecordNum != 1;

    SET @DeletedRows = @@ROWCOUNT;
END

分批删除的好处是每次事务只处理少量数据，日志压力小，也不会长时间占用锁，让数据库能及时释放资源。

4. 优化过滤条件，避免隐式转换 #

原语句里CAST(Date AS smalldatetime) = '2078-12-31 00:00:00'，如果Date字段本身就是smalldatetime类型，完全可以去掉CAST——隐式转换会导致索引失效（如果有索引的话）。如果Date是datetime类型，可以把右边的常量转换成对应类型：

WHERE Date = CONVERT(datetime, '2078-12-31 00:00:00')

这样能让数据库更好地利用索引，提升过滤效率。

内容的提问来源于stack exchange，提问作者Che