Пользовательская сортировка и нумерация страниц в больших таблицах

Я привык получать преимущества от функции ROW_NUMBER в сценариях MS SQL Server с версии 2005 года. Но я заметил, что существует большой недостаток производительности при запросе больших таблиц с использованием этой функции.

Представьте себе таблицу с четырьмя столбцами (настоящая таблица из внешней базы данных имеет больше столбцов, но я использовал только их, чтобы не усложнять пример):

DECLARE TABLE StockItems (
  Id int PRIMARY KEY IDENTITY(1,1),
  StockNumber nvarchar(max),
  Name nvarchar(max),
  [Description] nvarchar(max))

Я написал процедуру для запроса этой таблицы, заполненной более чем 200 000 строк, со следующими параметрами:

  • @SortExpression — имя столбца, по которому я хочу сортировать
  • @SortDirection - битовая информация (0=по возрастанию, 1=по убыванию)
  • @startRowIndex - нулевой индекс, по которому я хочу получить строки
  • @maximumRows — количество извлекаемых строк

Запрос:

SELECT sortedItems.Id
    ,si.StockNumber
    ,si.Name
    ,si.Description
FROM (SELECT s.Id
         ,CASE WHEN @SortDirection=1 THEN
            CASE
               WHEN CHARINDEX('Name',@SortExpression)=1 THEN
                 ROW_NUMBER() OVER (ORDER by s.Name DESC)
               WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN
                 ROW_NUMBER() OVER (ORDER by s.StockNumber DESC)
            ELSE ROW_NUMBER() OVER (ORDER by s.StockNumber DESC)
            END
          ELSE    
            CASE
               WHEN CHARINDEX('Name',@SortExpression)=1 THEN
                  ROW_NUMBER() OVER (ORDER by s.Name ASC)
               WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN
                  ROW_NUMBER() OVER (ORDER by s.StockNumber ASC)
            ELSE  ROW_NUMBER() OVER (ORDER by s.StockNumber ASC)
            END
          END AS RowNo
       FROM stockItems s
     ) as sortedItems
INNER JOIN StockItems si ON sortedItems.Id=si.Id
ORDER BY sortedItems.RowNo

В ситуации, когда количество строк быстро растет, ROW_NUMBER становится неэффективным, т.к. должен сортировать все строки.

Пожалуйста, не могли бы вы помочь мне избежать этого недостатка производительности и ускорить запрос?


sql performance sql-server-2008 row-number
person Rudolf Dvoracek    schedule 26.03.2013    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Проверьте путь выполнения. ROW_NUMBER() не имеет большого значения, если у вас есть правильный индекс. Проблема с вашим запросом не в ROW_NUMBER(). Вместо этого используйте динамический, это устранит 2 СЕГМЕНТАЦИИ, вызванную ROW_NUMBER(). Я проверил это на таблице записей> 4mil, и она возвращается за доли секунды:

DECLARE @SortExpression VARCHAR(32)  SET @SortExpression = 'StockNumber'
DECLARE @SortDirection BIT           SET @SortDirection  = 1
DECLARE @startRowIndex BIGINT        SET @startRowIndex  = 1000
DECLARE @maximumRows BIGINT          SET @maximumRows    = 5000

DECLARE @vsSQL AS NVARCHAR(MAX)
SET @vsSQL = ''
SET @vsSQL = @vsSQL + 'SELECT sortedItems.Id, sortedItems.StockNumber, sortedItems.Name, sortedItems.Description FROM ( '
SET @vsSQL = @vsSQL + 'SELECT s.Id, s.StockNumber, s.Name, s.Description, '
SET @vsSQL = @vsSQL + 'ROW_NUMBER() OVER (ORDER BY ' + @SortExpression + ' ' + CASE @SortDirection WHEN 1 THEN 'DESC' ELSE 'ASC' END + ') AS RowNo '
SET @vsSQL = @vsSQL + 'FROM StockItems s '
SET @vsSQL = @vsSQL + ') AS sortedItems '
SET @vsSQL = @vsSQL + 'WHERE RowNo BETWEEN ' + CONVERT(VARCHAR,@startRowIndex) + ' AND ' + CONVERT(VARCHAR,@startRowIndex+@maximumRows) + ' '
SET @vsSQL = @vsSQL + 'ORDER BY sortedItems.RowNo'

PRINT @vsSQL
EXEC sp_executesql @vsSQL
person Joseph Lee    schedule 26.03.2013
comment
Спасибо, Джозеф, за ваши усилия, но при использовании функции ROW_NUMBER для очень больших таблиц она стала работать медленнее из-за упорядочения всей таблицы и нумерации строк. - person Rudolf Dvoracek; 26.03.2013
comment
Вы пробовали выше? Попробуй. Я пробовал на таблице записей › 4mil. У вас есть правильный индекс для Name & StockNumber, верно? - person Joseph Lee; 26.03.2013
comment
Да, ваш подход к сокращению количества сегментаций быстрее, чем мой исходный код, но я хочу максимально избегать запросов, определенных в переменной varchar, чтобы исключить возможность внедрения SQL. Спасибо за помощь мне. - person Rudolf Dvoracek; 26.03.2013

