SQL Server自定义函数执行缓慢求助：CASE分支函数被预执行

问题背景 #

自定义标量函数History.getPrimaryID_Asset_Asset用于UPDATE触发器中，更新Asset.Asset表600+行时耗时约55秒。执行测试语句select History.getPrimaryID_Asset_Asset('Asset','Asset', 'ProcurementPlantModelAutoID', '1000')并分析执行计划发现：CASE表达式中所有THEN分支的嵌套函数均被预执行（即使对应分支不命中），每个函数都会触发一次聚集索引查找，产生大量冗余开销。

原函数代码：

ALTER FUNCTION [History].[getPrimaryID_Asset_Asset](@table varchar(32), @schema varchar(32), @column_name varchar(max), @column_value varchar(max))
RETURNS varchar(max)
AS
BEGIN 
    DECLARE @ID varchar(max) = @schema + '.' + @table + '.' + @column_name 
    IF SUBSTRING(@Schema, 1, 1) != '[' SET @Schema = QUOTENAME(@Schema)
    IF SUBSTRING(@Schema, 1, 1) != '[' SET @Table = QUOTENAME(@Table)
    RETURN
    
    CASE 
    WHEN @column_value IS NULL THEN NULL
    WHEN   @ID = '[Asset].[Asset].AssetTypeAutoID' THEN  [Enum].getAssetType(CONVERT(int, @column_value)) 
    WHEN   @ID = '[Asset].[Asset].AssetStatusAutoID' THEN [Enum].getAssetStatus(CONVERT(int, @column_value)) 
    WHEN   @ID = '[Asset].[Asset].ModelNumberAutoID' THEN [ModelNumber].getModelNumberID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].ProjectAutoID' THEN [Project].getProjectName(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].CabinetAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].SystemAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].PowerSupplyAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].PlantModelAutoID' THEN [Location].getPlantModelID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].ColumnAutoID' THEN [Location].getColumnID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].DeviceTypeAutoID' THEN [Typical].getDeviceTypeID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].RackAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].PLCAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].CalBoxAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].GasTypeAutoID' THEN [Enum].getGasType(Convert(int, @column_value))
    WHEN   @ID = '[Asset].[Asset].RedundantAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].VertexAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].PRMAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].NACAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].ConduitAssetAutoID' THEN [Asset].getAssetID(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].GasZoneTypeAutoID' THEN [Enum].getGasZoneType(Convert(int, @Column_Value))
    WHEN   @ID = '[Asset].[Asset].ProcurementPlantModelAutoID' THEN [Location].getPlantModelID(Convert(int, @Column_Value))
    ELSE  @column_value
    END
END

A. 性能缓慢的具体原因 #

• 标量函数CASE表达式的预执行特性：SQL Server对多语句标量函数中的CASE表达式，会预先执行所有THEN分支的函数调用，无论分支是否会被实际命中。每调用一次主函数，所有20+个嵌套函数都会执行一遍，每个函数触发一次聚集索引查找，产生大量冗余IO和计算。
• 函数语法错误导致逻辑无效：原函数中RETURN语句写在CASE表达式之前，导致CASE逻辑完全不会被执行，实际返回NULL（此错误大概率是代码粘贴失误，但如果是线上运行版本，会直接导致业务逻辑错误）。
• QUOTENAME逻辑错误：原代码两次判断@Schema的格式，却错误地修改@Table，导致@ID的构造不符合预期。
• 触发器逐行调用（RBAR）：触发器中逐行调用标量函数，600多行更新会触发600×20+次函数调用和索引查找，累计开销被放大数十倍。
• 嵌套标量函数的叠加开销：每个嵌套标量函数本身都有独立的执行开销，预执行机制进一步放大了这部分开销。

B. 优化方案 #

方案1：修复语法错误，改用IF...ELSE实现短路求值 #

使用IF...ELSE IF逻辑替代CASE表达式，SQL Server会对IF分支进行短路求值，仅执行匹配条件的分支，避免预执行所有嵌套函数。

修复优化后的函数代码：

