如何修复被百万次调用且重复生成执行计划的SQL Server函数

一、能否将fn_TestBinaryBitwiseOr改为参数化存储过程？ #

不能。因为你的场景是在表值函数的SELECT语句中直接调用该函数获取列值，而存储过程无法嵌入到SELECT的列表达式中返回单个值——存储过程只能通过EXEC命令执行，无法作为列的计算逻辑嵌入查询，所以这个方案不适用你的场景。

二、可测试的替代解决方案 #

1. 将标量函数改为内联表值函数（ITVF） #

标量值函数（Scalar UDF）在SQL Server中通常逐行执行，且易因参数值差异导致重复编译；而内联表值函数会被查询优化器直接展开到调用它的查询中，相当于将函数逻辑合并到主查询，既能减少编译次数，又能提升执行效率。

修改后的函数代码：

CREATE OR ALTER FUNCTION[dbo].[fn_TestBinaryBitwiseOr_ITVF]
    (@TestFlags1 binary(16),
     @TestFlags2 binary(16))
RETURNS TABLE
AS
RETURN
SELECT 
    CASE
        WHEN @TestFlags1 IS NULL AND @TestFlags2 IS NULL 
            THEN 0x0
        WHEN @TestFlags1 IS NULL 
            THEN @TestFlags2
        WHEN @TestFlags2 IS NULL 
            THEN @TestFlags1
        ELSE
            CONVERT(binary(16), 
                CONVERT(binary(4), (SUBSTRING(@TestFlags2, 1, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1, 1, 4)))) +  
                CONVERT(binary(4), (SUBSTRING(@TestFlags2, 5, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1, 5, 4)))) +  
                CONVERT(binary(4), (SUBSTRING(@TestFlags2, 9, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1,9,4)))) +
                CONVERT(binary(4), (SUBSTRING(@TestFlags2, 13, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1, 13, 4)))) 
            )
    END AS Result
GO

调整DoAThing函数的调用方式（使用CROSS APPLY）：

INSERT INTO @T(ColName) 
SELECT itvf.Result
FROM @Table1 t1 
JOIN @Table2 t2 ON t1.id = t2.table1Id
CROSS APPLY dbo.fn_TestBinaryBitwiseOr_ITVF(t1.binary1, t2.binary2) itvf

2. 创建模板计划指南强制参数化 #

针对该函数的调用模式创建模板计划指南，无需开启全局强制参数化，仅对特定查询模式生效，避免全局设置的副作用。

示例代码（需替换为真实查询文本和参数定义）：

EXEC sp_create_plan_guide 
    @name = N'Guide_fn_TestBinaryBitwiseOr',
    @stmt = N'SELECT [dbo].[fn_TestBinaryBitwiseOr](t1.binary1, t2.binary2) AS result FROM @Table1 t1 JOIN @Table2 t2 ON t1.id = t2.table1Id',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@Table1 table (id int, binary1 binary(16)), @Table2 table (table1Id int, binary2 binary(16))',
    @hints = N'OPTION(PARAMETERIZATION FORCED)'

3. 优化原标量函数的内部逻辑 #

原函数中的SELECT TOP(1)属于冗余代码（CASE语句仅返回单个值），去掉后可简化执行逻辑，帮助优化器更稳定地复用执行计划：

优化后的标量函数：

CREATE OR ALTER FUNCTION[dbo].[fn_TestBinaryBitwiseOr]
    (@TestFlags1 binary(16),
     @TestFlags2 binary(16))
RETURNS binary(16)
AS
BEGIN
    RETURN 
        CASE
            WHEN @TestFlags1 IS NULL AND @TestFlags2 IS NULL 
                THEN 0x0
            WHEN @TestFlags1 IS NULL 
                THEN @TestFlags2
            WHEN @TestFlags2 IS NULL 
                THEN @TestFlags1
            ELSE
                CONVERT(binary(16), 
                    CONVERT(binary(4), (SUBSTRING(@TestFlags2, 1, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1, 1, 4)))) +  
                    CONVERT(binary(4), (SUBSTRING(@TestFlags2, 5, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1, 5, 4)))) +  
                    CONVERT(binary(4), (SUBSTRING(@TestFlags2, 9, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1,9,4)))) +
                    CONVERT(binary(4), (SUBSTRING(@TestFlags2, 13, 4) | CONVERT(bigint, SUBSTRING(@TestFlags1, 13, 4)))) 
                )
        END
END 
GO

4. 持久化计算列（如果适用） #

若binary1和binary2来自物理表而非临时表，可将函数计算逻辑定义为持久化计算列，计算结果会直接存储在表中，无需每次调用函数计算：

示例（基于物理表创建绑定视图并持久化）：

-- 创建绑定视图
CREATE VIEW vw_BinaryOrResult WITH SCHEMABINDING
AS
SELECT 
    t1.id,
    CONVERT(binary(16), 
        CONVERT(binary(4), (SUBSTRING(t2.binary2, 1, 4) | CONVERT(bigint, SUBSTRING(t1.binary1, 1, 4)))) +  
        CONVERT(binary(4), (SUBSTRING(t2.binary2, 5, 4) | CONVERT(bigint, SUBSTRING(t1.binary1, 5, 4)))) +  
        CONVERT(binary(4), (SUBSTRING(t2.binary2, 9, 4) | CONVERT(bigint, SUBSTRING(t1.binary1,9,4)))) +
        CONVERT(binary(4), (SUBSTRING(t2.binary2, 13, 4) | CONVERT(bigint, SUBSTRING(t1.binary1, 13, 4)))) 
    ) AS BinaryOrResult
FROM dbo.Table1 t1
JOIN dbo.Table2 t2 ON t1.id = t2.table1Id

-- 创建持久化索引（需视图包含唯一键）
CREATE UNIQUE CLUSTERED INDEX IX_vw_BinaryOrResult ON vw_BinaryOrResult(id)

后续查询直接从视图获取结果即可，无需调用函数。

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