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

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

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 лучшим кандидатом для решения этой проблемы? Спасибо!


sql-server common-table-expression case row-number sql-order-by
person Kevin Pullin    schedule 12.07.2010    source источник
comment
у вас трехуровневая сортировка (3 DESC и 2 запятые). почему бы просто не использовать один случай с несколькими когда?   -  person Jeff Meatball Yang    schedule 13.07.2010

Ответы (4)


arrow_upward
2
arrow_downward

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

person Will A    schedule 13.07.2010

arrow_upward
3
arrow_downward

Не должно быть причин дважды запрашивать таблицу сообщений. Вы можете пойти по динамическому маршруту и ​​решить эти проблемы с производительностью или создать 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
person JeffO    schedule 13.07.2010
comment
Я даже не посмотрел в конец его кода, чтобы увидеть, что он возвращается к сообщениям, вы абсолютно правы, что это не нужно, и я соответствующим образом очистил свой ответ. - person Cade Roux; 13.07.2010

arrow_upward
3
arrow_downward

Лучше сделать это либо с помощью трех жестко заданных запросов (в соответствующих операторах 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)
person Cade Roux    schedule 13.07.2010
comment
Я обновил свой ответ после просмотра вашего исходного запроса - случай без else приводит к NULL, которые все сортируются вместе, но поэтому он все еще сортирует все. - person Cade Roux; 13.07.2010

arrow_upward
0
arrow_downward

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

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
person Jeff Meatball Yang    schedule 13.07.2010
comment
Этот пример на самом деле имеет проблему, которую, как я думал, имел исходный запрос: разные столбцы (вероятно) являются разными типами данных, пользователь, дата и адрес электронной почты будут преобразованы в один и тот же тип. Зная это, ваш ORDER BY CONVERT(varchar(?), Date) может даже работать некорректно в зависимости от преобразования в varchar (если только это уже не varchar в форме ГГГГ-ММ-ДД, в этом случае он, вероятно, отсортирован нормально ). - person Cade Roux; 13.07.2010