导致聚集索引扫描的日期参数 [英] Date Parameter causing Clustered Index Scan

问题描述

我有以下查询

DECLARE @StartDate DATE = '2017-09-22'
DECLARE @EndDate DATE = '2017-09-23'

SELECT a.col1,
       a.col2,
       b.col1,
       b.col2,
       b.col3,
       a.col3
FROM   TableA a
       JOIN TableB b
           ON b.pred = a.pred
WHERE  b.col2 > @StartDate AND b.col2 < @EndDate

当我运行它并检查实际执行计划时，我可以看到成本最高的操作符是聚集索引扫描(索引在 a.pred 上)

When I run this and inspect the actual execution plan, I can see that the most costly operator is a clustered index scan (The index is on a.pred)

但是，如果我按如下方式更改查询

However, if I change the query as follows

SELECT a.col1,
       a.col2,
       b.col1,
       b.col2,
       b.col3,
       a.col3
FROM   TableA a
       JOIN TableB b
           ON b.pred = a.pred
WHERE  b.col2 > '2017-09-22' AND b.col2 < '2017-09-23'

取消了索引扫描并使用了索引查找.

The index scan is eliminated and an index seek is used.

谁能解释一下这是为什么?在我看来，这与变量中的值可以是任何值这一事实有关，因此 SQL 不知道如何计划执行.

Can someone explain why this is? In my mind, this is something to do with the fact that the value in the variable could be anything so SQL doesn't know how plan the execution.

有什么办法可以消除表扫描但仍然使用变量?(PS，这将被转换为一个以@StartDate和@EndDate为参数的存储过程)

Is there any way I can eliminate the table scan but still have use of the variable? (PS, this will be converted to a stored procedure which takes @StartDate and @EndDate as parameters)

编辑

col2 是 DATETIME，但是，如果我将变量设置为 DATETIME，问题仍然存在

col2 is DATETIME, however, if I make my variable DATETIME the problem still persists

推荐答案

SQL 使计划可重用于变量.

SQL makes plans reusable for variables.

当您使用变量时 - 它会在不知道您将传递的实际值的情况下编译查询.即使在这个 sql batch 中，值也是已知的.但是它不需要为另一组传递参数重新编译查询.

When you use variables - it compiles query without knowing actual values you'll pass. Even in this sql batch values are known. But it won't need to recompile query for another set of passing arguments.

因此，如果您对值进行硬编码 - DB 会编译它选择针对这些特定值优化的计划(例如，它猜测通过日期检查的预期行数).它至少不会比使用变量更糟糕".但是 DB 需要为另一组硬编码值重新编译它(因为查询的文本已更改)，这需要时间和垃圾 compiled query cache 存储取代其他有用的查询.

So if you hardcode values - DB compiles it chosing the plan optimized for these particular values (e.g., it guesses expected number of rows passed date check). It'd be 'at least not worse' than when you use variables. But DB needs to recompile it for another set of hardcoded values (because text of the query is changed), which takes time and litters compiled query cache storage superseding the other useful queries.

截至:

有什么办法可以消除表扫描但仍然使用变量?(PS，这将被转换为一个以@StartDate和@EndDate为参数的存储过程)

Is there any way I can eliminate the table scan but still have use of the variable? (PS, this will be converted to a stored procedure which takes @StartDate and @EndDate as parameters)

我认为 b.col2 上的非聚集索引可能是解决方案.此索引的键也可能包含 b.pred 作为代理键的一部分或包含 (with include(pred)).

I think non-clustered index on b.col2 maybe be the solution. The key of this index may also contain b.pred as a part of surrogate key or as including (with include(pred)).

这篇关于导致聚集索引扫描的日期参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！