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: