如何优化含WHILE循环的夜间T-SQL大规模行更新存储过程

问题背景 #

我有一个夜间执行的T-SQL存储过程，需更新数千万行数据，当前运行耗时约40分钟，希望缩短执行时间。

各表数据量 #

• T1：755行
• T2：87,548,735行
• T3：87,447,307行

存储过程代码 #

DECLARE @CT AS INT = 0;

SELECT     @CT = HB_FR_PERIOD      
 FROM    [Reset].[Hub_Fraud]   
WHERE DATEDIFF(DAY, HB_FR_CREATEDDATE, GETUTCDATE()) = 0

WHILE (@CT < 2)
BEGIN 
    UPDATE [SDP_Subscription].[dbo].[GEN_SUBSCRIBERS_HUB_FRAUD]
    SET 
     [HB_FD_UPDATEDDATE]   = CAST (  FORMAT (GETUTCDATE (), 'yyyy-MM-dd 00:00:00') AS DATETIME ), 
     [HB_FD_PR_Try_Flag]   = 0, 
     [HB_FD_PR_Try_Count]  = 0, 
     [HB_FD_PR_Try_Number]  = 0, 
     [HB_FD_PR_Billing_Flag]  = 0, 
     [HB_FD_PR_Billing_Count] = 0, 
     [HB_FD_PR_Billing_Number] = 0
    FROM
     [SDP_Subscription].[dbo].[GEN_SUBSCRIBERS_HUB_FRAUD] AS [TABLEA]
    INNER JOIN
     [SDP_Subscription].[dbo].[GEN_SUBSCRIBERS]    AS [TABLEB]
    ON 
     TABLEA.GEN_SB_ID_FK   = TABLEB.GEN_SB_ID
    WHERE
    (
     TABLEB.[GEN_SB_BILLING_SUCCESSDATE]         > TABLEB.[GEN_SB_INFO_PERIOD_DATE]
    AND 
     CONVERT(VARCHAR, GETUTCDATE()  - [TABLEB].[GEN_SB_INFO_PERIOD], 112) <= CONVERT(VARCHAR, TABLEB.[GEN_SB_BILLING_SUCCESSDATE], 112)  
    )
    OR 
    (
     [TABLEB].[GEN_SB_INFO_PERIOD] = 1
    )
;
    IF(@@ROWCOUNT >= 0)
     BEGIN
      UPDATE
       [Reset].[Hub_Fraud]
      SET
       HB_FR_PERIOD = HB_FR_PERIOD + 1
      WHERE
       DATEDIFF(DAY, HB_FR_CREATEDDATE, GETUTCDATE()) = 0
     END    
SET @CT = 0
SELECT 
    @CT = HB_FR_PERIOD       
FROM
    [Reset].[Hub_Fraud]    
WHERE
    DATEDIFF(DAY, HB_FR_CREATEDDATE, GETUTCDATE()) = 0
END

当前状态与疑问 #

已创建索引缩小扫描范围，但执行时间未降低，怀疑WHILE循环是性能瓶颈。请问该过程存在哪些反模式或优化方法？

编辑说明：已提供原始代码，移除了测试用的事务；此过程非我编写，仅负责优化，被告知循环存在出于业务需求。

反模式与优化方案 #

1. 循环逻辑冗余低效 #

当前循环最多执行2次，但每次都会全量更新符合条件的所有行——如果第一次更新已覆盖全部目标数据，第二次更新属于无意义的重复操作，会浪费大量资源。即使业务要求必须循环，也应在每次循环后过滤已更新的行，避免重复处理。

2. WHERE子句函数调用导致索引失效 #

• DATEDIFF(DAY, HB_FR_CREATEDDATE, GETUTCDATE()) = 0：对HB_FR_CREATEDDATE使用函数会阻止SQL Server使用该列索引，改为范围查询：

  HB_FR_CREATEDDATE >= CAST(GETUTCDATE() AS DATE) 
  AND HB_FR_CREATEDDATE < DATEADD(DAY, 1, CAST(GETUTCDATE() AS DATE))
  

• CONVERT(VARCHAR, GETUTCDATE() - [TABLEB].[GEN_SB_INFO_PERIOD], 112) <= CONVERT(VARCHAR, TABLEB.[GEN_SB_BILLING_SUCCESSDATE], 112)：将日期转字符串比较不仅性能差，还可能出现逻辑错误，改为日期直接计算：

  DATEADD(DAY, -[TABLEB].[GEN_SB_INFO_PERIOD], GETUTCDATE()) <= CAST(TABLEB.[GEN_SB_BILLING_SUCCESSDATE] AS DATE)
  

