Производительность SQL с использованием ROW_NUMBER и динамического порядка

Я понимаю, что это распространенная проблема и ранее обсуждалась на SO , но я подумал, что снова подниму этот вопрос в надежде, что какая-то жизнеспособная альтернатива может быть найдено.

Возьмите следующий SQL, который объединяет пейджинг с динамическим порядком:

WITH CTE AS (
  SELECT 
      OrderID,
      ROW_NUMBER() OVER (ORDER BY
        CASE WHEN @SortCol='OrderID' THEN OrderID END ASC,
        CASE WHEN @SortCol='CustomerName' THEN Surname END ASC 
      ) AS ROW_ID
  FROM Orders WHERE X
)

SELECT Orders.* FROM CTE
INNER JOIN Orders ON CTE.OrderID = Orders.OrderID
WHERE ROW_ID BETWEEN @RowStart AND @RowStart + @RowCount -1;

Как известно, метод ROW_NUMBER() плохо работает на больших таблицах, поскольку индексы таблицы не могут быть правильно использованы при использовании нескольких операторов CASE в предложении ORDER BY (см. ссылка).

Решение, которое мы использовали в течение ряда лет, - это построить строку, которая затем выполняется с помощью sp_executesql. Производительность хороша при использовании динамического SQL, подобного этому, но полученный код ужасен с точки зрения удобочитаемости.

Я слышал о методе ROWCOUNT, но насколько я знаю, он все еще подвержен тем же проблемам, когда вы вводите динамический порядок по элементу.

Итак, рискуя спросить невозможное, какие существуют другие варианты?

ИЗМЕНИТЬ

Чтобы сделать некоторый полезный прогресс, я собрал три запроса, в которых были освещены различные предлагаемые методы:

Текущее, динамическое решение SQL (время выполнения 147 мс)
gbn Решение (время выполнения 1687 мс)
Решение Anders (время выполнения 1604 мс)
Решение Мухмуда (время выполнения 46 мс)

Ответы

Ответ 1

Как насчет этого:

WITH data as (
    SELECT OrderID,
            ROW_NUMBER() OVER ( ORDER BY OrderID asc) as OrderID_ROW_ID,
            ROW_NUMBER() OVER ( ORDER BY Surname asc) as Surname_ROW_ID
        FROM Orders --WHERE X   
), CTE AS (               
        SELECT OrderID, OrderID_ROW_ID as ROW_ID
        FROM data
        where @SortCol = 'OrderID'

        union all

        SELECT OrderID, Surname_ROW_ID
        FROM data
        where @SortCol = 'Surname'
)
SELECT Orders.*, ROW_ID FROM CTE
INNER JOIN Orders ON CTE.OrderID = Orders.OrderID       
WHERE ROW_ID BETWEEN @RowStart AND @RowStart + @RowCount -1
order by ROW_ID
option (recompile);

Изменить: Использование option (recompile) в примере запроса в сообщении делает его намного быстрее. Однако case не может использоваться таким образом, чтобы выбирать между восходящим/убывающим порядком.

Причина этого заключается в том, что создается план для значений переменных, которые являются неуместными, а затем этот план кэшируется. Принуждение перекомпиляции позволяет использовать фактические значения переменных.

Ответ 2

(Edited)

DECLARE 
      @OrderColumnName SYSNAME
    , @RowStart INT
    , @RowCount INT
    , @TopCount INT

SELECT 
      @OrderColumnName = 'EmployeeID'
    , @RowStart = 5
    , @RowCount = 50
    , @TopCount = @RowStart + @RowCount – 1

Решение @muhmud -

; WITH data AS 
(
    SELECT 
          wo.WorkOutID
        , RowIDByEmployee = ROW_NUMBER() OVER (ORDER BY wo.EmployeeID)
        , RowIDByDateOut = ROW_NUMBER() OVER (ORDER BY wo.DateOut)  
    FROM dbo.WorkOut wo
), CTE AS 
(         
    SELECT
          wo.WorkOutID
        , RowID = RowIDByEmployee 
    FROM data wo
    WHERE @OrderColumnName = 'EmployeeID'

    UNION ALL

    SELECT
          wo.WorkOutID
        , RowID = RowIDByDateOut
    FROM data wo
    WHERE @OrderColumnName = 'DateOut'
)
SELECT wo.*  
FROM CTE t
JOIN dbo.WorkOut wo ON t.WorkOutID = wo.WorkOutID
WHERE t.RowID BETWEEN @RowStart AND @RowCount + @RowStart - 1
ORDER BY t.RowID
OPTION (RECOMPILE)

Table 'WorkOut'. Scan count 3, logical reads 14254, physical reads 1,
read-ahead reads 14017, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 1295 ms,  elapsed time = 3048 ms.

Решение без общего выражения таблицы данных -

