SQL Azure标量函数性能优化：转内联表值函数需求

核心思路 #

标量函数在SQL Azure中会触发逐行处理逻辑，导致严重性能瓶颈。转换为内联表值函数(iTVF) 能让查询优化器生成更高效的执行计划，关键是将原函数中的变量赋值、分支判断和最终输出修正逻辑全部转换为纯集合操作。

转换后的内联函数代码 #

CREATE FUNCTION [dbo].[udf_GetLSLineDescByGUIDLocalised_Inline] (
    @ComputationID INT,
    @AccountGUID UNIQUEIDENTIFIER,
    @LSCode NVARCHAR(50),
    @SpecificLevelOnly INT = -1,
    @LanguageID INT
)
RETURNS TABLE
AS
RETURN (
    WITH FormatTokenCTE AS (
        SELECT ResourceString AS FormatToken
        FROM dbo.tbl_ResourceString WITH(NOLOCK)
        WHERE ResourceKey = 'General.AccountSeparator' AND LanguageID = @LanguageID
    ),
    BSData AS (
        SELECT 
            CASE
                WHEN @SpecificLevelOnly > 0 THEN
                    CASE
                        WHEN @SpecificLevelOnly = 1 THEN dbo.udf_AccountName(l1.AccountGUID, l1.Description, @LanguageID)
                        WHEN @SpecificLevelOnly = 2 THEN dbo.udf_AccountName(l2.AccountGUID, l2.Description, @LanguageID)
                        WHEN @SpecificLevelOnly = 3 THEN dbo.udf_AccountName(l3.AccountGUID, l3.Description, @LanguageID)
                        WHEN @SpecificLevelOnly = 4 THEN dbo.udf_AccountName(l4.AccountGUID, l4.Description, @LanguageID)
                        WHEN @SpecificLevelOnly = 5 AND ls.Level5ID <> 0 THEN dbo.udf_AccountName(ls.AccountGUID, ls.Description, @LanguageID)
                    END
                ELSE
                    CASE
                        WHEN l2.Description IS NULL THEN dbo.udf_AccountName(l1.AccountGUID, l1.Description, @LanguageID)
                        ELSE dbo.udf_AccountName(l1.AccountGUID, l1.Description, @LanguageID) + ft.FormatToken
                    END +
                    CASE
                        WHEN NULLIF(l2.Description, 'BLANK') IS NULL THEN ''
                        WHEN l3.Description IS NULL THEN dbo.udf_AccountName(l2.AccountGUID, l2.Description, @LanguageID)
                        ELSE dbo.udf_AccountName(l2.AccountGUID, l2.Description, @LanguageID) + ft.FormatToken
                    END +
                    CASE
                        WHEN l3.Description IS NULL THEN ''
                        WHEN ls.Level4ID = 0 THEN dbo.udf_AccountName(l3.AccountGUID, l3.Description, @LanguageID)
                        ELSE dbo.udf_AccountName(l3.AccountGUID, l3.Description, @LanguageID) + ft.FormatToken
                    END +
                    CASE
                        WHEN l4.Description IS NULL THEN ''
                        WHEN ls.Level5ID = 0 THEN dbo.udf_AccountName(l4.AccountGUID, l4.Description, @LanguageID)
                        ELSE dbo.udf_AccountName(l4.AccountGUID, l4.Description, @LanguageID) + ft.FormatToken
                    END +
                    CASE
                        WHEN ls.Level5ID <> 0 THEN dbo.udf_AccountName(ls.AccountGUID, ls.Description, @LanguageID)
                        ELSE ''
                    END
            END AS RawOutput,
            ft.FormatToken
        FROM tbl_ScheduleBS ls WITH(NOLOCK)
        CROSS JOIN FormatTokenCTE ft
        LEFT JOIN dbo.tbl_ScheduleBS l1 WITH(NOLOCK)
            ON ls.ComputationID = l1.ComputationID
            AND ls.Level1ID = l1.Level1ID
            AND l1.Level2ID = 0
        LEFT JOIN dbo.tbl_ScheduleBS l2 WITH(NOLOCK)
            ON ls.ComputationID = l2.ComputationID
            AND ls.Level1ID = l2.Level1ID
            AND ls.Level2ID = l2.Level2ID AND l2.Level2ID <> 0
            AND l2.Level3ID = 0
        LEFT JOIN dbo.tbl_ScheduleBS l3 WITH(NOLOCK)
            ON ls.ComputationID = l3.ComputationID
            AND ls.Level1ID = l3.Level1ID
            AND ls.Level2ID = l3.Level2ID AND l3.Level2ID <> 0
            AND ls.Level3ID = l3.Level3ID AND l3.Level3ID <> 0
            AND l3.Level4ID = 0
        LEFT JOIN dbo.tbl_ScheduleBS l4 WITH(NOLOCK)
            ON ls.ComputationID = l4.ComputationID
            AND ls.Level1ID = l4.Level1ID
            AND ls.Level2ID = l4.Level2ID AND l4.Level2ID <> 0
            AND ls.Level3ID = l4.Level3ID AND l4.Level3ID <> 0
            AND ls.Level4ID = l4.Level4ID AND l4.Level4ID <> 0
            AND l4.Level5ID = 0
        WHERE ls.ComputationID = @ComputationID
            AND ls.AccountGUID = @AccountGUID
            AND @LSCode = 'BS'
    ),
    OtherLSCode AS (
        SELECT 
            NULL AS RawOutput,
            ft.FormatToken
        FROM FormatTokenCTE ft
        WHERE @LSCode <> 'BS'
    )
    SELECT 
        CASE
            WHEN RawOutput LIKE ft.FormatToken + '%' THEN STUFF(RawOutput, 1, LEN(ft.FormatToken), '')
            ELSE RawOutput
        END AS OutputDesc
    FROM (
        SELECT RawOutput, FormatToken FROM BSData
        UNION ALL
        SELECT RawOutput, FormatToken FROM OtherLSCode
    ) AS Combined
);

关键转换细节 #

• CTE替代变量赋值：用FormatTokenCTE获取分隔符，避免DECLARE变量，符合内联函数纯集合操作的要求。
• 分支逻辑转为UNION ALL：原函数中50个IF分支（示例仅展示BS），每个分支对应一个子查询，通过UNION ALL合并所有分支结果。
• 整合输出修正逻辑：将原函数末尾的@Output前缀修正逻辑直接嵌入SELECT语句，用LEN(ft.FormatToken)替代硬编码的3，适配不同长度的分隔符。
• 保留原逻辑完整性：所有原有的CASE判断、JOIN条件和函数调用完全保留，确保输出结果与原标量函数一致。

调用方式 #

原查询中调用标量函数的位置，改为用CROSS APPLY或LEFT JOIN调用内联函数：

-- 示例调用
SELECT 
    main.*,
    descFn.OutputDesc
FROM YourMainTable main
CROSS APPLY dbo.udf_GetLSLineDescByGUIDLocalised_Inline(
    main.ComputationID,
    main.AccountGUID,
    main.LSCode,
    main.SpecificLevelOnly,
    main.LanguageID
) descFn;

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