3. 不必要的变量重复查询 #

循环中反复查询[Reset].[Hub_Fraud]获取@CT值，可改为循环开始前获取一次，更新后直接在内存中递增，减少对小表的重复读写：

DECLARE @CT AS INT = 0;
SELECT @CT = HB_FR_PERIOD 
FROM [Reset].[Hub_Fraud] 
WHERE HB_FR_CREATEDDATE >= CAST(GETUTCDATE() AS DATE) 
  AND HB_FR_CREATEDDATE < DATEADD(DAY, 1, CAST(GETUTCDATE() AS DATE));

WHILE (@CT < 2)
BEGIN 
    -- 执行更新逻辑
    
    SET @CT = @CT + 1;
    UPDATE [Reset].[Hub_Fraud]
    SET HB_FR_PERIOD = @CT
    WHERE HB_FR_CREATEDDATE >= CAST(GETUTCDATE() AS DATE) 
      AND HB_FR_CREATEDDATE < DATEADD(DAY, 1, CAST(GETUTCDATE() AS DATE));
END

4. 大表全量更新的IO瓶颈 #

每次更新数千万行会产生大量日志，引发IO瓶颈。可将更新拆分为分批处理，每次更新固定行数（如10000行），减少单次事务的日志量和锁竞争：

WHILE (@CT < 2)
BEGIN 
    WHILE 1=1
    BEGIN
        UPDATE TOP(10000) [SDP_Subscription].[dbo].[GEN_SUBSCRIBERS_HUB_FRAUD]
        SET 
         [HB_FD_UPDATEDDATE]   = CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME), 
         [HB_FD_PR_Try_Flag]   = 0, 
         [HB_FD_PR_Try_Count]  = 0, 
         [HB_FD_PR_Try_Number]  = 0, 
         [HB_FD_PR_Billing_Flag]  = 0, 
         [HB_FD_PR_Billing_Count] = 0, 
         [HB_FD_PR_Billing_Number] = 0
        FROM
         [SDP_Subscription].[dbo].[GEN_SUBSCRIBERS_HUB_FRAUD] AS [TABLEA]
        INNER JOIN
         [SDP_Subscription].[dbo].[GEN_SUBSCRIBERS]    AS [TABLEB]
        ON 
         TABLEA.GEN_SB_ID_FK   = TABLEB.GEN_SB_ID
        WHERE
        (
         TABLEB.[GEN_SB_BILLING_SUCCESSDATE] > TABLEB.[GEN_SB_INFO_PERIOD_DATE]
        AND 
         DATEADD(DAY, -TABLEB.[GEN_SB_INFO_PERIOD], GETUTCDATE()) <= CAST(TABLEB.[GEN_SB_BILLING_SUCCESSDATE] AS DATE)
        )
        OR 
        (
         TABLEB.[GEN_SB_INFO_PERIOD] = 1
        )
        AND TABLEA.[HB_FD_UPDATEDDATE] <> CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME); -- 过滤已更新行
        
        IF @@ROWCOUNT = 0 BREAK;
    END
    
    SET @CT = @CT + 1;
    UPDATE [Reset].[Hub_Fraud]
    SET HB_FR_PERIOD = @CT
    WHERE HB_FR_CREATEDDATE >= CAST(GETUTCDATE() AS DATE) 
      AND HB_FR_CREATEDDATE < DATEADD(DAY, 1, CAST(GETUTCDATE() AS DATE));
END

5. 日期格式化冗余优化 #

CAST(FORMAT(GETUTCDATE(), 'yyyy-MM-dd 00:00:00') AS DATETIME)可简化为CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME)，性能更优且逻辑一致。

6. 索引优化建议 #

确保以下索引存在：

• [GEN_SUBSCRIBERS]：GEN_SB_ID（主键/聚集索引），以及包含GEN_SB_BILLING_SUCCESSDATE、GEN_SB_INFO_PERIOD_DATE、GEN_SB_INFO_PERIOD的非聚集索引
• [GEN_SUBSCRIBERS_HUB_FRAUD]：GEN_SB_ID_FK的非聚集索引，包含需要更新的列作为覆盖索引
• [Reset].[Hub_Fraud]：HB_FR_CREATEDDATE的非聚集索引，包含HB_FR_PERIOD

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