;WITH CTE AS 
(         
    SELECT
          wo.WorkOutID
        , RowID = ROW_NUMBER() OVER (ORDER BY wo.EmployeeID) 
    FROM dbo.WorkOut wo
    WHERE @OrderColumnName = 'EmployeeID'

    UNION ALL

    SELECT
          wo.WorkOutID
        , RowID = ROW_NUMBER() OVER (ORDER BY wo.DateOut) 
    FROM dbo.WorkOut wo
    WHERE @OrderColumnName = 'DateOut'
)
SELECT wo.*  
FROM CTE t
JOIN dbo.WorkOut wo ON t.WorkOutID = wo.WorkOutID
WHERE t.RowID BETWEEN @RowStart AND @RowCount + @RowStart - 1
ORDER BY t.RowID
OPTION (RECOMPILE)

Table 'WorkOut'. Scan count 3, logical reads 14254, physical reads 1, read-ahead reads 14017, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1296 ms,  elapsed time = 3049 ms.

Решение с TOP -

;WITH CTE AS 
(         
    SELECT TOP (@TopCount)
          wo.WorkOutID
        , RowID = ROW_NUMBER() OVER (ORDER BY wo.EmployeeID) 
    FROM dbo.WorkOut wo
    WHERE @OrderColumnName = 'EmployeeID'

    UNION ALL

    SELECT TOP (@TopCount)
          wo.WorkOutID
        , RowID = ROW_NUMBER() OVER (ORDER BY wo.DateOut) 
    FROM dbo.WorkOut wo
    WHERE @OrderColumnName = 'DateOut'
)
SELECT wo.*  
FROM CTE t
JOIN dbo.WorkOut wo ON t.WorkOutID = wo.WorkOutID
WHERE t.RowID > @RowStart - 1
ORDER BY t.RowID
OPTION (RECOMPILE)

Table 'WorkOut'. Scan count 3, logical reads 14246, physical reads 1, read-ahead reads 14017, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1248 ms,  elapsed time = 2864 ms.

Ответ 3

Попробуйте это. Это должно использовать индексы, которые у вас есть

WITH CTE AS (               
        SELECT 
            Orders.*,
            ROW_NUMBER() OVER (ORDER BY OrderID) AS rnOrderID,
            ROW_NUMBER() OVER (ORDER BY Surname) AS rnSurname                                                                         
        FROM Orders WHERE X                                         
    )
SELECT CTE.*
FROM CTE
WHERE
   CASE @SortCol
       WHEN 'OrderID' THEN rnOrderID
   END BETWEEN @RowStart AND @RowStart + @RowCount -1;

Однако для больших наборов данных (100 000 и более) существуют другие методы, такие как http://www.4guysfromrolla.com/webtech/042606-1.shtml

Ответ 4

Я бы попробовал нечто похожее на это:

WITH CTEOrder AS (
  SELECT 
     OrderID,
     ROW_NUMBER() OVER (ORDER BY OrderID ASC) AS ROW_ID
  FROM Orders 
)
, CTECustomerName AS (
  SELECT 
     OrderID,
     ROW_NUMBER() OVER (ORDER BY Surname ASC) AS ROW_ID
  FROM Orders 
)
, CTECombined AS
(
  SELECT 'OrderID' OrderByType, OrderID, Row_ID
    FROM CTEOrder
   WHERE Row_id BETWEEN @RowStart AND @RowStart + @RowCount -1
  UNION
  SELECT 'CustomerName' OrderByType, OrderID, Row_ID
    FROM CTECustomerName
   WHERE row_id BETWEEN @RowStart AND @RowStart + @RowCount -1
)
SELECT Orders.* FROM CTECombined
INNER JOIN Orders ON CTECombined.OrderID = Orders.OrderID       
WHERE ROW_ID BETWEEN @RowStart AND @RowStart + @RowCount -1;  
AND OrderByType = @SortCol

Я попробовал это с одной из моих собственных таблиц, в которой есть приложение. 4 миллиона записей. Очевидно, что у него разные имена полей, поэтому я извиняюсь, если я не "правильно" перевел это в ответ, и SQL не работает для вас. Однако идея должна быть очевидной.

С кодом в вашем вопросе я получаю приложение 200000 логических чтений и процессор 6068 мс на моей таблице, а с приведенным выше я получаю 1422 логических чтения и 78 мс CPU.

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

Если у вас есть запросы со многими полями, которые вы хотите заказать, это решение может не масштабироваться достаточно, так как вам придется расширять число CTE, но вы можете сделать это в коде, если вы все равно строите SQL - и для примера с сортировкой по двум различным полям он работает как прелесть для меня.

