Row_Number() CTE Performance when using ORDER BY CASE

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 Emai开发者_JAVA百科l 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!

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 

Better to do this with either three hardcoded queries (in appropriate IF statements based on @sortCol) or dynamic SQL.

You might be able to do a trick with UNION ALL of three different queries (base on a base CTE which does all your JOINs), where only one returns rows for @sortCol, but I'd have to profile it before recommending it:

WITH BasePosts(PostID, User, Date, Email) AS (
    SELECT PostID, User, Date, Email
    FROM Posts -- This is your complicated query
)
,NumberedPosts (PostID, User, Date, Email, RowNum) AS
(
    SELECT PostID, User, Date, Email, ROW_NUMBER() OVER (ORDER BY User DESC)
    FROM BasePosts
    WHERE @sortCol = 'User'

    UNION ALL

    SELECT PostID, User, Date, Email, ROW_NUMBER() OVER (ORDER BY Date DESC)
    FROM BasePosts
    WHERE @sortCol = 'Date'

    UNION ALL

    SELECT PostID, User, Date, Email, ROW_NUMBER() OVER (ORDER BY Email DESC)
    FROM BasePosts
    WHERE @sortCol = 'Email'
)
INSERT INTO #temp(PostID, User, Date, Email)
SELECT PostID, User, Date, Email
FROM NumberedPosts
WHERE NumberedPosts.RowNum BETWEEN @start and (@start + @pageSize)

I would definitely go down the dynamic SQL route (using sp_executesql with parameters to avoid any injection attacks). Using the CASE approach you're immediately stopping SQL Server from using any relevant indexes that would assist in the sorting process.

This should work, but not sure if it improves performance:

WITH NumberedPosts (PostID, RowNum) AS
(
    SELECT PostID, ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @sortCol = 'User' THEN User 
             WHEN @sortCol = 'Date' THEN Date
             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