. Например, «Выбрать заказы данного клиента, начиная с 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.


Сводная таблица результатов.

Купить номер с этой статьей в PDF
6360