SQL Server 2017 高CPU占用自定义函数[dbo].[fnEncClaimType]优化咨询

1. 替换标量用户自定义函数为内联表值函数 #

当前函数是标量UDF，这类函数在被主查询批量调用时会逐行执行，哪怕单次执行速度快，数据量大时CPU占用也会飙升。改成内联表值函数后，SQL Server优化器会将函数逻辑直接展开到主查询的执行计划中做整体优化，性能通常能提升数倍到数十倍。
改造示例：

CREATE function [dbo].[fnEncClaimType](@in_ApptOrEncId numeric(8),@in_bool_IsApptId numeric(2)) 
returns table 
as
return (
    SELECT TOP 1 CASE 
        WHEN BLH_PATINV_TEXT = 'AUTO' THEN 'Auto Accident' 
        WHEN BLH_PATINV_TEXT = 'EMPLOYER' THEN 'Employer' 
        WHEN BLH_PATINV_TEXT = 'PATCLM' THEN 'Self Pay' 
        WHEN BLH_PATINV_TEXT = 'PATINV' THEN 'Penalty Invoice' 
        WHEN BLH_PATINV_TEXT = 'PI' THEN 'Penalty Invoice' 
        WHEN BLH_PATINV_TEXT = 'INJURY' THEN 'Personal Accident' 
        WHEN BLH_PATINV_TEXT = 'PROF' THEN 'Professional' 
        WHEN BLH_PATINV_TEXT = 'TPA' THEN 'TPA' 
        WHEN BLH_PATINV_TEXT = 'UB04' THEN 'Institutional' 
        WHEN BLH_PATINV_TEXT = 'WORKCOMP' THEN 'Work Comp' 
        WHEN BLH_PATINV_TEXT = 'DMERC' THEN 'DMERC' ELSE '' 
        END as ClaimType
    FROM TRN_BILLING_HEAD
    LEFT JOIN TRN_ENCOUNTERS ON BLH_ENC_ID = ENC_ID AND @in_bool_IsApptId = 1
    WHERE 
        BLH_BOOL_INACTIVE = 0
        AND (
            (@in_bool_IsApptId = 1 AND (BLH_APPT_ID = @in_ApptOrEncId OR ENC_APPT_ID = @in_ApptOrEncId))
            OR 
            (@in_bool_IsApptId <> 1 AND BLH_ENC_ID = @in_ApptOrEncId)
        )
    ORDER BY BLH_ID
)

调用方式改为使用APPLY运算符：

SELECT t.*, c.ClaimType
FROM 你的主表 t
OUTER APPLY dbo.fnEncClaimType(t.关联ID, t.是否是ApptID) c

2. 重构OR条件避免索引失效 #

第一个逻辑分支中的BLH_APPT_ID = @in_ApptOrEncId OR ENC_APPT_ID = @in_ApptOrEncId很容易导致索引效率下降，哪怕当前执行计划显示走索引，也可以改写成UNION ALL拆分两个条件，让两个分支各自命中对应索引：

SELECT TOP 1 ClaimType FROM (
    SELECT TOP 1 CASE /* 省略重复的CASE逻辑 */ END as ClaimType, BLH_ID
    FROM TRN_BILLING_HEAD INNER JOIN TRN_ENCOUNTERS ON BLH_ENC_ID = ENC_ID
    WHERE BLH_BOOL_INACTIVE = 0 AND BLH_APPT_ID = @in_ApptOrEncId
    UNION ALL
    SELECT TOP 1 CASE /* 省略重复的CASE逻辑 */ END as ClaimType, BLH_ID
    FROM TRN_BILLING_HEAD INNER JOIN TRN_ENCOUNTERS ON BLH_ENC_ID = ENC_ID
    WHERE BLH_BOOL_INACTIVE = 0 AND ENC_APPT_ID = @in_ApptOrEncId
) t ORDER BY BLH_ID

3. 抽离重复映射逻辑减少冗余 #

两次查询中重复的CASE判断可以抽离为独立的映射表，不需要每次执行都做多次条件判断：

• 新建小表ClaimTypeMapping，存储BLH_PATINV_TEXT和对应展示名的映射关系
• 查询时直接JOIN该映射表获取结果，后续需要修改映射时不用改函数代码，直接更新映射表即可

4. 优化参数与索引匹配 #

• 检查参数类型和表字段类型是否完全匹配：@in_bool_IsApptId是布尔标识，可改为bit类型减少存储和判断开销；确认@in_ApptOrEncId的类型和BLH_APPT_ID、ENC_APPT_ID、BLH_ENC_ID字段类型完全一致，避免隐式转换导致索引失效
• 补充覆盖索引避免回表：
  • TRN_BILLING_HEAD上建立索引：(BLH_BOOL_INACTIVE, BLH_ENC_ID, BLH_APPT_ID, BLH_ID) INCLUDE (BLH_PATINV_TEXT)
  • TRN_ENCOUNTERS上建立索引：(ENC_ID, ENC_APPT_ID)

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