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

* * *

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

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

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

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

Запросы

Сценарий всех запросов заключается в выборке пакета из 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. Выборка "холодным" запросом, миллисекунды

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

Выводы

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

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

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

Исходные тексты: sqlserverpaging2.zip