SSMS中高效查找并删除ServiceCodes表重复组合行方案求助

我有一张ServiceCodes表，包含Customer、companyId、ServiceCode、CompanySourc、NewlyInserted字段。插入记录时，会存在Customer和ServiceCode组合重复的行，这类重复行的NewlyInserted字段会被设为1。现在需要查找并删除这类重复行，之前尝试Inner Join、Cross Apply等方法未成功，原更新删除方案在数据量增大时运行极慢，需要高效解决方案，该删除操作每次用户点击按钮时执行。

示例数据 #

custID1,compid1,Service1233,CompSource1,0
custID2,compid1,Service1234,CompSource1,0
custID3,compid1,Service1235,CompSource1,0
custID4,compid1,Service1236,CompSource1,0
custID3,compid1,Service1235,CompSource1,1
custID4,compid1,Service1236,CompSource1,1

原低效代码 #

UPDATE Servicecodes  
            SET isNeedstobeDelete = 1
            FROM Servicecodes
            CROSS APPLY (SELECT distinct Client,ServiceCode FROM Servicecodes WHERE NewlyInserted = 0) AS ApplicableClients
            WHERE
                NewlyInserted = 1
                AND [Servicecodes].Client= [ApplicableClients].Client
                AND NOT EXISTS (SELECT 1 FROM @tempservicecodes 
                WHERE [Servicecodes].servicecode = [ApplicableClients].servicecode 
                AND [Servicecodes].clientid = [ApplicableClients].clientid)

delete from servicecodes where isNeedstobeDelete = 1

高效解决方案 #

方案1：直接通过EXISTS定位删除 #

跳过中间更新标记字段的步骤，直接用EXISTS定位符合条件的重复记录并删除，减少IO开销：

DELETE sc
FROM ServiceCodes sc
WHERE sc.NewlyInserted = 1
AND EXISTS (
    SELECT 1
    FROM ServiceCodes sc_exist
    WHERE sc_exist.Customer = sc.Customer
      AND sc_exist.ServiceCode = sc.ServiceCode
      AND sc_exist.NewlyInserted = 0
)

方案2：ROW_NUMBER分组标记删除（适合多重复场景） #

如果存在同一Customer+ServiceCode组合下多条重复记录，可通过窗口函数分组标记后删除：

WITH DuplicateCTE AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Customer, ServiceCode 
            ORDER BY NewlyInserted ASC -- 保留NewlyInserted=0的原始记录
        ) AS RowNum
    FROM ServiceCodes
)
DELETE FROM DuplicateCTE
WHERE RowNum > 1 AND NewlyInserted = 1

关键优化建议 #

• 创建复合索引：给ServiceCodes表添加索引，大幅提升查询匹配效率：

  CREATE NONCLUSTERED INDEX IX_ServiceCodes_Customer_ServiceCode_NewlyInserted 
  ON ServiceCodes (Customer, ServiceCode, NewlyInserted)
  

• 分批删除（大数据量场景）：避免一次性删除大量数据导致锁表，采用循环分批删除：

  WHILE 1=1
  BEGIN
      DELETE TOP (1000) sc
      FROM ServiceCodes sc
      WHERE sc.NewlyInserted = 1
      AND EXISTS (
          SELECT 1
          FROM ServiceCodes sc_exist
          WHERE sc_exist.Customer = sc.Customer
            AND sc_exist.ServiceCode = sc.ServiceCode
            AND sc_exist.NewlyInserted = 0
      )
      IF @@ROWCOUNT = 0 BREAK
  END
  

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