Без лишних запросов

Типичный способ разбиения на страницы состоит в использовании функции ROW_NUMBER. Однако такому решению свойственны неизбежные недостатки. В этой статье я приведу их описание и рекомендации по устранению. В примерах используется таблица Orders в базе данных PerformanceV3. Вы можете загрузить исходный текст для создания тестовой базы данных PerformanceV3 (http://tsql.solidq.com/books/source_code/PerformanceV3.zip).

Предположим, нам нужно вернуть пользовательскую информацию о заказах на основе идентификатора заказов orderid. Пользователь вводит номер страницы (@pagenum) и размер страницы (@pagesize). Номера строк вычисляются на основе orderid, а затем возвращается диапазон номеров строк на основе входных данных: rownum BETWEEN (@pagenum — 1) * @pagesize + 1 AND @pagenum * @pagesize. В листинге 1 приводится полный исходный текст решения с использованием большого числа страниц, чтобы проблема производительности стала более очевидной. На рисунке 1 показан план запроса для этого решения.

Рисунок 1. Неэффективный план

Существует некластеризованный, неохватывающий индекс PK_Orders, определенный для таблицы Orders, с ключом orderid. К счастью, оптимизатор достаточно интеллектуален, чтобы выбрать план, который просматривает в индексе лишь строки, число которых равно верхнему номеру строки в нужном диапазоне. Таким образом, если число строк равно 1000, а размер страницы — 25, то план просматривает первые 25 000 строк в индексе и останавливается. Затем он фильтрует только последние 25 строк. Однако план также выполняет 25 000 уточняющих запросов вместо 25, что приводит к лишним уточняющим запросам. При запросе страницы номер 1000 вы получаете 76 978 операций считывания. Конечно, пользователи обычно не заходят так далеко, но чем больше глубина, тем больше обрабатывается лишних уточняющих запросов.

Простой прием, позволяющий предотвратить лишние уточняющие запросы, заключается в том, что внутренний запрос возвращает только ключ, который появляется в индексе. Затем внешний запрос выполняет объединение обобщенного табличного выражения (CTE) и таблицы, чтобы получить остальную информацию. В листинге 2 приведен полный исходный текст, реализующий этот прием. На рисунке 2 показан план данного решения.

Рисунок 2. Эффективный план

Обратите внимание, что на этот раз совершается всего 25 сеансов поиска в индексе по orderid, за которыми следует 25 уточняющих запросов. Этот план выполняет 226 операций чтения благодаря тому, что фильтр применяется к плану перед выполнением поисков и уточняющих запросов.

Очень похожая проблема возникает при использовании фильтра OFFSET-FETCH для разбиения на страницы. Решение основывается на том же приеме.

USE PerformanceV3; — http://tsql.solidq.com/books/source_code/PerformanceV3.zip
GO
DECLARE @pagenum AS INT = 1000, @pagesize AS INT = 25;
WITH C AS
(
SELECT ROW_NUMBER() OVER(ORDER BY orderid) AS rownum,
orderid, orderdate, custid, empid, filler
FROM dbo.Orders
)
SELECT orderid, orderdate, custid, empid, filler
FROM C
WHERE rownum BETWEEN (@pagenum — 1) * @pagesize + 1 AND @pagenum * @pagesize;

DECLARE @pagenum AS INT = 1000, @pagesize AS INT = 25;
WITH C AS
(
SELECT ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, orderid
FROM dbo.Orders
)
SELECT C.orderid, O.orderdate, O.custid, O.empid, O.filler
FROM C
INNER JOIN dbo.Orders AS O
ON C.orderid = O.orderid
WHERE rownum BETWEEN (@pagenum — 1) * @pagesize + 1 AND @pagenum * @pagesize;