Using "CASE" in a Where clause to select a different column is bad for performance

Question

I have a query that should be dynamic in some columns, that is, I get the parameter and in accordance with its value. I decide which column to retrieve in my Where clause. I executed this query using a 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 selected ArrivalTime column else, select the PickedupTime column. I have a clustered index on ArrivalTime and a non-clustered index on PickedupTime .

I noticed that when I use this query (with @isArrivalTime = 1 ), my performance is much worse than when using ArrivalTime .

Perhaps the query optimizer cannot use \ to select the index correctly in this way?

I compared the execution plans, noticed that when I use CASE 32% of the time is spent on index scans, but when I did not use CASE (just used ArrivalTime`), only 3% was wasted on index scans.

Does anyone know the reason for this?

Answer 1

try setting the datetime border:

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

and try changing case to 'or'

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

Answer 2

The difference between 2 is most likely due to the different number of lines that it should scan. Since your query is the likely end result of joining between a large number of tables. Keep in mind that scanning ... well, what he says is scanning all rows. You can check the plan for the estimated number of lines to make up the view.

If you use a filter in a dynamic field (for example, for example), the index in this field cannot be used by the engine. In this case, you better use dynamic sql to create the desired SQL code that you need and use exec_sql. Or use if statements to query correctly.

You may find this useful for using dynamic sql, it will explain why and how.

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