如何优化含多子查询的字符串聚合SQL查询？

原查询因重复执行三次近乎一致的关联子查询，再加上隐式类型转换的影响，导致查询耗时偏高。以下是具体优化思路和优化后的语句：

核心优化点 #

• 减少重复关联计算：原查询对每个WorkPlanID重复三次关联UserLogin、Vendors、WorkPlanVendors表，改成先一次性关联所有所需表并聚合字段，避免重复运算。
• 消除隐式转换损耗：CAST(v.VendorID AS INT)会导致Vendors表的VendorID索引无法被利用（若存在索引），优先建议统一Vendors.VendorID与WorkPlanVendors.VendorID的字段类型；若无法修改字段类型，可在Vendors表上创建基于CAST(VendorID AS INT)的计算列索引。
• 添加针对性索引：为关联字段创建联合索引，加速表关联速度：
  • WorkPlanVendors：创建(WorkPlanID, VendorID)联合索引
  • Vendors：创建(UserLoginID, VendorID, PrimaryPhone)联合索引
  • UserLogin：确保UserLoginID为主键或有单独索引，同时可创建(UserLoginID, FirstName, LastName, Email)联合索引

优化后的SQL语句 #

WITH VendorWorkplanCTE AS (
    SELECT 
        wpv.WorkPlanID,
        STRING_AGG(ISNULL(ul.FirstName + ' ', '') + ISNULL(ul.LastName, ''), ', ') AS VendorName,
        STRING_AGG(v.PrimaryPhone, ', ') AS PrimaryPhone,
        STRING_AGG(ul.Email, ', ') AS Email
    FROM dbo.WorkPlanVendors wpv
    INNER JOIN Vendors v ON wpv.VendorID = CAST(v.VendorID AS INT) -- 优先统一字段类型以移除CAST
    INNER JOIN UserLogin ul ON v.UserLoginID = ul.UserLoginID
    GROUP BY wpv.WorkPlanID
)
SELECT 
    wp.WorkplanID,
    ISNULL(vcte.VendorName, '') AS VendorName,
    ISNULL(vcte.PrimaryPhone, '') AS PrimaryPhone,
    ISNULL(vcte.Email, '') AS Email
FROM WorkPlan wp
LEFT JOIN VendorWorkplanCTE vcte ON wp.WorkPlanID = vcte.WorkPlanID

兼容低版本SQL Server的备选方案 #

若你的SQL Server版本低于2017（不支持STRING_AGG函数），可在CTE内一次性完成字段拼接，避免三次重复关联：

WITH VendorWorkplanCTE AS (
    SELECT 
        wpv.WorkPlanID,
        STUFF((SELECT ', ' + ISNULL(ul2.FirstName + ' ', '') + ISNULL(ul2.LastName, '')
               FROM Vendors v2
               INNER JOIN UserLogin ul2 ON v2.UserLoginID = ul2.UserLoginID
               WHERE wpv.VendorID = CAST(v2.VendorID AS INT)
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS VendorName,
        STUFF((SELECT ', ' + v2.PrimaryPhone
               FROM Vendors v2
               INNER JOIN UserLogin ul2 ON v2.UserLoginID = ul2.UserLoginID
               WHERE wpv.VendorID = CAST(v2.VendorID AS INT)
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS PrimaryPhone,
        STUFF((SELECT ', ' + ul2.Email
               FROM Vendors v2
               INNER JOIN UserLogin ul2 ON v2.UserLoginID = ul2.UserLoginID
               WHERE wpv.VendorID = CAST(v2.VendorID AS INT)
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Email
    FROM dbo.WorkPlanVendors wpv
    GROUP BY wpv.WorkPlanID
)
SELECT 
    wp.WorkplanID,
    ISNULL(vcte.VendorName, '') AS VendorName,
    ISNULL(vcte.PrimaryPhone, '') AS PrimaryPhone,
    ISNULL(vcte.Email, '') AS Email
FROM WorkPlan wp
LEFT JOIN VendorWorkplanCTE vcte ON wp.WorkPlanID = vcte.WorkPlanID

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