Row_Number()使用ORDER BY CASE时的CTE性能 [英] 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 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

问题是，与普通的ORDER BY Date desc子句相比，使用CASE语句时性能会严重下降(至少降低10倍).从查询计划来看，即使所有列都与@sortCol限定符不匹配，仍在对其进行排序.

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.

是否有办法使它以接近本机"的速度执行?动态SQL是否是解决此问题的最佳选择?谢谢！

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

推荐答案

我肯定会沿用动态SQL路由(使用带有参数的sp_executesql以避免任何注入攻击).使用CASE方法，您将立即停止SQL Server使用任何有助于排序过程的相关索引.

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.

这篇关于Row_Number()使用ORDER BY CASE时的CTE性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！