SQL Server存储过程可空参数与动态WHERE子句性能优化咨询

问题描述 #

现有一个SQL Server存储过程，通过多参数构建动态WHERE子句，仅使用日期范围（@Start_Date、@End_Date）过滤时，1个月数据查询耗时约5秒；但添加@User、@Template等额外参数后，执行时间超过2分钟。需求是仅在参数为有效值（非NULL、非0、非空）时，才加入对应过滤条件，并解决大查询多可选参数的性能问题。

存储过程核心WHERE子句如下：

WHERE (p.AppointmentDate BETWEEN @Start_Date AND @End_Date)
  AND ((p.officeId IN (SELECT OfficeId FROM @OfficeIds)) OR 
       (SELECT COUNT(OfficeId) FROM @OfficeIds) = 0)
  AND (@GroupId = 0 OR g.GroupId = @GroupId)
  AND (@User IS NULL OR Forename + ' ' + Surname = @User)
  AND (@Template IS NULL OR t.TemplateName = @Template)
  AND (@Status IS NULL OR d.Status = @Status)
  AND (@Office IS NULL OR a.Name = @Office)
  AND (@TraceOption = 0 
       OR (@TraceOption = 1 AND p.traceDate < GETDATE()-3)
       OR (@TraceOption = 2 AND p.traceDate >= GETDATE())
       OR (@TraceOption = 3 AND p.traceDate IS NULL))
  AND (@NotesOption = 0 
       OR @NotesOption = 2 
       AND (SELECT COUNT(*) 
            FROM Events 
            WHERE PrevId = p.Id 
              AND (DataItem = 14 OR DataItem = 54)) = 0
       OR (@NotesOption = 1 AND 
           (SELECT COUNT(*) 
            FROM Events 
            WHERE PrevId = p.Id AND (DataItem = 14 OR DataItem = 54)) > 0))
  AND (@Type = 0 
       OR (@Type = 2 AND p.Flags & 2 = 2) 
       OR (@Type = 1 AND p.Flags & 2 <> 2))

一、实现“仅参数有效时加入过滤”的方法 #

1. 动态SQL拼接（推荐） #

通过拼接SQL语句，仅当参数为有效值时才添加对应WHERE条件，同时使用sp_executesql进行参数化，避免SQL注入风险。

示例实现：

ALTER PROCEDURE [dbo].[myTestProcedure]
    @Start_Date smalldatetime,
    @End_Date smalldatetime,
    @User varchar(71),
    @Template varchar(100),
    @Status varchar(400),
    @Office varchar(100),
    @GroupId int,
    @TraceOption int,
    @NotesOption int,
    @Type int,
    @FatherId int,
    @OfficeIds officeIds READONLY
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX), @WhereClauses NVARCHAR(MAX) = '';

    -- 基础日期条件
    SET @WhereClauses = 'WHERE p.AppointmentDate BETWEEN @Start_Date AND @End_Date';

    -- 处理表值参数@OfficeIds
    IF EXISTS(SELECT 1 FROM @OfficeIds)
    BEGIN
        SET @WhereClauses += ' AND p.officeId IN (SELECT OfficeId FROM @OfficeIds)';
    END

    -- 处理@GroupId（非0时生效）
    IF @GroupId <> 0
    BEGIN
        SET @WhereClauses += ' AND g.GroupId = @GroupId';
    END

    -- 处理@User（非NULL非空时生效）
    IF @User IS NOT NULL AND @User <> ''
    BEGIN
        SET @WhereClauses += ' AND Forename + '' '' + Surname = @User';
    END

    -- 处理@Template（非NULL非空时生效）
    IF @Template IS NOT NULL AND @Template <> ''
    BEGIN
        SET @WhereClauses += ' AND t.TemplateName = @Template';
    END

    -- 处理@Status（非NULL非空时生效）
    IF @Status IS NOT NULL AND @Status <> ''
    BEGIN
        SET @WhereClauses += ' AND d.Status = @Status';
    END

    -- 处理@Office（非NULL非空时生效）
    IF @Office IS NOT NULL AND @Office <> ''
    BEGIN
        SET @WhereClauses += ' AND a.Name = @Office';
    END

    -- 处理@TraceOption（非0时生效）
    IF @TraceOption <> 0
    BEGIN
        SET @WhereClauses += CASE @TraceOption
            WHEN 1 THEN ' AND p.traceDate < GETDATE()-3'
            WHEN 2 THEN ' AND p.traceDate >= GETDATE()'
            WHEN 3 THEN ' AND p.traceDate IS NULL'
            ELSE ''
        END;
    END

    -- 处理@NotesOption（非0时生效）
    IF @NotesOption <> 0
    BEGIN
        SET @WhereClauses += CASE @NotesOption
            WHEN 1 THEN ' AND EXISTS(SELECT 1 FROM Events WHERE PrevId = p.Id AND DataItem IN(14,54))'
            WHEN 2 THEN ' AND NOT EXISTS(SELECT 1 FROM Events WHERE PrevId = p.Id AND DataItem IN(14,54))'
            ELSE ''
        END;
    END

    -- 处理@Type（非0时生效）
    IF @Type <> 0
    BEGIN
        SET @WhereClauses += CASE @Type
            WHEN 1 THEN ' AND (p.Flags & 2 <> 2)'
            WHEN 2 THEN ' AND (p.Flags & 2 = 2)'
            ELSE ''
        END;
    END

    -- 拼接完整SQL（替换原SELECT部分）
    SET @SQL = 'SELECT [你的列列表] FROM [你的表关联语句] ' + @WhereClauses;

    -- 执行动态SQL，传入所有参数
    EXEC sp_executesql @SQL,
        N'@Start_Date smalldatetime, @End_Date smalldatetime, @User varchar(71), @Template varchar(100), @Status varchar(400), @Office varchar(100), @GroupId int, @OfficeIds officeIds READONLY',
        @Start_Date = @Start_Date,
        @End_Date = @End_Date,
        @User = @User,
        @Template = @Template,
        @Status = @Status,
        @Office = @Office,
        @GroupId = @GroupId,
        @OfficeIds = @OfficeIds;
