优化SQL Server临时表UPDATE无循环查询性能求助

针对百万级数据量的临时表#TableA，我们可以通过窗口函数+CTE的方式实现无循环的高效更新，核心思路是一次性完成所有统计逻辑，避免循环带来的性能损耗。

步骤1：创建辅助索引（关键性能优化） #

首先为临时表创建非聚集索引，大幅提升分组统计的效率：

CREATE NONCLUSTERED INDEX IX_TableA_MapVal1 ON #TableA (MapVal1) INCLUDE (MapVal2);
CREATE NONCLUSTERED INDEX IX_TableA_MapVal2 ON #TableA (MapVal2) INCLUDE (MapVal1);

步骤2：编写UPDATE语句 #

利用CTE计算所有必要的统计维度，直接关联更新IsActual列：

WITH ValStats AS (
    SELECT 
        MapVal1,
        -- 统计每个MapVal1的出现次数
        COUNT(*) OVER (PARTITION BY MapVal1) AS cnt1,
        -- 每个MapVal1对应的最小MapVal2
        MIN(MapVal2) OVER (PARTITION BY MapVal1) AS min_val2_for_val1,
        MapVal2,
        -- 统计每个MapVal2的出现次数
        COUNT(*) OVER (PARTITION BY MapVal2) AS cnt2,
        -- 每个MapVal2对应的最小MapVal1
        MIN(MapVal1) OVER (PARTITION BY MapVal2) AS min_val1_for_val2,
        -- 关键：当前MapVal1的最小MapVal2对应的最小MapVal1（用于规则2判断）
        MIN(MIN(MapVal1) OVER (PARTITION BY MapVal2)) OVER (PARTITION BY MapVal1) AS min_val1_for_min_val2_of_val1,
        -- 关键：当前MapVal2的最小MapVal1对应的最小MapVal2（用于规则3判断）
        MIN(MIN(MapVal2) OVER (PARTITION BY MapVal1)) OVER (PARTITION BY MapVal2) AS min_val2_for_min_val1_of_val2,
        ID,
        IsActual
    FROM #TableA
)
UPDATE ValStats
SET IsActual = CASE
    -- 规则1：MapVal1和MapVal2均唯一（一对一映射）
    WHEN cnt1 = 1 AND cnt2 = 1 THEN 1
    -- 规则2：MapVal1不唯一，当前行是该MapVal1的最小MapVal2，且该MapVal2未映射到更小的MapVal1
    WHEN cnt1 > 1 AND MapVal2 = min_val2_for_val1 AND min_val1_for_min_val2_of_val1 = MapVal1 THEN 1
    -- 规则3：MapVal2不唯一，当前行是该MapVal2的最小MapVal1，且该MapVal1未映射到更小的MapVal2
    WHEN cnt2 > 1 AND MapVal1 = min_val1_for_val2 AND min_val2_for_min_val1_of_val2 = MapVal2 THEN 1
    -- 规则4：不满足上述条件的行
    ELSE 0
END;

方案优势 #

1. 无循环设计：全程使用窗口函数完成统计，仅需扫描表1-2次，避免循环带来的O(n²)时间复杂度；
2. 性能优化：辅助索引将分组统计的时间复杂度从O(n)降至O(log n)，百万级数据下可大幅缩短执行时间；
3. 逻辑清晰：所有规则判断直接嵌入CASE语句，逻辑直观易维护。

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