SQL Server中含ROW_NUMBER的STUFF语句查询变慢的优化咨询

我之前碰到过类似的场景，核心问题是CTE在嵌套子查询中无法被优化器有效利用——哪怕总数据量只有350条，每次执行STUFF子查询时，SQL Server都会重新扫描整个CTE来过滤RN <=6，叠加两次STUFF调用就把执行时间拖慢到了25秒。下面给你几个不用临时表、适合做成视图的优化方案：

方案1：提前在CTE中筛选前6条记录 #

既然Data_1只需要每个Program的最近6条，我们直接在CTE阶段就把RN <=6的记录过滤掉，后续STUFF子查询只处理已经筛选好的小数据集，避免重复计算和过滤：

CREATE VIEW Your_View_Name
AS
-- 2016版本需要用子查询嵌套来过滤前6条（不支持QUALIFY）
WITH CTE_Top6 AS (
    SELECT *
    FROM (
        SELECT 
            P.Program_Number,
            P.Date_Status,
            '{"date":"' + P.Date_Status_Display + '","percent":"' + P.Percent_Complete + '","status":"' + P.Status_Overall_Col + '"}' AS JSON_String,
            ROW_NUMBER() OVER (PARTITION BY P.Program_Number ORDER BY P.Date_Status DESC) AS RN
        FROM dbo.Main_Entries_Table
    ) t
    WHERE RN <= 6
),
CTE_All AS (
    SELECT 
        P.Program_Number,
        P.Date_Status,
        '{"date":"' + P.Date_Status_Display + '","percent":"' + P.Percent_Complete + '","status":"' + P.Status_Overall_Col + '"}' AS JSON_String
    FROM dbo.Main_Entries_Table
)
SELECT 
    P.[Program_Number],
    P.[Program_Name],
    -- 用提前过滤好的CTE生成Data_1
    '[' + ISNULL(STUFF((
        SELECT ',' + [JSON_String]
        FROM CTE_Top6 C
        WHERE C.Program_Number = P.Program_Number
        ORDER BY RN DESC -- RN=1是最新记录，DESC排序实现从旧到新
        FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,''), '') + ']' AS Data_1,
    -- Data_2用全量CTE保持原有逻辑
    '[' + ISNULL(STUFF((
        SELECT ',' + [JSON_String]
        FROM CTE_All C
        WHERE C.Program_Number = P.Program_Number
        ORDER BY Date_Status ASC
        FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,''), '') + ']' AS Data_2,
    P.Last_Updated
FROM dbo.Main_Entries_Table P
GROUP BY P.Program_Number, P.Program_Name, P.Last_Updated; -- 去重，避免原表重复Program记录影响结果

方案2：用APPLY替代嵌套子查询（更高效） #

APPLY运算符能让子查询和外层查询更好地关联，SQL Server的查询优化器更容易生成高效的执行计划，尤其是在有分区过滤的场景：

CREATE VIEW Your_View_Name
AS
SELECT 
    P.[Program_Number],
    P.[Program_Name],
    '[' + ISNULL(APPLY_Top6.Data_1_Json, '') + ']' AS Data_1,
    '[' + ISNULL(APPLY_All.Data_2_Json, '') + ']' AS Data_2,
    P.Last_Updated
FROM dbo.Main_Entries_Table P
-- 生成Data_1：每个Program的最近6条，按从旧到新排序
OUTER APPLY (
    SELECT STUFF((
        SELECT ',' + '{"date":"' + C.Date_Status_Display + '","percent":"' + C.Percent_Complete + '","status":"' + C.Status_Overall_Col + '"}'
        FROM (
            SELECT 
                Date_Status_Display,
                Percent_Complete,
                Status_Overall_Col,
                ROW_NUMBER() OVER (PARTITION BY Program_Number ORDER BY Date_Status DESC) AS RN
            FROM dbo.Main_Entries_Table
            WHERE Program_Number = P.Program_Number
        ) C
        WHERE C.RN <=6
        ORDER BY C.RN DESC
        FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS Data_1_Json
) APPLY_Top6
-- 生成Data_2：全量记录按时间升序
OUTER APPLY (
    SELECT STUFF((
        SELECT ',' + '{"date":"' + C.Date_Status_Display + '","percent":"' + C.Percent_Complete + '","status":"' + C.Status_Overall_Col + '"}'
        FROM dbo.Main_Entries_Table C
        WHERE C.Program_Number = P.Program_Number
        ORDER BY C.Date_Status ASC
        FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS Data_2_Json
) APPLY_All
GROUP BY P.Program_Number, P.Program_Name, P.Last_Updated, APPLY_Top6.Data_1_Json, APPLY_All.Data_2_Json;

关键优化补充 #

1. 避免字符转义问题：原代码的FOR XML PATH('')会自动转义特殊字符（比如双引号），加上TYPE后用.value()提取可以解决这个问题，同时性能更优。
2. 添加覆盖索引：给Main_Entries_Table创建复合索引，让整个查询几乎不用回表：

   CREATE NONCLUSTERED INDEX IX_Main_Entries_Table_Program_DateStatus
   ON dbo.Main_Entries_Table (Program_Number, Date_Status DESC)
   INCLUDE (Date_Status_Display, Percent_Complete, Status_Overall_Col, Program_Name, Last_Updated);
   

   这个索引会覆盖ROW_NUMBER计算、过滤和STUFF所需的所有字段，大幅提升查询速度。

原查询慢的根本原因 #

原CTE没有提前过滤RN <=6，每次执行STUFF子查询时，SQL Server都会扫描整个CTE的所有记录，再过滤出当前Program的RN <=6数据——相当于对每个Program都做一次全表扫描，叠加两次STUFF就产生了大量重复计算，导致时间飙升。

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