Row_Number() Производительность CTE при использовании ORDER BY CASE

Question

У меня есть таблица, на которой я хотел бы выполнить разбивку по страницам и упорядочение, и смог выполнить запрос, подобный следующему, для выполнения работы (реальный запрос гораздо больше связан с объединениями и тому подобным).

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

Проблема заключается в том, что производительность сильно снижается при использовании операторов CASE (по крайней мере, 10-кратное замедление) по сравнению с обычным ORDER BY Date desc оговорка Глядя на план запроса, кажется, что все столбцы все еще сортируются, даже если они не соответствуют квалификатору @sortCol.

Есть ли способ заставить это выполняться на "родной" скорости? Является ли динамический SQL лучшим кандидатом на решение этой проблемы? Спасибо!

Answer 1

Я бы определенно пошел по динамическому маршруту SQL (используя sp_executesql с параметрами, чтобы избежать любых атак внедрения). Используя подход CASE, вы немедленно останавливаете SQL Server от использования любых соответствующих индексов, которые могут помочь в процессе сортировки.

Answer 2

Лучше сделать это с помощью трех жестко закодированных запросов (в соответствующих операторах IF на основе @sortCol) или динамического SQL.

Возможно, вам удастся сделать трюк с UNION ALL из трех разных запросов (основанных на базовом CTE, который выполняет все ваши JOIN), где только один возвращает строки для @sortCol, но мне придется профилировать его, прежде чем рекомендовать:

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)

Answer 3

Не должно быть никаких причин запрашивать таблицу сообщений дважды. Вы можете пойти по динамическому маршруту и ​​решить эти проблемы с производительностью или создать 3 запроса, определяемых параметром @sortCol. Избыточный код, за исключением row_num и порядка по частям, но иногда вы отказываетесь от обслуживания, если скорость критична.

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 

Answer 4

Это должно работать, но не уверен, если это улучшает производительность:

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