Row_Number() CTE Performance when using ORDER BY CASE

Question

I have a table I'd like to do paging and ordering on and was able to get a query similar to the following to do the work (the real query is much more involved with joins and such).

WITH NumberedPosts (PostID, RowNum) AS
(
    SELECT PostID, ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @sortCol = 'User' THEN User END DESC,
        CASE WHEN @sortCol = 'Date' THEN Date END DESC,
        CASE WHEN @sortCol = 'Email' THEN Email END DESC) as RowNum
   FROM Post
)
INSERT INTO #temp(PostID, User, Date, Email)
SELECT PostID, User, Date, Email
FROM Post
WHERE NumberedPosts.RowNum BETWEEN @start and (@start + @pageSize)
      AND NumberedPosts.PostID = Post.PostID

The trouble is that performance is severely degraded when using the CASE statements (at least a 10x slowdown), when compared to a normal ORDER BY Date desc clause . Looking at the query plan it appears that all columns are still being sorted, even if they do not match the @sortCol qualifier.

Is there a way to get this to execute at near 'native' speed? Is dynamic SQL the best candidate for this problem? Thanks!

Answer 1

There shouldn't be any reason to query the post table twice. You can go the dynamic route and address those issues on performance or create 3 queries determined by the @sortCol parameter. Redundant code except for the row_num and order by parts, but sometimes you give up maintainability if speed is critical.

If @sortCol = 'User' 
Begin
  Select... Order by User
End

If @sortCol = 'Date' 
Begin
  Select .... Order by Date 
end

If @sortCol = 'Email' 
Begin
  Select... Order by Email
End