EDIT: подумайте об этом, вам, вероятно, не понадобятся отдельные CTE для каждого столбца OrderBy, вы, вероятно, сможете просто иметь один, где вы делаете как ROW_NUMBER(), так и UNION в том же CTE, Принцип тот же, и я бы подумал, что оптимизатор в конечном итоге сделает то же самое, но я пока не тестировал это. Я обновлю ответ, если и когда я получу время, чтобы проверить это.

EDIT 2: Как и ожидалось, вы можете сделать UNION в пределах одного CTE. Я не буду обновлять код, но я дам некоторые контрольные значения для кода, как есть. Я сделал серию из 10000 строк, чтобы узнать, не изменилось ли это, но это не так. (Два прогона cusinar9 и мой код соответственно выполнялись одинаково, поэтому у холодного запуска были одинаковые параметры для обеих версий кода, а во втором прогоне были разные параметры, но то же самое для двух версий кода):

код cusimar9, холодный старт:

Table 'TestTable'. Scan count 10009, logical reads 43080, physical reads 189, read-ahead reads 12915, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:   CPU time = 3037 ms,  elapsed time = 2206 ms.

код cusimar9, второй запуск, различные параметры:

Table 'TestTable'. Scan count 10009, logical reads 43096, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:   CPU time = 4132 ms,  elapsed time = 1012 ms.

Мое предложение, холодный старт:

Table 'TestTable'. Scan count 10001, logical reads 31963, physical reads 12, read-ahead reads 6984, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:   CPU time = 218 ms,  elapsed time = 1410 ms.

Мое предложение, 2-й прогон:

Table 'TestTable'. Scan count 10001, logical reads 31963, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:   CPU time = 218 ms,  elapsed time = 358 ms.

Изменить 3: увидев ваш опубликованный код, я отметил эту конструкцию:

PagedCTE AS (
    SELECT (SELECT Max(ROW_ID) FROM OrderByCTE) AS TOTAL_ROWS, OrderID 
    FROM OrderByCTE             
    WHERE
        OrderByCTE.SortCol = @SortCol AND OrderByCTE.SortDir = @SortDir AND
        OrderByCTE.ROW_ID BETWEEN @RowStart AND @RowStart + @RowCount -1
)

Я не на 100% уверен в цели этого, но я предполагаю, что вы хотите вернуть общее количество строк, чтобы вы могли вычислять (и показывать пользователю), насколько далеко мы находимся в наборе записей. Так почему бы просто не получить этот номер за пределами всего шума ORDER BY? Чтобы придерживаться текущего стиля, почему бы не сделать CTE с SELECT COUNT(*)? А затем присоединитесь к этому в своем окончательном выборе?

Ответ 5

Это один случай, когда процедурные шаблоны терпят неудачу для хранимых процедур. Ваша попытка параметризовать атрибуты, используемые оптимизатором, не позволяет оптимизатору выполнять эту работу.

В этом случае я бы использовал 2 хранимых процедуры.

Если вы действительно настроены на использование параметров, то:

DECLARE @strSQL varchar(1000) = 
    'SELECT OrderID,ROW_NUMBER() OVER ( ORDER BY '                                                                                        
    + @SortCol +
    ' ASC) AS ROW_ID FROM Orders WHERE X ' +
    ' AND ROW_ID BETWEEN @RowStart and @RowStart + @RowCount - 1;'                                        

EXECUTE (@StrSQL)

Ответ 6

Без CTE Генерация номера строки при наличии условий динамического порядка

select TotalCount = COUNT(U.UnitID) OVER() ,

ROW_NUMBER() over(
    order by 
     (CASE @OrderBy WHEN '1' THEN m.Title  END) ASC ,
     (CASE @OrderBy WHEN '2' THEN m.Title  END) DESC,
     (CASE @OrderBy WHEN '3' THEN Stock.Stock  END) DESC,
     (CASE @OrderBy WHEN '4' THEN Stock.Stock   END) DESC

) as RowNumber,

M.Title,U.ColorCode,U.ColorName,U.UnitID, ISNULL(Stock.Stock,0) as Stock

 from tblBuyOnlineMaster M

    inner join BuyOnlineProductUnitIn U on U.BuyOnlineID=M.BuyOnlineID 

    left join 
            ( select IT.BuyOnlineID,IT.UnitID,ISNULL(sum(IT.UnitIn),0)-ISNULL(sum(IT.UnitOut),0) as Stock 
                from [dbo].[BuyOnlineItemTransaction] IT 
                group by IT.BuyOnlineID,IT.UnitID
             ) as Stock

        on U.UnitID=Stock.UnitID


order by 
 (CASE @OrderBy WHEN '1' THEN m.Title  END) ASC ,
 (CASE @OrderBy WHEN '2' THEN m.Title  END) DESC,
 (CASE @OrderBy WHEN '3' THEN Stock.Stock  END) DESC,
 (CASE @OrderBy WHEN '4' THEN Stock.Stock   END) DESC


offset  @offsetCount rows fetch next 6 rows only