T-SQL函数Base.fn_AssetPriority运行极慢，如何进行性能优化？

核心性能问题定位 #

你的函数性能差主要是几个共性问题导致的：

• 标量用户定义函数（UDF）在视图中调用时会逐行触发执行，数据集规模大的时候重复执行开销被指数级放大
• 函数内部使用WHILE循环 + 表变量逐行匹配规则，运行效率远低于SQL原生的集合操作
• 每次函数调用都重复查询静态配置表Base.ConsequencePriority和全局最新RGID，无意义的重复计算开销极高
• 缺少必要的索引，每次匹配ACA.ConsequenceAssessment都可能触发全表扫描

优化步骤 #

1. 替换标量函数为内联表值函数（iTVF） #

这是优化收益最高的改动，内联表值函数不会逐行执行，会被查询优化器展开到整个视图的执行计划中做批量处理，性能可以提升10~100倍不等。
优化后的函数代码如下：

IF OBJECT_ID('Base.fn_AssetPriority_Inline') IS NOT NULL
    DROP FUNCTION Base.fn_AssetPriority_Inline
GO

CREATE FUNCTION Base.fn_AssetPriority_Inline 
    (@LID BIGINT)
RETURNS TABLE
WITH EXECUTE AS CALLER
AS 
RETURN
(
    -- 一次性获取全局最新RGID，仅执行1次
    WITH GlobalVars AS (
        SELECT MAX(ID) AS RGID FROM ACA.ReviewGroup
    ),
    -- 加载优先级规则
    PriorityRules AS (
        SELECT  
            ConsequenceAssessmentID, [Sign], Score, AssetPriority, ConsNo
        FROM    
            Base.ConsequencePriority
    )
    -- 取第一个匹配的最高优先级规则
    SELECT TOP 1 pr.AssetPriority
    FROM PriorityRules pr
    CROSS JOIN GlobalVars gv
    INNER JOIN ACA.ConsequenceAssessment ca
        ON ca.LID = @LID 
        AND ca.RGID = gv.RGID 
        AND ca.BaseCAID = pr.ConsequenceAssessmentID
        AND (
            (pr.Sign = '=' AND ca.Score = pr.Score)
            OR (pr.Sign != '=' AND ca.Score >= pr.Score)
        )
    ORDER BY pr.ConsNo DESC
    -- 无匹配时返回默认值
    UNION ALL
    SELECT 'Not Allocated' AS AssetPriority
    WHERE NOT EXISTS (
        SELECT 1 
        FROM PriorityRules pr
        CROSS JOIN GlobalVars gv
        INNER JOIN ACA.ConsequenceAssessment ca
            ON ca.LID = @LID 
            AND ca.RGID = gv.RGID 
            AND ca.BaseCAID = pr.ConsequenceAssessmentID
            AND (
                (pr.Sign = '=' AND ca.Score = pr.Score)
                OR (pr.Sign != '=' AND ca.Score >= pr.Score)
            )
    )
)

2. 修改视图调用方式 #

内联表值函数需要用APPLY语法调用，修改视图中的调用代码：

SELECT
    -- 保留原有其他字段
    ap.AssetPriority
FROM Base.BaseAS -- 替换为你原来的表名
OUTER APPLY Base.fn_AssetPriority_Inline(BaseAS.ID) ap

3. 添加必要索引 #

添加以下两个索引即可消除查询时的全表扫描开销：

-- 给ConsequenceAssessment加覆盖索引，匹配查询条件
CREATE NONCLUSTERED INDEX IX_ConsequenceAssessment_LID_RGID_BaseCAID 
ON ACA.ConsequenceAssessment (LID, RGID, BaseCAID) 
INCLUDE (Score);

-- 给规则表加索引，加速排序和字段读取
CREATE NONCLUSTERED INDEX IX_ConsequencePriority_ConsNo 
ON Base.ConsequencePriority (ConsNo DESC) 
INCLUDE (ConsequenceAssessmentID, [Sign], Score, AssetPriority);

额外优化建议 #

如果Base.ConsequencePriority是低频更新的配置表，可以在查询时加WITH (NOLOCK)提示减少锁开销；如果数据量极小，也可以考虑固化为内存优化表进一步提升访问速度。

内容的提问来源于stack exchange，提问作者Kevin van Niekerk