SQL Server中SELECT语句列中子查询的优化方案咨询

原查询中SELECT列包含多个相关子查询，这类子查询会对activity表的每一行单独执行一次，数据量大时效率极低。可以通过预聚合关联表的目标数据，再用JOIN关联的方式优化，具体方案如下：

优化思路 #

用窗口函数预计算text_view中每个act_id的最新记录（包含code和created_dt），以及act_mail中每个act_id的最早created_dt，再和activity表做关联，避免重复扫描关联表。

优化后的SQL #

WITH text_view_latest AS (
    SELECT 
        act_id,
        code,
        created_dt
    FROM (
        SELECT 
            act_id,
            code,
            created_dt,
            -- 按act_id分组，取created_dt最新的记录
            ROW_NUMBER() OVER (PARTITION BY act_id ORDER BY created_dt DESC) AS rn
        FROM text_view
    ) t
    WHERE rn = 1
),
act_mail_earliest AS (
    SELECT 
        act_id,
        created_dt AS mail_dt
    FROM (
        SELECT 
            act_id,
            created_dt,
            -- 按act_id分组，取created_dt最早的记录
            ROW_NUMBER() OVER (PARTITION BY act_id ORDER BY created_dt ASC) AS rn
        FROM act_mail
    ) m
    WHERE rn = 1
)
SELECT 
    t.id,
    t.status,
    CASE WHEN tv.code IN ('A', 'B', 'C', 'D', 'E') THEN tv.created_dt ELSE NULL END AS notedate,
    am.mail_dt
FROM activity t
-- 关联text_view的最新记录，确保notedate有值（对应原查询外层WHERE条件）
JOIN text_view_latest tv ON t.id = tv.act_id
LEFT JOIN act_mail_earliest am ON t.id = am.act_id
WHERE t.status NOT IN ('CLOSED', 'ACT', 'CANCELED')

额外性能提升建议 #

为关联表创建复合索引，让窗口函数的分组排序可以直接利用索引，避免额外排序开销：

• 给text_view创建索引：CREATE INDEX IX_text_view_act_id_created_dt ON text_view(act_id, created_dt DESC) INCLUDE(code);
• 给act_mail创建索引：CREATE INDEX IX_act_mail_act_id_created_dt ON act_mail(act_id, created_dt ASC);
• 给activity创建索引：CREATE INDEX IX_activity_status_id ON activity(status) INCLUDE(id);

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