SQL使用declare定义日期变量后查询无限运行原因及解决咨询

问题根因 #

• SQL Server在编译包含局部变量的查询时，局部变量的赋值操作在编译完成后的执行阶段才会运行，因此查询优化器无法获知变量的实际值，只能依据日期列的统计信息平均密度生成执行计划，极易出现预估行数和实际行数偏差过大的问题，导致优化器选择全表扫描等低效的执行逻辑，最终查询耗时激增。
• 你当前使用的OR多日期字段判定逻辑会进一步放大执行计划的缺陷，优化器无法正确判断走REG_DT_TM、PRE_REG_DT_TM独立索引的收益，大概率直接选择全表扫描，数据量较大时就会出现类似无限运行的情况。
• 直接在WHERE子句中写入getdate()相关计算时，这些表达式属于编译时常量，优化器在编译阶段就可以计算出具体的日期值，基于实际值生成最优执行计划，因此查询速度正常。

修复方案 #

方案1：添加RECOMPILE查询提示（改造成本最低） #

强制查询每次执行前重新编译，编译阶段变量已经完成赋值，优化器可以基于实际变量值生成正确的执行计划，只需在你的原有查询末尾添加一行提示即可：

-- 原有查询逻辑不变，末尾添加如下提示
SELECT [你的查询列]
FROM [你的表名] e
WHERE [原有其他过滤条件]
  AND ((e.REG_DT_TM >= @start_date AND e.REG_DT_TM < @end_date)
    OR (e.PRE_REG_DT_TM >= @start_date AND e.PRE_REG_DT_TM < @end_date))
OPTION (RECOMPILE);

方案2：拆分OR条件为UNION ALL（性能最优） #

OR条件是导致索引失效的常见原因，拆分为两个独立查询分别走两个日期字段的索引，再合并结果，性能比原写法提升更明显：

SELECT [你的查询列]
FROM [你的表名] e
WHERE [原有其他过滤条件]
  AND e.REG_DT_TM >= @start_date AND e.REG_DT_TM < @end_date

UNION ALL -- 如果两个条件可能返回重复行，改为UNION去重

SELECT [你的查询列]
FROM [你的表名] e
WHERE [原有其他过滤条件]
  AND e.PRE_REG_DT_TM >= @start_date AND e.PRE_REG_DT_TM < @end_date;

方案3：使用OPTIMIZE FOR提示（适合固定日期范围的查询） #

如果你的业务查询每次的日期范围固定为1天，可以用该提示指定优化器的预估参考值，不需要每次重编译：

-- 原有查询逻辑不变，末尾添加如下提示
OPTION (OPTIMIZE FOR (@start_date = '2021-01-01', @end_date = '2021-01-02'));

补充建议 #

如果REG_DT_TM和PRE_REG_DT_TM字段未创建非聚集索引，建议补充对应索引，可以进一步提升查询性能。

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