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:
Execution plan/live stats with case ordering:
Best Answer
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, asId
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 anIF
statement to decide which one to execute.