在 Where 子句中使用 "CASE" 选择各种列会损害性能

Question

我有查询，它需要在某些列上是动态的，这意味着我得到一个参数，并根据它的值决定在我的 Where 子句中获取哪一列。我使用"CASE"表达式实现了此请求：

(CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) 
>= DATEADD(mi, -@TZOffsetInMins, @sTime) 
 AND (CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) 
 < DATEADD(mi, -@TZOffsetInMins, @fTime)

如果@isArrivalTime = 1则选择ArrivalTime列，则选择PickedupTime列。我在ArrivalTime上有一个聚集索引，在PickedupTime上有一个非聚集索引。

我注意到当我使用此查询（带有@isArrivalTime = 1）时，与仅使用ArrivalTime相比，我的性能要差得多。

也许查询优化器不能以这种方式正确使用\选择索引？

我比较了执行计划，注意到当我使用 CASE 时，32% 的时间浪费在索引扫描上，但当我不使用 CASE (just used ArrivalTime'）时，只有 3% 的时间浪费在索引扫描上。

有人知道原因吗？

Answer 1

两者之间的差异很可能是由于它必须扫描的行数不同。由于您的查询很可能是更多表之间联接的最终结果。请记住，扫描是..好吧，顾名思义，扫描所有行。您可以在计划中签入估计的行数以提出想法。

如果对动态字段使用筛选器（例如使用大小写），则引擎无法使用该字段上的索引。在这种情况下，您最好使用动态 sql 来生成您需要并使用exec_sql的正确 sql。或者使用 if 语句直接查询正确的内容。

您可能会发现这对于使用动态 sql 很有用，它将解释原因和方式。

http://www.sommarskog.se/dynamic_sql.html

Answer 2

尝试设置日期时间边界：

declare @resSTime datetime
        ,@resFTime datetime
set @resSTime = DATEADD(mi, -@TZOffsetInMins, @sTime)
set @resFTime = DATEADD(mi, -@TZOffsetInMins, @fTime)

并尝试将大小写更改为"或"

( ArrivalTime >= @resSTime 
    and ArrivalTime < @resFTime 
    and @isArrivalTime = 1 )
or ( PickedupTime >= @resSTime 
    and PickedupTime < @resFTime 
    and @isArrivalTime <> 1 )