本地SSMS中带Eager Spool的MS SQL Server慢UPDATE查询优化求助

• 环境与现状：本地运行Microsoft SQL Server 2019 Developer Edition，通过SSMS执行UPDATE查询，为关联字段添加索引后耗时从120秒降至85秒，但仍远慢于同表内数据类型相似的1秒级查询。
• 原始查询语句：

UPDATE SummaryTable 
SET LifeTeacherTopScore = (SELECT COUNT(*) 
                           FROM WorkTable AS T1 
                           WHERE T1.Z_date < SummaryTable.Z_date 
                             AND T1.Teacher = SummaryTable.Teacher 
                             AND T1.Score = 1)
WHERE SummaryTable.Teacher <> ''

• 表与字段信息：
  • SummaryTable：约1万行、30列，包含VARCHAR、FLOAT、INT类型；
  • WorkTable：约750万行，列数和数据类型与SummaryTable类似；
  • 关键字段：Z_date(DATE，约1万唯一值)、Teacher(VARCHAR(50)，约2.5万唯一值，含150万条NULL值)、Score(INT，约30唯一值)；查询通过SummaryTable.Teacher <> ''排除空值。
• 执行计划问题：存在占比87%的Index Spool (Eager Spool)，已在WorkTable的Z_date、Teacher、Score字段创建非聚集索引，但性能未达预期。

1. 调整索引结构，实现覆盖查询 #

当前索引列顺序未匹配查询的过滤与关联逻辑，建议创建定向覆盖非聚集索引，并排除无效数据缩小索引体积：

CREATE NONCLUSTERED INDEX IX_WorkTable_Teacher_Score_ZDate 
ON WorkTable (Teacher, Score, Z_date)
WHERE Teacher IS NOT NULL;

理由：将关联字段Teacher放在索引首位，其次是过滤条件Score，最后是范围筛选的Z_date，索引可直接满足COUNT(*)的统计需求，避免回表或临时spool操作。

2. 替换相关子查询为预聚合JOIN #

原查询为逐行执行的相关子查询，相当于对SummaryTable的1万行执行1万次WorkTable扫描。改用窗口函数预聚合+JOIN的方式，仅扫描WorkTable一次：

WITH TeacherScoreCumulative AS (
    SELECT 
        Teacher,
        Z_date,
        -- 统计每个Teacher在当前Z_date之前的Score=1累计数
        COUNT(*) OVER (
            PARTITION BY Teacher 
            ORDER BY Z_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS CumulativeTopScore
    FROM WorkTable
    WHERE Score = 1 AND Teacher IS NOT NULL
)
UPDATE st
SET st.LifeTeacherTopScore = ISNULL(tsc.CumulativeTopScore, 0)
FROM SummaryTable st
LEFT JOIN TeacherScoreCumulative tsc 
    ON st.Teacher = tsc.Teacher 
    AND tsc.Z_date = st.Z_date
WHERE st.Teacher <> '';

理由：窗口函数一次性完成所有Teacher的累计计数，彻底避免逐行子查询的重复扫描开销。

3. 更新统计信息，确保优化器决策准确 #

执行计划异常可能源于过时的统计信息，强制更新全量统计：

UPDATE STATISTICS WorkTable WITH FULLSCAN;
UPDATE STATISTICS SummaryTable WITH FULLSCAN;

理由：让查询优化器获取最新的数据分布，避免因统计偏差选择低效执行路径。

4. 分批更新（可选） #

若单次更新造成锁等待或日志压力，可拆分批次执行：

DECLARE @BatchSize INT = 1000;
DECLARE @MaxTeacher VARCHAR(50) = '';

WHILE EXISTS (SELECT 1 FROM SummaryTable WHERE Teacher <> '' AND Teacher > @MaxTeacher)
BEGIN
    UPDATE TOP (@BatchSize) st
    SET LifeTeacherTopScore = (
        SELECT COUNT(*) 
        FROM WorkTable T1 
        WHERE T1.Z_date < st.Z_date 
          AND T1.Teacher = st.Teacher 
          AND T1.Score = 1
    )
    FROM SummaryTable st
    WHERE st.Teacher <> '' AND st.Teacher > @MaxTeacher
    ORDER BY st.Teacher;

    SET @MaxTeacher = (SELECT MAX(Teacher) FROM SummaryTable WHERE Teacher <> '' AND Teacher > @MaxTeacher);
END

理由：减少单次事务的锁范围与日志生成，避免长时间阻塞。

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