ALTER FUNCTION [History].[getPrimaryID_Asset_Asset](@table varchar(32), @schema varchar(32), @column_name varchar(max), @column_value varchar(max))
RETURNS varchar(max)
AS
BEGIN 
    DECLARE @ID varchar(max)
    
    -- 修正QUOTENAME逻辑，正确处理schema和table的格式
    IF SUBSTRING(@schema, 1, 1) != '[' 
        SET @schema = QUOTENAME(@schema)
    IF SUBSTRING(@table, 1, 1) != '[' 
        SET @table = QUOTENAME(@table)
    
    -- 构造正确的@ID
    SET @ID = @schema + '.' + @table + '.' + @column_name

    -- 短路求值分支逻辑
    IF @column_value IS NULL
        RETURN NULL

    IF @ID = '[Asset].[Asset].AssetTypeAutoID'
        RETURN [Enum].getAssetType(CONVERT(int, @column_value))
    ELSE IF @ID = '[Asset].[Asset].AssetStatusAutoID'
        RETURN [Enum].getAssetStatus(CONVERT(int, @column_value))
    ELSE IF @ID = '[Asset].[Asset].ModelNumberAutoID'
        RETURN [ModelNumber].getModelNumberID(Convert(int, @column_value))
    ELSE IF @ID = '[Asset].[Asset].ProjectAutoID'
        RETURN [Project].getProjectName(Convert(int, @column_value))
    -- 合并调用相同函数的分支，减少判断次数
    ELSE IF @ID IN (
        '[Asset].[Asset].CabinetAssetAutoID',
        '[Asset].[Asset].SystemAssetAutoID',
        '[Asset].[Asset].PowerSupplyAssetAutoID',
        '[Asset].[Asset].RackAssetAutoID',
        '[Asset].[Asset].PLCAssetAutoID',
        '[Asset].[Asset].CalBoxAssetAutoID',
        '[Asset].[Asset].RedundantAssetAutoID',
        '[Asset].[Asset].VertexAssetAutoID',
        '[Asset].[Asset].PRMAssetAutoID',
        '[Asset].[Asset].NACAssetAutoID',
        '[Asset].[Asset].ConduitAssetAutoID'
    )
        RETURN [Asset].getAssetID(Convert(int, @column_value))
    ELSE IF @ID IN (
        '[Asset].[Asset].PlantModelAutoID',
        '[Asset].[Asset].ProcurementPlantModelAutoID'
    )
        RETURN [Location].getPlantModelID(Convert(int, @column_value))
    ELSE IF @ID = '[Asset].[Asset].ColumnAutoID'
        RETURN [Location].getColumnID(Convert(int, @column_value))
    ELSE IF @ID = '[Asset].[Asset].DeviceTypeAutoID'
        RETURN [Typical].getDeviceTypeID(Convert(int, @column_value))
    ELSE IF @ID = '[Asset].[Asset].GasTypeAutoID'
        RETURN [Enum].getGasType(Convert(int, @column_value))
    ELSE IF @ID = '[Asset].[Asset].GasZoneTypeAutoID'
        RETURN [Enum].getGasZoneType(Convert(int, @column_value))
    ELSE
        RETURN @column_value
END

方案2：改写为内联表值函数（ITVF） #

内联表值函数的执行计划会与外部查询合并，避免标量函数的逐行开销，同时天然支持短路求值，性能优于标量函数。

示例代码：

CREATE FUNCTION [History].[getPrimaryID_Asset_Asset_ITVF](
    @table varchar(32), 
    @schema varchar(32), 
    @column_name varchar(max), 
    @column_value varchar(max)
)
RETURNS TABLE
AS
RETURN
(
    WITH FormattedIDs AS
    (
        SELECT
            CASE WHEN SUBSTRING(@schema, 1, 1) != '[' THEN QUOTENAME(@schema) ELSE @schema END AS schema_name,
            CASE WHEN SUBSTRING(@table, 1, 1) != '[' THEN QUOTENAME(@table) ELSE @table END AS table_name,
            @column_value AS column_value
    )
    SELECT
        CASE
            WHEN column_value IS NULL THEN NULL
            WHEN schema_name + '.' + table_name + '.' + @column_name = '[Asset].[Asset].AssetTypeAutoID' 
                THEN [Enum].getAssetType(CONVERT(int, column_value))
            WHEN schema_name + '.' + table_name + '.' + @column_name = '[Asset].[Asset].AssetStatusAutoID' 
                THEN [Enum].getAssetStatus(CONVERT(int, column_value))
            WHEN schema_name + '.' + table_name + '.' + @column_name = '[Asset].[Asset].ModelNumberAutoID' 
                THEN [ModelNumber].getModelNumberID(Convert(int, column_value))
            WHEN schema_name + '.' + table_name + '.' + @column_name = '[Asset].[Asset].ProjectAutoID' 
                THEN [Project].getProjectName(Convert(int, column_value))
            WHEN schema_name + '.' + table_name + '.' + @column_name IN (
                '[Asset].[Asset].CabinetAssetAutoID',
                '[Asset].[Asset].SystemAssetAutoID',
                '[Asset].[Asset].PowerSupplyAssetAutoID'
            ) THEN [Asset].getAssetID(Convert(int, column_value))
            WHEN schema_name + '.' + table_name + '.' + @column_name IN (
                '[Asset].[Asset].PlantModelAutoID',
                '[Asset].[Asset].ProcurementPlantModelAutoID'
            ) THEN [Location].getPlantModelID(Convert(int, column_value))
            ELSE column_value
        END AS Result
    FROM FormattedIDs
)

使用方式：

SELECT Result 
FROM [History].[getPrimaryID_Asset_Asset_ITVF]('Asset','Asset', 'ProcurementPlantModelAutoID', '1000')

方案3：触发器中改用批量集合操作 #

避免在触发器中逐行调用函数，改为基于集合的批量处理，一次性转换所有需要更新的值，减少函数调用次数。例如：

-- 触发器中示例逻辑
UPDATE a
SET TargetColumn = f.Result
FROM inserted i
JOIN Asset.Asset a ON a.ID = i.ID
JOIN [History].[getPrimaryID_Asset_Asset_ITVF](i.TableName, i.SchemaName, i.ColumnName, i.ColumnValue) f ON 1=1

方案4：优化嵌套标量函数 #

将嵌套的标量函数（如Enum.getAssetType）也改写为内联表值函数，或者直接将其逻辑内联到主函数中，进一步消除函数调用的额外开销。

内容的提问来源于Stack Exchange，提问作者HelpImdumb