END

2. 使用OPTION (RECOMPILE) 提示 #

若不想重构为动态SQL，可在原存储过程的SELECT语句末尾添加OPTION (RECOMPILE)，让SQL Server每次执行时根据实际参数值重新生成最优执行计划，避免参数嗅探导致的低效计划。

示例：

SELECT [你的列列表]
FROM [你的表关联语句]
WHERE [原WHERE条件]
OPTION (RECOMPILE);

注意：该方案适合执行频率不高的存储过程，因为每次重新编译会带来额外开销。

二、大查询多可选参数的性能优化方案 #

1. 避免WHERE子句中的表达式运算 #

原代码中Forename + ' ' + Surname = @User会导致索引失效，建议创建持久化计算列并添加索引：

-- 创建计算列
ALTER TABLE [用户表] ADD FullName AS Forename + ' ' + Surname PERSISTED;
-- 创建索引
CREATE NONCLUSTERED INDEX IX_User_FullName ON [用户表](FullName) INCLUDE([关联需要的列]);

之后将WHERE条件改为FullName = @User。

2. 优化表值参数的使用 #

• 不要用COUNT(OfficeId)判断表值参数是否为空，改用EXISTS(SELECT 1 FROM @OfficeIds)，减少不必要的全表扫描。
• 给表值参数创建索引，提升IN查询的效率：

-- 在存储过程中声明表值参数后添加索引
CREATE CLUSTERED INDEX IX_OfficeIds ON @OfficeIds(OfficeId);

3. 替换COUNT子查询为EXISTS #

原代码中(SELECT COUNT(*) FROM Events WHERE ...)需要统计所有符合条件的行，改用EXISTS只需找到第一条匹配行即可返回，大幅提升性能：

-- 原条件
(@NotesOption = 1 AND (SELECT COUNT(*) FROM Events WHERE PrevId = p.Id AND (DataItem =14 OR DataItem=54))>0)
-- 优化后
(@NotesOption = 1 AND EXISTS(SELECT 1 FROM Events WHERE PrevId = p.Id AND DataItem IN(14,54)))

4. 针对性创建覆盖索引 #

根据常用过滤条件创建覆盖索引，包含查询需要返回的列，避免键查找：

-- 示例：针对p表的日期+常用过滤列创建覆盖索引
CREATE NONCLUSTERED INDEX IX_p_AppointmentDate_Filters 
ON p(AppointmentDate)
INCLUDE(officeId, traceDate, Flags, Id)
-- 可根据实际添加过滤条件（如仅针对常用参数场景）
-- WHERE [过滤条件];

同时确保关联表的过滤列（如g.GroupId、t.TemplateName、d.Status、a.Name）有单独索引或包含在覆盖索引中。

5. 缓解参数嗅探问题 #

除了OPTION(RECOMPILE)，还可以用局部变量接收输入参数，让SQL Server生成更通用的执行计划：

ALTER PROCEDURE [dbo].[myTestProcedure]
    @Start_Date smalldatetime,
    @End_Date smalldatetime,
    @GroupId int,
    -- 其他参数...
AS
BEGIN
    DECLARE @Local_GroupId int = @GroupId;
    -- 其他局部变量...

    SELECT [你的列列表]
    FROM [你的表关联语句]
    WHERE (p.AppointmentDate BETWEEN @Start_Date AND @End_Date)
      AND (@Local_GroupId = 0 OR g.GroupId = @Local_GroupId)
      -- 其他条件使用局部变量...
END

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