by Alexey Knyazev
25. августа 2011 19:17
Постраничная выборка, пейджинг - это достаточно популярная тема/задача, которую приходится решать любому программисту. Очень многие ругали SQL Server, за то, что в отличии от других СУБД, в сиквеле эта задача решается не так прозрачно. Для решения этой задачи приходилось использовать подзапросы с несколькими операторами TOP, либо ранжирующие функции, которые накладывают большие сопутствующие затраты, что сказывается на производительности.
В новой версии SQL Server эта задача решается с помощью предложений OFFSET и FETCH.
Применение в качестве решения для разбиения на страницы предложений OFFSET и FETCH потребует однократного выполнения запроса для каждой «страницы» данных, возвращаемых клиентскому приложению. Например, чтобы вернуть результаты запроса блоками по 10 строк, необходимо выполнить запрос для получения строк с 1 по 10, затем еще раз для получения строк с 11 по 20 и так далее. Каждый запрос выполняется независимо и никаким образом не связан с другими запросами.
Для тестов создадим таблицу с 10 млн записей и сравним 3 запроса:
- 1) решение через оператор TOP (вариант для SQL Server 2000 и ниже)
- 2) решение через ранжирующую функцию ROW_NUMBER (вариант для SQL Server 2005 и выше)
- 3) решение через предложения OFFSET и FETCH (вариант для SQL Server 2011)
if object_id( 'dbo.Table01', 'U' ) is not null
drop table dbo.Table01
go
--Таблица для тестов
create table dbo.Table01 ( val uniqueidentifier )
go
--Наполняем тестовыми данными - 10 млн строк
with cte ( id, val )
as
( select 1, newid()
union all
select id + 1, newid()
from cte
where id < 10000000
)
insert into dbo.Table01
select val from cte
option ( maxrecursion 0 )
go
--Создадим индекс
create index idx on dbo.Table01 ( val )
go
Теперь 3 запроса на выборку записей с 100001 по 100500 упорядоченные по текстовому полю val
--1) выборка с помощью оператора TOP
select * from
(
select top 500 * from
(
select top 100500 * from dbo.Table01
order by val
) t
order by val desc
) t
order by val
--2) выборка с помощью ранжирующей функции ROW_NUMBER
select val from
(
select row_number() over ( order by val ) id, * from dbo.Table01
) t
where id between 100001 and 100500
--3) вариант с использованием OFFSET и FETCH
select * from dbo.Table01
order by val
offset 100000 rows
fetch next 500 rows only
А теперь результат сравнения этих 3х запросов по времени и по IO ( set statistics io on; set statistics time on; ). Для чистоты эксперимента, перед каждым запросом очищаю буферный пул ( dbcc dropcleanbuffers ):
Первый запрос:
Второй запрос:
Третий запрос:
Ссылки по теме:
Подписаться