SQL Server Denali – Paging

30. July 2011 08:51 by Denis Reznik in FETCH, OFFSET, Paging, SQL Server, SQL Server 2011, SQL Server Denali  //  Tags:   //   Comments

        Постраничный вывод данных – это тот функционал, который встречается чуть-ли не на каждом информационном сайте, и реализация подобной выборки зачастую не так сложна. Но всё же в 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)

blog comments powered by Disqus

Обо мне

MVP

Data Architect at Intapp, Inc.

PASS Regional Mentor, CEE

MCT, MCITP, MCPD, MCTS


Microsoft MVP

Month List