3

I have the following query in a SP which executes fine:

SELECT * FROM MyTable u
ORDER BY 
u.[Id] DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

However, when I make the ORDER BY configured from a SP parameter the query goes from executing in ms to several seconds:

SELECT * FROM MyTable u
ORDER BY 
CASE WHEN @Sort = 0 THEN u.[Id] END DESC,
CASE WHEN @Sort = 1 THEN u.[Id] END ASC,
CASE WHEN @Sort = 2 THEN u.[LastName] END ASC,
CASE WHEN @Sort = 3 THEN u.[LastName] END DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

What is the reason now for the slowdown even though the same field is being used for the order? (ID DESC?)

Is there a way to make this perform better?

I have tried in SQL Server 2016 and SQL Azure.

EDIT: Execution plan/live stats without case ordering:

Without case ordering execution plan ok

Execution plan/live stats with case ordering:

Here is the execution plan using the case statment execution plan

  • You could try dynamic SQL instead – Lamak Jan 25 '18 at 13:46
  • 1
    Did you look at the execution plan? It should help explain. – Aaron Bertrand Jan 25 '18 at 13:46
  • @AaronBertrand good point, I meant to add. The scan goes from 20 rows to all rows using case statement. – izip Jan 25 '18 at 14:07
9

Any calculation or function, when applied to an index key column, will prevent SQL Server from using that index.

In your case, ORDER BY Id DESC can make use of an ordered scan of the Clustered Index on your table, as Id is the key column on that index. That means, only 20 rows have to be read.

Once you apply the CASE statement, SQL Server cannot use that index anymore and instead has to scan the entire row set and execute (very expensive) sorting on all rows to find the 20 rows you are interested in.

One of the solutions is to use dynamic SQL to include the appropriate ORDER BY without any calculations (as Lamak suggested in the comments above) or to just write the query 4 times and use an IF statement to decide which one to execute.

  • Thank you for the explanation. The actual query is much larger so I guess we'll use dynamic sql – izip Jan 25 '18 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.