. Например, «Выбрать заказы данного клиента, начиная с 10 000-й строки и по 12 000-ю». Нечто вроде простого и понятного:
SELECT O.*
FROM orders O INNER JOIN customers C
ON O.customer_code = C.customer_code
ORDER BY O.qty_date ASC
LIMIT 10000, 12000
Введенные в 2005-й версии функции ранжирования, и в частности row_number(), несколько скрасили серые будни рядовых разработчиков, но по сути проблему так и не решили. Дело в том, что конструкция LIMIT работает на уровне ядра СУБД, а функция row_number() — на пользовательском. Соответственно скорость выполнения принципиально отличается, что особенно заметно на больших таблицах.
В данном обзоре я опишу различные методы решения задачи постраничной выборки (paging, пакетная выборка) на примере таблиц заказов и клиентов. Для тестов использовался MS SQL Server 2005 Service Pack 2 (9.00.3054.00) на рабочей станции с 2 Гбайт оперативной памяти (512 Мбайт доступно под MS SQL) и двухъядерным процессором Intel 1,8 ГГц.
Задача
Необходимо выбрать заказы всех итальянских клиентов (код страны «IT») пакетами по 100 тыс. записей в каждом. Например, пакет с 400 001-й строки и по 500 000-ю — это четвертый пакет в серии. Заказов, соответствующих заданному критерию, в таблице порядка 800 тыс. Всего же в таблице содержится примерно 4 млн. 300 тыс. записей. Большим такое число не назовешь, но оно уже способно неплохо загрузить наш компьютер для выявления оптимальных способов решения задачи.
Описание теста
Основная цель теста — выявить временные характеристики различных способов решения нашей задачи, отметив их особенности. Каждый способ выполняется в четырех сериях тестов.
Серия 1: с составным ключом из символьных (nvarchar) полей, SQL Server перезапускаем только в начале серии;
Серия 2: с составным ключом из символьных (nvarchar) полей, SQL Server перезапускаем перед каждым тестом серии;
Серия 3: с простым целочисленным ключом, SQL Server перезапускаем только в начале серии;
Серия 4: с простым целочисленным ключом, SQL Server перезапускаем перед каждым тестом серии.
Перезапуск сервера производим для исключения влияния кэширования результатов предыдущих серий на последующие. После перезапуска для «разогрева» — частичной загрузки данных в кэш и приближения к реальным условиям — всякий раз выполняем следующий запрос:
SELECT count(*)
FROM orders O INNER JOIN customers C
ON O.customer_code = C.customer_code
WHERE C.country_code = ‘IT’
Структура таблиц
Таблица заказов имеет простой целочисленный ключ, добавленный специально для тестов, и составной ключ из символьных полей. С небольшими упрощениями структура выглядит следующим образом.
CREATE TABLE dbo.orders (
product_code nvarchar(18) NOT NULL,
customer_code nvarchar(15) NOT NULL,
order_type nvarchar(4) NOT NULL,
qty_date datetime NOT NULL,
qty int NOT NULL,
CONSTRAINT PK_ORDERS PRIMARY KEY NONCLUSTERED,
CONSTRAINT FK1_ORDERS_CUSTOMERS FOREIGN
KEY(customer_code)
REFERENCES dbo.customers (customer_code)
)
GO
CREATE UNIQUE INDEX AK1_ORDERS ON orders(
product_code ASC,
customer_code ASC,
order_type ASC,
qty_date ASC)
GO
CREATE TABLE dbo.customers (
customer_code nvarchar(15) NOT NULL,
country_code nchar(2) NOT NULL,
name nvarchar(255) NOT NULL,
street_address nvarchar(100) NULL,
city nvarchar(40) NULL,
postal_code nvarchar(15) NULL,
CONSTRAINT PK_CUSTOMERS
PRIMARY KEY NONCLUSTERED (customer_code ASC)
)
GO
CREATE INDEX IX1_COUNTRY_CODE ON dbo.customers
(country_code ASC)
GO
Способы решения
Для каждого метода мы определим два входных параметра: начальное смещение (@offset — заданный номер начальной строки выборки) и размер пакета (@batch_size — требуемое количество строк в выборке, начиная с заданной). Пример объявления и инициализации параметров перед выборкой:
DECLARE @offset int, @batch_size int;
SELECT @offset = 1, @batch_size = 100;
«Классический» способ с использованием стандартного SQL
У данного способа, видимо, есть только одно достоинство: запрос выполняется практически на любой СУБД. Принцип основан на соединении таблицы на саму себя (self join), что при количестве в миллионы записей более чем накладно. На таблицах же с несколькими тысячами/десятками тысяч записей способ вполне работоспособен. Так как окончания выполнения запроса на тестовом массиве данных за 20 мин дождаться не удалось, привожу только текст запроса без внесения результатов в сводную таблицу.
SELECT O.*
FROM orders O
INNER JOIN customers C ON O.customer_code
= C.customer_code
WHERE C.country_code = ‘IT’ AND
(SELECT count(*)
FROM orders O1
INNER JOIN customers C1 ON
O1.customer_code = C1.customer_code
WHERE C1.country_code = ‘IT’ AND
O1.product_code <=O.product_code
AND
O1.customer_code <= O.customer_
code AND
O1.order_type <= O.order_type
AND
O1.qty_date <= O.qty_date
) BETWEEN @offset AND @offset + @batch_
size - 1
ORDER BY O.product_code ASC, O.customer_code
ASC, O.order_type ASC, O.qty_date ASC
В первом запросе номера строк не выводятся. Для случая с простым ключом order_id этот недостаток легко исправить:
SELECT num, O.*
FROM orders O
INNER JOIN
(SELECT count(*) AS num, O2.order_id
FROM orders O1
INNER JOIN customers C1
ON O1.customer_code = C1.customer_code
INNER JOIN orders O2 ON O1.order_
id <= O2.order_id
INNER JOIN customers C2
ON O2.customer_code = C2.customercode
AND
C1.country_code = C2.country_code AND
C1.country_code = ‘IT’
GROUP BY O2.order_id
HAVING count(*) BETWEEN @offset AND @
offset + @batch_size - 1
) AS OO ON O.order_id = OO.order_id
ORDER BY OO.num ASC
Использование функции row_number()
Пример использования функции имеется в документации к MS SQL Server (Books online), наш запрос выглядит похоже.
WITH ordered_orders AS (
SELECT O.*,
row_number() OVER(
ORDER BY O.product_code ASC,
O.customer_code ASC,
O.order_type ASC,
O.qty_date ASC
) AS row_num
FROM orders O INNER JOIN customers C ON
O.customer_code = C.customer_code
WHERE C.country_code = ‘IT’
)
SELECT *
FROM ordered_orders
WHERE row_num BETWEEN 400001 AND 500000
Использование временной таблицы
Заносим промежуточный результат (только ключевые поля) во временную таблицу с пронумерованными строками, отсекая по верхней границе, затем выбираем из нее нужный диапазон, соединяя с основной таблицей.
Не забудьте увеличить размер системной базы tempdb. Для данного примера она составила 1,5 Гбайт. В отсутствии верхнего предела для временных данных и заключается основной недостаток метода: чем больше исходная таблица и чем дальше от начального значения находится очередной запрашиваемый нами пакет, тем больше потребуется заливать данных во временную таблицу. Конечно, дисковое пространство нынче большое и дешевое, но все-таки винчестер не резиновый, а скорость с ростом числа загружаемых во временную таблицу строк будет падать.
DECLARE @offset int, @batch_size int;
SELECT @offset = 400001, @batch_size = 100000;
CREATE TABLE #orders(
row_num int identity(1, 1) NOT NULL,
product_code nvarchar(18) NOT NULL,
customer_code nvarchar(15) NOT NULL,
order_type nvarchar(4) NOT NULL,
qty_date datetime NOT NULL
);
INSERT INTO #orders (product_code, customer_
code, order_type, qty_date)
SELECT TOP (@offset + @batch_size)
O.product_code, O.customer_code, O.order_
type, O.qty_date
FROM orders O INNER JOIN customers C ON
O.customer_code = C.customer_code
WHERE C.country_code = ‘IT’
ORDER BY O.product_code ASC, O.customer_code
ASC, O.order_type ASC, O.qty_date ASC;
SELECT O.*
FROM #orders T INNER JOIN orders O
ON T.product_code = O.product_code AND
T.customer_code = O.customer_code AND
T.order_type = O.order_type AND
T.qty_date = O.qty_date
WHERE T.row_num BETWEEN @offset and @offset +
@batch_size - 1;
DROP TABLE #orders;
Использование инструкции TOP
Принцип основан на отсечении нужного числа записей в двух запросах с противоположным порядком следования записей. По сути здесь нет отличий от способа с временной таблицей, кроме того, что она используется неявно. Однако, сравнив результаты, мы видим, что на небольших пакетах (100 записей) SQL Server манипулирует промежуточными выборками менее эффективно, чем в способе с явным использованием временных таблиц.
DECLARE @offset int, @batch_size int;
SELECT @offset = 400001, @batch_size = 100000;
SELECT TOP (@batch_size) *
FROM
(SELECT TOP (@offset + @batch_size) O.*
FROM orders O INNER JOIN customers C ON
O.customer_code = C.customer_code
WHERE C.country_code = ‘IT’
ORDER BY O.product_code DESC, O.customer_
code DESC, O.order_type DESC, O.qty_date DESC
) AS T1
ORDER BY product_code ASC, customer_code ASC,
order_type ASC, qty_date ASC
Использование серверного курсора
Данный способ является не вполне документированным, так как функции работы с серверными курсорами не описаны в SQL Server Books Online, хотя активно используются разработчиками Microsoft. Поэтому имеется весьма небольшой риск несовместимости с будущими версиями. Неофициальные описания этих функций можно найти, например, по адресу http://jtds.sourceforge.net/apiCursors.html.
DECLARE @handle int, @rows int;
EXEC sp_cursoropen
@handle OUT,
‘SELECT O.* FROM orders O INNER JOIN customers
C ON O.customer_code = C.customer_code
WHERE C.country_code = ‘’IT’’
ORDER BY O.product_code ASC, O.customer_code
ASC, O.order_type ASC, O.qty_date ASC’,
1, — Keyset-driven cursor
1, — Read-only
@rows OUT;
SELECT @rows; — Возвращает общее число строк в выборке
EXEC sp_cursorfetch
@handle,
16, — Выборка по абсолютному номеру
(absolute row index)
400001, — Начало диапазона выборки
100000 — Размер (число записей)
EXEC sp_cursorclose @handle;
Использование SET ROWCOUNT
Хотя способ использует стандартную настройку SET ROWCOUNT, но инициализация переменной в запросе, возвращающем более одной строки, его последним значением не документирована.Как подтвердил наш эксперимент, данный метод не работает на составных ключах. Тем не менее в случае простого ключа способ показал неплохие результаты.
DECLARE @order_id int;
SET ROWCOUNT @offset;
SELECT @order_id = O.order_id
FROM orders O INNER JOIN customers C ON
O.customer_code = C.customer_code
WHERE C.country_code = ‘IT’
ORDER BY O.order_id ASC;
SET ROWCOUNT @batch_size;
SELECT O.*
FROM orders O INNER JOIN customers C ON
O.customer_code = C.customer_code
WHERE C.country_code = ‘IT’ AND
O.order_id >= @order_id
ORDER BY O.order_id ASC;
SET ROWCOUNT 0;
Выводы
К сожалению, появившаяся в SQL Server 2005 новая функция ранжирования row_number() показала в целом плохие результаты в тестах по сравнению с другими методами. Наиболее быстрым оказался метод с установкой ROWCOUNT, но он неприменим на составных ключах. У остальных способов есть свои достоинства и недостатки, их и необходимо учесть при выборе. Наиболее универсальным и одновременно показавшим приемлемую скорость выборки, является метод с применением серверного курсора. В этом методе ваш запрос используется в исходном виде, т.е. нет необходимости добавлять к нему условия ограничения диапазона выборки, что важно при работе с возвращающими результат хранимыми функциями и процедурами или проекциями (view). Например, использование функции в способе с TOP зачастую приводит к удвоению времени выборки. И будем надеяться, что в новых версиях разработчики Microsoft все-таки реализуют на уровне ядра СУБД конструкцию LIMIT.