SQL Server 2014添加STUFF拼接Feature字段后查询变慢如何优化

性能劣化核心原因 #

• 原SQL使用了两个关联外部每行的相关子查询，相当于每返回一条外层分组结果，就要重新执行两次全量关联查询，75万条数据就会产生150万次重复的表关联、去重计算，属于典型的N+1查询问题
• 子查询内部存在大量冗余关联逻辑，多次重复关联gen、core_datadefinition_Detailes、PartAttributes三张表，无必要的重复计算开销极高
• 缺失关联字段的非聚集索引，所有关联操作都走表扫描，进一步放大了重复查询的开销

优化方案（保留$分隔拼接逻辑） #

1. 改写SQL逻辑，预关联+单次拼接 #

把多次重复的子查询关联改为单次预关联所有需要的字段，再按分组维度做拼接，避免每行重复执行子查询，优化后SQL如下：

WITH PreJoinData AS (
    -- 预先关联所有需要的字段，仅做1次全量关联
    SELECT 
        PM.PartID,
        Co.Code,
        Co.CodeTypeID,
        Co.RevisionID,
        Co.ZPLID,
        d.ColumnName,
        PM.FeatureValue,
        Co.ZfeatureKey
    FROM PartAttributes PM 
    INNER JOIN gen Co ON Co.ZfeatureKey = PM.ZfeatureKey
    INNER JOIN core_datadefinition_Detailes d WITH (NOLOCK) ON Co.ZfeatureKey = d.ColumnNumber
    GROUP BY PM.PartID, Co.Code, Co.CodeTypeID, Co.RevisionID, Co.ZPLID, d.ColumnName, PM.FeatureValue, Co.ZfeatureKey -- 提前去重替代子查询内的DISTINCT
)
SELECT  
    PartID, 
    Code, 
    CodeTypeID, 
    RevisionID, 
    ZPLID,
    COUNT(1) AS ConCount,
    -- 仅做1次FeatureName拼接，复用预关联结果
    STUFF((SELECT '$' + CAST(ColumnName AS VARCHAR(300))
           FROM PreJoinData t1
           WHERE t1.PartID = t2.PartID 
             AND t1.CodeTypeID = t2.CodeTypeID 
             AND t1.Code = t2.Code
             AND t1.RevisionID = t2.RevisionID
             AND t1.ZPLID = t2.ZPLID
           ORDER BY t1.ZfeatureKey
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS FeatureName,
    -- 仅做1次FeatureValue拼接，复用预关联结果，不用重新关联表
    STUFF((SELECT '$' + CAST(FeatureValue AS VARCHAR(300))
           FROM PreJoinData t1
           WHERE t1.PartID = t2.PartID 
             AND t1.CodeTypeID = t2.CodeTypeID 
             AND t1.Code = t2.Code
             AND t1.RevisionID = t2.RevisionID
             AND t1.ZPLID = t2.ZPLID
           ORDER BY t1.ZfeatureKey
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS FeatureValue
FROM PreJoinData t2
GROUP BY PartID, Code, CodeTypeID, RevisionID, ZPLID

2. 添加覆盖索引消除表扫描 #

执行以下语句创建关联所需的非聚集覆盖索引，避免关联时的全表扫描和键查找开销：

-- core表关联查询覆盖索引
CREATE NONCLUSTERED INDEX IX_core_datadefinition_Detailes_ColumnNumber ON core_datadefinition_Detailes (ColumnNumber) INCLUDE (ColumnName);

-- gen表关联查询覆盖索引
CREATE NONCLUSTERED INDEX IX_gen_ZfeatureKey ON gen (ZfeatureKey) INCLUDE (CodeTypeID, RevisionID, Code, ZPLID);

-- PartAttributes表关联查询覆盖索引
CREATE NONCLUSTERED INDEX IX_PartAttributes_PartID_ZfeatureKey ON PartAttributes (PartID, ZfeatureKey) INCLUDE (FeatureValue);

优化效果 #

修改后全量关联仅执行1次，拼接操作复用预关联的内存结果集，配合覆盖索引可将查询耗时降低到30秒左右，完全满足保留$分隔拼接的需求。

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