Using "CASE" in Where clause to choose various column harm the performance

Question

I have query which needs to be dynamic on some of the columns, meaning I get a parameter and according its value I decide which column to fetch in my Where clause. I've implemented this request using "CASE" expression:

(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)

If @isArrivalTime = 1 then chose ArrivalTime column else chose PickedupTime column. I have a clustered index on ArrivalTime and nonclustered index on PickedupTime.

I've noticed that when I'm using this query (with @isArrivalTime = 1), my performance is a lot worse comparing to only using ArrivalTime.

Maybe the query optimizer can't use\choose the index properly in this way?

I compared the execution plans an noticed that when I'm using the CASE 32% of the time is being wasted on the index scan, but when I didn't use the CASE(just usedArrivalTime`) only 3% were wasted on this index scan.

Anyone know the reason for this?

Naraka

Answer 1

try to set datetime boundary:

declare @resSTime datetime
        ,@resFTime datetime

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

and try to change case to 'or'

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

Stearic

Answer 2

The difference between the 2 is most probably due to the different number of rows it has to scan. As your query is probable the end result of a join between more tables. Keep in mind that a scan is .. well what it's name says, a scan of all rows. You can check in the plan for the estimated number of rows to make an idea.

If you use a filter on a dynamic field (using case for example), an index on that field cannot be used by the engine. In a case like this you are better of using dynamic sql to produce the right sql that you need and use exec_sql. Or use if statements to query the right thing directly.

You might find this usefull for using dynamic sql, it will explain why and how.

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

Beautifully