Using "CASE" in Where clause to choose various column harm the performance
- by zivgabo
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?
