Постраничная (пакетная, paging) выборка в MS SQL Server 2012

Материал этой статьи послужил основой для одной из глав книги "СУБД для программиста. Базы данных изнутри".

* * *

Хорошая новость: в SQL Server 2012, наконец, появилась возможность ограничивать выборку номером строки и размером пакета на уровне запроса. Точнее, на уровне инструкции ORDER BY. Означает ли это, что все ранее известные способы постраничной выборки станут ненужными? Ответить на этот вопрос нам помогут испытания.

По сравнению с предыдущими тестами SQL Server 2005, мы несколько упростим условия и автоматизируем процесс.

Исходные данные

Структура таблиц "customers" и "sales" для теста остается без особых изменений, используем целочисленные ключи и кластеры. Заполнение таблиц производится SQL-скриптом. Общее число продаж ограничено 10 миллионами при количестве клиентов в 10 тысяч. Используя генератор случайных значений, получаем равномерное распределение количества продаж по странам (скрипты для создания БД и вставки тестовых данных).

country_code sales_count
ES 1424420
FR 1434608
GE 1391128
IT 1443384
NL 1414063
RU 1441266
UK 1451131

Запросы

Сценарий всех запросов заключается в выборке пакета из 100 записей, начиная с заданной, из массива продаж по выбранной стране. Сам запрос оформлен в виде проекции (view) "test_sales_data". Эта проекция используется в запросах производящих собственно пакетирование разными методами и оформленных в виде хранимых процедур (см. скрипт создания).

Сигнатура процедур одинакова (N - порядковый номер метода):

CREATE PROCEDURE dbo.test_paging_mN
  @offset int,
  @page_size int
AS
BEGIN
  ...
  реализация метода пакетирования
  ...
END

Ниже приводим код реализации методов.

Метод 1: встроенный механизм ORDER BY в SQL Server 2012

  SELECT * FROM dbo.test_sales_data
  ORDER BY id_product, id_customer, sale_date
  OFFSET @offset - 1 ROW FETCH NEXT @page_size ROWS ONLY

Метод 2: функция ранжирования

  WITH ordered_sales AS (
    SELECT 
      *,
      row_number() OVER( ORDER BY id_product, id_customer, sale_date) AS row_num
    FROM dbo.test_sales_data
  )
  SELECT *
    FROM ordered_sales
    WHERE row_num BETWEEN @offset AND @offset + @page_size - 1;

Метод 3: временная таблица

  SELECT * INTO #s FROM dbo.test_sales_data WHERE 1 = 0;
  ALTER TABLE #s ADD row_num INT NOT NULL IDENTITY(1, 1) PRIMARY KEY;
 
  INSERT INTO #s
  SELECT TOP (@offset + @page_size - 1) * FROM dbo.test_sales_data
  ORDER BY id_product, id_customer, sale_date;
 
  SELECT * FROM #s
  WHERE row_num BETWEEN @offset and @offset + @page_size - 1;

Метод 4: использование SELECT TOP

  SELECT *
  FROM
  (
    SELECT TOP (@page_size) *
    FROM
      (SELECT TOP (@offset + @page_size - 1) *
         FROM dbo.test_sales_data
         ORDER BY id_product ASC, id_customer ASC, sale_date ASC
      ) t1
    ORDER BY id_product DESC, id_customer DESC, sale_date DESC
  ) t2
  ORDER BY id_product, id_customer, sale_date

Метод 5: серверный курсор

Документация по API курсоров в MSDN.

  DECLARE @handle int, @rows int;
  EXEC sp_cursoropen
    @handle OUT,
    'SELECT * FROM dbo.test_sales_data ORDER BY id_product, id_customer, sale_date',
    1, -- 0x0001 - Keyset-driven cursor
    1, -- Read-only
    @rows OUT; -- Contains total rows count
 
  EXEC sp_cursorfetch
    @handle,
    16,        -- Absolute row index
    @offset,   -- Fetch from row
    @page_size -- Rows count to fetch
 
  EXEC sp_cursorclose @handle;

Результаты

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

set SQL_BIN_HOME=C:\Program Files\Microsoft SQL Server\110\Tools\Binn
"%SQL_BIN_HOME%\sqlcmd.exe" -S .\SQL2012 -d test_paging -E -i SQLServerPaging2_04_Test.sql -o Test.log

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

На моем относительно слабом компьютере с одним медленным диском получились следующие данные и соответствующие им графики.

Таблица 1. Выборка "холодным" запросом, миллисекунды

offset 1 2 3 4 5
1 6690 6693 6763 6906 35033
100 7260 6790 7190 7083 34736
1000 9230 8996 8570 8663 34653
10000 9330 8916 8570 7200 35433
100000 14946 15126 16156 15180 35103
200000 21120 20216 22863 21126 35736
300000 25223 23210 26063 24970 35473
400000 29923 25690 31336 29846 34933
500000 29326 28240 31526 30300 34750
 

Таблица 2. Выборка "горячим" запросом, среднее время 3 попыток, миллисекунды

offset 1 2 3 4 5
1 7 9 23 13 6669
100 16 12 24 16 6617
1000 33 34 48 37 7048
10000 140 210 243 131 6662
100000 1037 2084 1711 1182 6632
200000 3291 4206 4477 3218 6678
300000 4754 6340 5997 4923 6640
400000 1634 8407 2981 2006 6612
500000 1642 10641 3232 2196 6717
 

Выводы

Введенный в версии SQL Server 2012 метод пакетирования записей ORDER BY OFFSET ожидаемо показывает хорошие результаты, так как выполняется не на пользовательском уровне, а на уровне ядра СУБД. Использование этого способа можно рекомендовать всем разработчикам.

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

Отдельный совет веб-программистам. Максимально ограничивайте возвращаемую веб-серверу выборку, чтобы не заниматься, как в тесте, пакетированием полутора миллионов записей. Нормальная ситуация, когда в ответ на запрос пользователю возвращается несколько десятков или сотен записей. Если запрос возвращает больше, выдавайте пользователю только TOP NNN записей и подсказку о необходимости ограничить запрос. И тогда вам в обычной ситуации не понадобится ни сверхмощный сервер, ни распределение нагрузки по СУБД-серверам.

AttachmentSize
Package icon sqlserverpaging2.zip4.31 KB