优化FOR XML PATH+STUFF实现的SQL查询 解决Lazy Spool性能问题

问题根源 #

你遇到的性能问题核心是同一份关联逻辑重复执行了3次，每个STUFF拼接都单独跑一遍三表关联和外层匹配，数据库为了缓存重复的中间数据生成了Lazy Spool，数据量一大就会产生大量IO开销。

优化思路 #

• 合并重复子查询：3次拼接用的是完全相同的关联和过滤逻辑，先一次性按LOT_0聚合出所有需要的字段，再做拼接，避免重复关联计算
• 替换字符串拼接逻辑：SQL Server 2017及以上版本使用原生STRING_AGG函数替代FOR XML PATH拼接，性能提升明显且写法更简洁
• 精简冗余语法：外层GROUP BY LOT_0已经保证LOT_0唯一，不需要再加DISTINCT，直接删除即可
• 新增覆盖索引：针对关联、过滤、查询字段创建覆盖索引，避免全表扫描和回表查询
  • TOLOT表：创建联合索引 INDEX IX_TOLOT_LOT_ITMREF (LOT_0, ITMREF_0)，覆盖外层分组、子查询关联条件
  • ETMM表：创建联合索引 INDEX IX_ETMM_ITMREF_TSICOD (ITMREF_0, TSICOD_0, TSICOD_6)，覆盖关联条件和TSICOD_0='OP'的过滤条件
  • SICOD6表：创建联合索引 INDEX IX_SICOD6_ID_DES (ID_0, SHODES_0, LNGDES_0)，覆盖关联条件和需要拼接的三个字段，同时满足LNGDES_0<>''的过滤需求
• 前置过滤逻辑：把LOT_0 <> ''的过滤条件放到外层查询先执行，减少子查询需要处理的数据量

优化后代码（SQL Server 2017及以上，性能最优）

SELECT 
    LOT_0,
    VarCode = STRING_AGG(DISTINCT IT6.ID_0, ', '),
    VarShort = STRING_AGG(DISTINCT IT6.SHODES_0, ', '),
    VarLong = STRING_AGG(DISTINCT IT6.LNGDES_0, ', ')
FROM TOLOT AS S2
INNER JOIN ETMM I ON I.ITMREF_0 = S2.ITMREF_0
INNER JOIN SICOD6 IT6 ON IT6.ID_0 = I.TSICOD_6
WHERE 
    IT6.LNGDES_0 <> '' 
    AND S2.LOT_0 <> '' 
    AND I.TSICOD_0 = 'OP'
GROUP BY LOT_0

兼容低版本SQL Server的优化代码

WITH BaseData AS (
    SELECT DISTINCT 
        S2.LOT_0,
        IT6.ID_0,
        IT6.SHODES_0,
        IT6.LNGDES_0
    FROM TOLOT AS S2
    INNER JOIN ETMM I ON I.ITMREF_0 = S2.ITMREF_0
    INNER JOIN SICOD6 IT6 ON IT6.ID_0 = I.TSICOD_6
    WHERE 
        IT6.LNGDES_0 <> '' 
        AND S2.LOT_0 <> '' 
        AND I.TSICOD_0 = 'OP'
)
SELECT 
    LOT_0,
    VarCode = STUFF(
        (SELECT ', ' + ID_0 FROM BaseData b WHERE b.LOT_0 = a.LOT_0 FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 
        1, 2, ''
    ),
    VarShort = STUFF(
        (SELECT ', ' + SHODES_0 FROM BaseData b WHERE b.LOT_0 = a.LOT_0 FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 
        1, 2, ''
    ),
    VarLong = STUFF(
        (SELECT ', ' + LNGDES_0 FROM BaseData b WHERE b.LOT_0 = a.LOT_0 FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 
        1, 2, ''
    )
FROM BaseData a
GROUP BY LOT_0

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