SQL标量值函数性能优化：改写fn_ConcatenateCourseEnrolmentTags为内联函数

我正在尝试优化查询，移除返回逗号分隔标签列表（例如'cor tag 1, tag test, testing, test.'）的标量值函数。了解到将其改为内联函数可提升性能，恳请协助将我的用户定义函数改写为内联函数，或提供性能优化建议。

原标量值函数代码 #

-- Description: Returns a comma separated list of Tags for a defined Course Enrolment
--      
-- =============================================

/****** Object:  UserDefinedFunction [dbo].[fn_ConcatenateCourseEnrolmentTags] */
CREATE FUNCTION [dbo].[fn_ConcatenateCourseEnrolmentTags] (@ClientOfferRegID As int)  
    RETURNS nvarchar(3999) AS  
BEGIN 
    DECLARE @Desc nvarchar(3999)

    SELECT @Desc = IsNull(@Desc + ', ', '') + dbo.tbl_Tag_Shared.Name
FROM         dbo.tbl_Tag_Shared INNER JOIN
                      dbo.tbl_Record_Tag ON dbo.tbl_Tag_Shared.TagId = dbo.tbl_Record_Tag.TagId INNER JOIN
                      dbo.tbl_Entity ON dbo.tbl_Tag_Shared.EntityId = dbo.tbl_Entity.EntityId
WHERE     (dbo.tbl_Entity.ObjectId = '06') AND (dbo.tbl_Record_Tag.RecordId = @ClientOfferRegID)
ORDER BY dbo.tbl_Tag_Shared.Name
    
    RETURN @Desc
END

GO

原查询代码片段 #

SELECT
    tbl_Client.ClientID AS LearnerId, 
    tbl_Client.FirstName AS LearnerFirstName, 
    tbl_Client.LastName AS LearnerLastName, 
    dbo.fn_ConcatenateCourseEnrolmentTags(tbl__Client_Offer_Reg.ClientOfferRegID) AS Tags,
FROM        
    tbl_Client
    ...
GROUP BY    
    tbl_Client.ClientID, 
    tbl_Client.FirstName, 
    tbl_Client.LastName,
    dbo.fn_ConcatenateCourseEnrolmentTags(tbl_Client_Offer_Reg.ClientOfferRegID),

优化方案 #

方案1：改写为内联表值函数（兼容SQL Server 2016及更早版本） #

内联表值函数能被查询优化器更好地解析，避免标量函数逐行调用的性能开销。

SQL Server 2017+版本（支持STRING_AGG）

CREATE FUNCTION [dbo].[fn_ConcatenateCourseEnrolmentTags_Inline] (@ClientOfferRegID As int)  
RETURNS TABLE
AS
RETURN (
    SELECT STRING_AGG(ts.Name, ', ') WITHIN GROUP (ORDER BY ts.Name) AS Tags
    FROM dbo.tbl_Tag_Shared ts
    INNER JOIN dbo.tbl_Record_Tag rt ON ts.TagId = rt.TagId
    INNER JOIN dbo.tbl_Entity e ON ts.EntityId = e.EntityId
    WHERE e.ObjectId = '06' AND rt.RecordId = @ClientOfferRegID
)
GO

SQL Server 2016及更早版本（用FOR XML PATH实现）

CREATE FUNCTION [dbo].[fn_ConcatenateCourseEnrolmentTags_Inline] (@ClientOfferRegID As int)  
RETURNS TABLE
AS
RETURN (
    SELECT STUFF((
        SELECT ', ' + ts.Name
        FROM dbo.tbl_Tag_Shared ts
        INNER JOIN dbo.tbl_Record_Tag rt ON ts.TagId = rt.TagId
        INNER JOIN dbo.tbl_Entity e ON ts.EntityId = e.EntityId
        WHERE e.ObjectId = '06' AND rt.RecordId = @ClientOfferRegID
        ORDER BY ts.Name
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(3999)'), 1, 2, '') AS Tags
)
GO

方案2：直接在主查询中集成拼接逻辑（推荐，SQL Server 2017+） #

如果版本支持STRING_AGG，可以完全去掉函数调用，直接在查询中处理标签拼接，性能最优：

SELECT
    c.ClientID AS LearnerId, 
    c.FirstName AS LearnerFirstName, 
    c.LastName AS LearnerLastName, 
    ISNULL(t.Tags, '') AS Tags
FROM        
    tbl_Client c
    -- 补充原查询的其他关联条件
    LEFT JOIN tbl__Client_Offer_Reg cor ON ... 
    LEFT JOIN (
        SELECT 
            rt.RecordId,
            STRING_AGG(ts.Name, ', ') WITHIN GROUP (ORDER BY ts.Name) AS Tags
        FROM dbo.tbl_Tag_Shared ts
        INNER JOIN dbo.tbl_Record_Tag rt ON ts.TagId = rt.TagId
        INNER JOIN dbo.tbl_Entity e ON ts.EntityId = e.EntityId
        WHERE e.ObjectId = '06'
        GROUP BY rt.RecordId
    ) t ON cor.ClientOfferRegID = t.RecordId
GROUP BY    
    c.ClientID, 
    c.FirstName, 
    c.LastName,
    t.Tags

性能优化建议 #

• 弃用标量值函数：标量函数会逐行执行，大数据量下性能极差，内联表值函数或直接拼接逻辑能让查询优化器生成更高效的执行计划。
• 索引优化：为tbl_Record_Tag.RecordId、tbl_Tag_Shared.TagId、tbl_Entity.EntityId和tbl_Entity.ObjectId创建合适的索引，减少关联查询时的查找开销。
• 优先用STRING_AGG：SQL Server 2017及以上版本中，STRING_AGG比FOR XML PATH更简洁，性能表现也更优。

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