arrow_upward
0
arrow_downward

Вы можете переместить выражение case в предложение order by:

order by (case when @SortDirection=1 and CHARINDEX('Name',@SortExpression)=1 then s.name end) desc,
         (case when @SortDirection=1 and CHARINDEX('StockNumber',@SortExpression)=1 then s.StockNumber end) desc,
         (case when @SortDirection=1 and (CHARINDEX('StockNumber',@SortExpression)<>1 and CHARINDEX('Name',@SortExpression)<>1) then va.match end) desc,
         (case when @SortDirection<>1 and CHARINDEX('Name',@SortExpression)=1 then s.name end) asc,
         (case when @SortDirection<>1 and CHARINDEX('StockNumber',@SortExpression)=1 then s.StockNmber end) asc,
         (case when @SortDirection<>1 and (CHARINDEX('StockNumber',@SortExpression)<>1 and CHARINDEX('Name',@SortExpression)<>1) then va.match end) asc

Я заметил, что в выражении есть va.match, поэтому оно на самом деле не соответствует ни одной таблице в вашем запросе. Итак, я просто вставляю выражение order by.

И да, по мере того, как таблица становится больше, это займет больше времени. Я не знаю, будет ли order by эффективнее row_number(), но это возможно.

Если вам нужно упорядочить строки, вы должны так или иначе выполнить сортировку (возможно, вместо этого вы могли бы использовать индекс). Если вас не волнует заказ, вы можете рискнуть:

row_number() over (order by (select NULL))

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

person Gordon Linoff    schedule 26.03.2013
comment
Спасибо за уведомление о моей опечатке va.match. Я починил это. Но ваш ответ не решает проблему с нумерацией страниц, только с порядком. - person Rudolf Dvoracek; 26.03.2013

arrow_upward
0
arrow_downward

Я нашел решение, как избежать снижения производительности, используя функцию ROW_NUMBER() для больших наборов результатов. Цель, которую я не написал в своем вопросе, заключалась в том, чтобы избежать объявления запроса как переменной nvarchar и его выполнения, потому что это может открыть дверь для SQL-инъекций.

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

Я определил новую переменную @innerCount для запроса самого внутреннего набора результатов и упорядочил его, как клиент запроса указывает в переменных @sortExpression и @sortDirection

SET @innerCount = @startRowIndex + @maximumRows

Select OppositeQuery.Id
,s.StockNumber
,s.Name
,s.Description
FROM (SELECT TOP (@maximumRows) InnerItems.Id
       FROM
            (SELECT TOP (@innerCount) sti.Id
               FROM stockItems sti
               ORDER BY
                CASE WHEN @SortDirection=1 THEN
                    CASE
                        WHEN CHARINDEX('Name',@SortExpression)=1 THEN sti.Name
                        WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN sti.StockNumber
                        ELSE sti.StockNumber
                    END
                END DESC
                CASE WHEN ISNULL(@SortDirection,0)=0 THEN
                    CASE
                       WHEN CHARINDEX('Name',@SortExpression)=1 THEN sti.Name
                       WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN sti.StockNumber
                       ELSE sti.StockNumber
                    END
                END ASC
             ) as InnerQuery
          INNER JOIN StockItems si on InnerQuery.Id=si.Id
          ORDER BY
            CASE WHEN @SortDirection=1 then
                CASE
                   WHEN CHARINDEX('Name',@SortExpression)=1 THEN si.Name
                   WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN si.StockNumber
                   ELSE si.StockNumber
               END
            END ASC
            CASE WHEN ISNULL(@SortDirection,0)=0 then
                CASE
                   WHEN CHARINDEX('Name',@SortExpression)=1 THEN si.Name
                   WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN si.StockNumber
                   ELSE si.StockNumber
                END
            END ASC
    ) AS OppositeQuery
INNER JOIN StockItems s on OppositeQuery.Id=s.Id
ORDER BY
CASE WHEN @SortDirection=1 THEN
    CASE
        WHEN CHARINDEX('Name',@SortExpression)=1 THEN s.Name
        WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN s.StockNumber
        ELSE s.StockNumber
     END
END DESC
CASE WHEN ISNULL(@SortDirection,0)=0 THEN
    CASE
        WHEN CHARINDEX('Name',@SortExpression)=1 THEN s.Name
        WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN s.StockNumber
        ELSE s.StockNumber
    END
END ASC

Недостатком этого подхода является то, что мне приходится сортировать данные три раза, но в случае множественных внутренних объединений с подзапросами таблицы StockItems это происходит намного быстрее, чем при использовании функции ROW_NUMBER().

Спасибо всем участникам за помощь.

person Rudolf Dvoracek    schedule 26.03.2013