Постраничный вывод данных – это тот функционал, который встречается чуть-ли не на каждом информационном сайте, и реализация подобной выборки зачастую не так сложна. Но всё же в SQL Server, до версии Denali, не было нормальной поддержки таких сценариев. Стандартным подходом реализации постраничной выборки в SQL Server был запрос с функцией ROW_NUMBER. Суть этого подхода в том, что запрос пробегает по всем строкам набора и нумерует их от единицы до номера конечного элемента. Потом по этим номерам и делается выборка по диапазону, например мы получаем вторые 10 записей набора (2-я страница). Но у этого подхода есть один существенный минус – каждый раз, даже если вам нужно выбрать 10 первых записей из таблицы в миллион записей, запрос будет пробегать миллион записей. Чем больше данных, тем ниже перфоманс. Те кто уже боролся с подобными проблемами меня поймут. Бороться можно и нужно, но это сложно. В SQL Server Denali появляется поддержка таких выборок на уровне обработчика запросов, но всё далеко не так хорошо, как может показаться на первый взгляд. Впрочем, обо всём по-порядку.
Первое что я покажу – это красота запросов :). Вот так может выглядеть выборка второй страницы списка объявлений на сайте:
SELECT * FROM dbo.Ads
ORDER BY Id
OFFSET 20 ROWS
FETCH NEXT 20 ROWS ONLY
Для сравнения, старая конструкция (с ROW_NUMBER) выглядит более громоздкой и менее понятной:
SELECT TOP (20) *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY a.Id) AS 'RowNum'
FROM dbo.Ads AS a
) AS s
WHERE s.RowNum > 20
ORDER BY s.Id
Кстати и L2S и EF сейчас генерирует второй вариант при использовании конструкции .Skip(..).Take(..). Имейте ввиду.
Второе, это конечно же производительность. Заранее сорри, что дал дезинформацию вначале поста. Если запрос с ROW_NUMBER написан правильно, то производительность его ТАКАЯ ЖЕ как и запроса OFFSET..FETCH. И ROW_NUMBER, и OFFSET..FETCH требуется полный пробег по набору, сортировка его по полю указанному в ORDER BY и потом выборка необходимых данных. Но, если набор по которому бежит запрос заранее отсортирован (по полям которые указаны в ORDER BY построен индекс) то, как только запрос понимает что он получил все нужные данные он прекращает своё выполнение и возвращает результат. Это страведливо и для ROW_NUMBER и для OFFSET..FETCH. Тем не менее, для того чтобы добиться такого поведения с использованием ROW_NUMBER, нужно использовать его вот в таком варианте (например, если здесь, вместо ORDER BY RowNum написать ORDER BY Id, то получим дополнительную сортировку):
SET STATISTICS IO ON;
DECLARE @RowsPerPage INT = 20, @PageNumber INT = 1;
-- Очищение буферного пула (для чистоты эксперимента)
DBCC DROPCLEANBUFFERS;
SELECT * FROM temp
ORDER BY Id DESC
OFFSET @RowsPerPage * @PageNumber - @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
DBCC DROPCLEANBUFFERS;
SELECT TOP (@RowsPerPage) * FROM
(
SELECT *, ROW_NUMBER() OVER (ORDER BY Id DESC) AS 'RowNum'
FROM dbo.temp AS a
) s
WHERE RowNum > @RowsPerPage * @PageNumber - @RowsPerPage AND RowNum <= @RowsPerPage * @PageNumber
ORDER BY s.RowNum DESC;
DBCC DROPCLEANBUFFERS;
WITH CTE_ADS AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY Id DESC) AS 'RowNum'
FROM dbo.temp AS a
)
SELECT *
FROM CTE_ADS
WHERE RowNum > @RowsPerPage * @PageNumber - @RowsPerPage AND RowNum <= @RowsPerPage * @PageNumber
ORDER BY RowNum DESC
GO
Здесь выборка идёт из небольшой таблицы в 3 мил. записей, в которой есть только один индекс (кластерный) по полю Id. Статистика ввода/вывода следующая (показана не полная статистика, а только статистика логических чтений):
1 пример: Table 'temp'. Scan count 1, logical reads
2 пример: Table 'temp'. Scan count 1, logical reads 5
3 пример:Table 'temp'. Scan count 1, logical reads 5
Данные хранятся в SQL Server в виде страниц по 8 Кб (отношения к постраничной выборке это не имеет). Когда происходит выборка данных, SQL Server поднимает в память необходимые страницы с данными (если они ещё не в памяти) и потом читает их. Одно логическое чтение – это чтение страницы SQL Server в памяти.
План запроса можно посмотреть в аттаче - SimpleQueryPlan.sqlplan
И теперь пример более сложного запроса:
-- Включение показа статистики ввода/вывода
SET STATISTICS IO ON;
DECLARE @RowsPerPage int = 20, @PageNumber int = 1, @CategoryId int = 26;
-- Очищение буферного пула (для чистоты эксперимента)
DBCC DROPCLEANBUFFERS;
-- Выборка с конструкцией OFFSET..FETCH
SELECT * FROM dbo.smclads AS a
WHERE a.category = @CategoryId
ORDER BY a.Id
OFFSET @RowsPerPage * @PageNumber - @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
DBCC DROPCLEANBUFFERS;
-- Выборка с подзапросом
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNum'
FROM dbo.smclads AS a
WHERE a.category = @CategoryId
) s
WHERE RowNum > @RowsPerPage * @PageNumber - @RowsPerPage AND RowNum <= @RowsPerPage * @PageNumber
ORDER BY s.RowNum;
DBCC DROPCLEANBUFFERS;
-- Выборка при помощи Common Table Expression (доступно, начиная с SQL Server 2005)
WITH CteAds AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNum'
FROM dbo.smclads AS a
WHERE a.category = @CategoryId
)
SELECT * FROM CteAds
WHERE RowNum > @RowsPerPage * @PageNumber - @RowsPerPage AND RowNum <= @RowsPerPage * @PageNumber
ORDER BY RowNum
GO
Смотрим на статистику:
1 пример: Table 'smclads'. Scan count 7, logical reads 8
2 пример: Table 'smclads'.Scan count 7, logical reads 8
3 пример: Table 'smclads'. Scan count 7, logical reads 8
Таким образом имеем следующую картину - SQL Server Denali предлагает более красивую и интуитивно понятную конструкцию OFFSET..FETCH для постраничной выборки. Скорость работы сравнима со скоростью работы конструкции ROW_NUMBER. Но, есть вариант написать выборку с ROW_NUMBER таким образом, что она не будет так эффективна как могла бы быть, и здесь OFFSET..FETCH позволяет избежать ненужных ошибок.
PS: Если кто-то уже игрался с этим функционалом, пишите в комментарии, я с удовольствием обсужу :)
Ссылки по теме:
Материалы поста:
SimpleQueryExecutionPlan.sqlplan (108,67 kb)
ComplexQueryExecutionPlan.sqlplan (665,16 kb)