如何优化含双重循环的SQL Server查询以缩短执行时间

用户问题 #

我是本群组新手，我的SQL Server查询执行耗时过长，虽已创建索引但问题仍未解决。当前查询使用双重循环，涉及的SI2表约有2500万行，具体代码如下：

DECLARE @SurvenanceLimite INT = 2023;
DECLARE @SURV INT = 2015;

WHILE @SURV <= @SurvenanceLimite
BEGIN
    DECLARE @AnneeSurvenance INT = @SURV;
    DECLARE @AnneeObservation INT = 2023;
    
    WHILE @AnneeSurvenance <= @AnneeObservation
    BEGIN
        INSERT INTO #LastSit
        SELECT *,
               @SURV AS SURV,
               @AnneeSurvenance AS Annee
        FROM (
            SELECT 
                CD_STE_CPTA,
                --STE_GEST,
                CD_BRCH_CPTA,
                CD_BRCH_GEST,
                GAR_GEST,
                GAR_CPTA,
                CD_DESC_SIN,
                CD_CAT_NAT,
                EXER_INV,
                EXER_SIN,
                NUM_SIN,
                DAT_SIT,
                MT_VAL_CUM,
                ROW_NUMBER() OVER (PARTITION BY EXER_SIN, NUM_SIN, GAR_CPTA, GAR_GEST ORDER BY DAT_SIT DESC, NUM_MVT DESC) AS RN,
                NUM_MVT
            FROM SI2
            WHERE DAT_SIT <= DATEFROMPARTS(@AnneeSurvenance, 08, 31) AND EXER_SIN = @SURV
        ) AS C 
        WHERE RN = 1;

        SET @AnneeSurvenance = @AnneeSurvenance + 1;
    END;
  
    SET @SURV = @SURV + 1;
END;

优化建议 #

1. 替换双重循环为集合式操作 #

原查询通过双重循环逐年份扫描SI2表，总扫描次数达45次（2015-2023共9个SURV值，每个对应多轮年份循环），重复扫描大表是性能瓶颈核心。改用集合操作一次性处理所有年份组合：

DECLARE @SurvenanceLimite INT = 2023;
DECLARE @StartSURV INT = 2015;

-- 生成所有需要的SURV与AnneeSurvenance组合
WITH YearCombos AS (
    SELECT 
        s.SURV,
        a.AnneeSurvenance
    FROM (
        SELECT TOP (@SurvenanceLimite - @StartSURV + 1)
            @StartSURV + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS SURV
        FROM sys.all_columns ac1
    ) s
    CROSS JOIN (
        SELECT TOP (@SurvenanceLimite - @StartSURV + 1)
            @StartSURV + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS AnneeSurvenance
        FROM sys.all_columns ac2
    ) a
    WHERE a.AnneeSurvenance >= s.SURV AND a.AnneeSurvenance <= @SurvenanceLimite
),
-- 获取各分组在对应年份截止日前的最新记录
LatestRecords AS (
    SELECT 
        si2.CD_STE_CPTA,
        si2.CD_BRCH_CPTA,
        si2.CD_BRCH_GEST,
        si2.GAR_GEST,
        si2.GAR_CPTA,
        si2.CD_DESC_SIN,
        si2.CD_CAT_NAT,
        si2.EXER_INV,
        si2.EXER_SIN,
        si2.NUM_SIN,
        si2.DAT_SIT,
        si2.MT_VAL_CUM,
        si2.NUM_MVT,
        yc.SURV,
        yc.AnneeSurvenance AS Annee,
        ROW_NUMBER() OVER(
            PARTITION BY yc.SURV, yc.AnneeSurvenance, si2.EXER_SIN, si2.NUM_SIN, si2.GAR_CPTA, si2.GAR_GEST 
            ORDER BY si2.DAT_SIT DESC, si2.NUM_MVT DESC
        ) AS RN
    FROM SI2 si2
    JOIN YearCombos yc 
        ON si2.EXER_SIN = yc.SURV 
        AND si2.DAT_SIT <= DATEFROMPARTS(yc.AnneeSurvenance, 08, 31)
)
INSERT INTO #LastSit
SELECT 
    CD_STE_CPTA,
    CD_BRCH_CPTA,
    CD_BRCH_GEST,
    GAR_GEST,
    GAR_CPTA,
    CD_DESC_SIN,
    CD_CAT_NAT,
    EXER_INV,
    EXER_SIN,
    NUM_SIN,
    DAT_SIT,
    MT_VAL_CUM,
    NUM_MVT,
    SURV,
    Annee
FROM LatestRecords
WHERE RN = 1;

2. 重建覆盖索引 #

原索引未覆盖查询所需全部字段，导致大量回表操作。创建以下覆盖索引，直接满足WHERE过滤、ROW_NUMBER排序及SELECT字段需求：

CREATE NONCLUSTERED INDEX IX_SI2_EXER_SIN_DAT_SIT
ON SI2 (EXER_SIN, DAT_SIT DESC, NUM_MVT DESC)
INCLUDE (
    CD_STE_CPTA, CD_BRCH_CPTA, CD_BRCH_GEST, GAR_GEST, GAR_CPTA,
    CD_DESC_SIN, CD_CAT_NAT, EXER_INV, NUM_SIN, MT_VAL_CUM
);

3. 优化临时表#LastSit #

• 提前定义表结构并创建索引，避免隐式转换及后续查询的性能损耗：

-- 替换[类型]为实际字段数据类型
CREATE TABLE #LastSit (
    CD_STE_CPTA VARCHAR(50),
    CD_BRCH_CPTA VARCHAR(50),
    CD_BRCH_GEST VARCHAR(50),
    GAR_GEST VARCHAR(50),
    GAR_CPTA VARCHAR(50),
    CD_DESC_SIN VARCHAR(100),
    CD_CAT_NAT VARCHAR(50),
    EXER_INV INT,
    EXER_SIN INT,
    NUM_SIN VARCHAR(50),
    DAT_SIT DATE,
    MT_VAL_CUM DECIMAL(18,2),
    NUM_MVT INT,
    SURV INT,
    Annee INT
);

CREATE CLUSTERED INDEX IX_LastSit_SURV_Annee ON #LastSit (SURV, Annee);

• 若使用SQL Server 2016+，可启用内存优化临时表提升插入速度：

CREATE TABLE #LastSit (
    -- 字段定义同上
) WITH (MEMORY_OPTIMIZED = ON);

4. 辅助优化措施 #

• 更新SI2表统计信息，确保查询优化器生成最优执行计划：

UPDATE STATISTICS SI2 WITH FULLSCAN;

• 提前计算所有年份的截止日期，避免在JOIN逻辑中重复计算：

WITH YearCombos AS (
    SELECT 
        s.SURV,
        a.AnneeSurvenance,
        DATEFROMPARTS(a.AnneeSurvenance, 08, 31) AS CutoffDate
    -- 后续逻辑中直接使用CutoffDate代替重复计算
)

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