使用表值参数过滤存储过程数据：求性能优且防参数嗅探方案

问题背景 #

我有一个接收多个表类型参数的存储过程，意图通过这些参数实现数据过滤，但遇到参数为空行时连接逻辑失效的问题。已定义的表类型如下：

CREATE TYPE [dbo].[IntListTableType] AS TABLE ([Id] INT NULL);

现有两种实现版本，同时考虑是否可以采用动态SQL方案，寻求兼顾性能与避免参数嗅探的最优解。

现有版本分析 #

版本1：左连接+WHERE条件 #

CREATE PROCEDURE [dbo].[FilterUsers]    
    @UserRoles IntListTableType READONLY
       ,@UserTypes IntListTableType READONLY
AS 
    BEGIN
          SELECT UserId, Name 
          FROM Users u
          LEFT JOIN @UserRoles ur on u.RoleId = ur.Id
          LEFT JOIN @UserTypes ut on u.UserTypeId = ut.Id
          WHERE 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserRoles) OR ur.Id IS NOT NULL)
             AND 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserTypes) OR ut.Id IS NOT NULL)
       END

问题：逻辑上能实现需求，但左连接会生成冗余的中间结果集（尤其是Users表数据量较大时），后续再通过WHERE条件过滤会额外消耗性能。此外，固定的执行计划无法适配参数为空/非空的不同场景，存在参数嗅探导致的性能波动。

版本2：无连接，使用EXISTS子查询 #

CREATE PROCEDURE [dbo].[FilterUsers]    
    @UserRoles IntListTableType READONLY
       ,@UserTypes IntListTableType READONLY
AS 
    BEGIN
          SELECT UserId, Name 
          FROM Users u
          WHERE 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserRoles) OR EXISTS(SELECT TOP 1 1 FROM @UserRoles ur where u.RoleId = ur.Id))
             AND 
             (NOT EXISTS(SELECT TOP 1 1 FROM @UserTypes) OR EXISTS(SELECT TOP 1 1 FROM @UserTypes ut where u.UserTypeId = ut.Id))
       END

问题：逻辑正确，但未优化的表类型（无索引）会导致EXISTS子查询重复扫描参数表；同时固定执行计划依然存在参数嗅探问题，当参数为空/非空切换时，执行计划无法匹配当前场景，性能不稳定。

动态SQL方案可行性分析 #

动态SQL是可行的，且是解决这类场景的最优方案之一。它可以根据参数是否为空动态拼接查询条件，生成最贴合当前参数的执行计划，从根源上避免参数嗅探，同时消除冗余的连接/过滤逻辑。

优化后的动态SQL实现 #

首先优化表类型，添加主键索引提升参数表的查询效率：

CREATE TYPE [dbo].[IntListTableType] AS TABLE ([Id] INT NOT NULL PRIMARY KEY CLUSTERED);

然后编写存储过程：

CREATE PROCEDURE [dbo].[FilterUsers]    
    @UserRoles IntListTableType READONLY
   ,@UserTypes IntListTableType READONLY
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT UserId, Name 
        FROM Users u
        WHERE 1=1';

    -- 拼接UserRoles过滤条件（参数非空时生效）
    IF EXISTS(SELECT 1 FROM @UserRoles)
    BEGIN
        SET @SQL += N'
            AND EXISTS(SELECT 1 FROM @UserRoles ur WHERE u.RoleId = ur.Id)';
    END

    -- 拼接UserTypes过滤条件（参数非空时生效）
    IF EXISTS(SELECT 1 FROM @UserTypes)
    BEGIN
        SET @SQL += N'
            AND EXISTS(SELECT 1 FROM @UserTypes ut WHERE u.UserTypeId = ut.Id)';
    END

    -- 执行动态SQL，传入表类型参数
    EXEC sp_executesql @SQL, 
        N'@UserRoles IntListTableType READONLY, @UserTypes IntListTableType READONLY',
        @UserRoles = @UserRoles,
        @UserTypes = @UserTypes;
END

方案优势 #

• 性能最优：参数为空时，直接执行SELECT UserId, Name FROM Users u，无冗余逻辑；参数非空时，仅添加必要的EXISTS过滤，利用表类型的主键索引快速匹配。
• 避免参数嗅探：不同参数组合生成不同的SQL语句，执行计划会适配当前参数场景，不会出现固定计划导致的性能退化。
• 安全可靠：表类型参数为只读传入，不存在SQL注入风险。

备选方案：带RECOMPILE的静态SQL #

如果不想使用动态SQL，可在版本2的基础上添加OPTION(RECOMPILE)，强制每次执行重新生成执行计划，避免参数嗅探：

CREATE PROCEDURE [dbo].[FilterUsers]    
    @UserRoles IntListTableType READONLY
   ,@UserTypes IntListTableType READONLY
AS 
BEGIN
    SELECT UserId, Name 
    FROM Users u
    WHERE 
       (NOT EXISTS(SELECT 1 FROM @UserRoles) OR EXISTS(SELECT 1 FROM @UserRoles ur where u.RoleId = ur.Id))
       AND 
       (NOT EXISTS(SELECT 1 FROM @UserTypes) OR EXISTS(SELECT 1 FROM @UserTypes ut where u.UserTypeId = ut.Id))
    OPTION(RECOMPILE);
END

适用场景：存储过程调用频率较低，可接受每次编译执行计划的开销；代码结构更简洁。

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