以前このブログでも紹介した、 OFFSET FETCH を使って気付いたメモです。
OFFSET FETCH の紹介 - お だ のスペース
大きいデータに対して、OFFSET FETCH を使った時に遅いなーと思って色々試してると、OFFSET と FETCH の値をパラメータ化してるかしてないかで実行プランと実行速度が変わることに気付きました。
検証した環境 Azure SQL Database Basic 互換性レベル 130 です。
テスト用のテーブルに 50万件ほどデータを入れます。
create table [PagingTest] ( [ID] int not null primary key, [SortDate] date not null ) with [cte] as (select 1 as [seq] union all select [seq] + 1 from [cte] where [seq] <= 500000) insert into [PagingTest] select [seq], dateadd(day, [seq] % 100, getdate()) from [cte] option (maxrecursion 0)
これに対して 同じ結果を返す2つのクエリを実行します。
declare @start int = 4 declare @count int = 10 -- set statistics time on -- set statistics io on select * from [PagingTest] order by [SortDate] offset 4 rows fetch next 10 rows only select * from [PagingTest] order by [SortDate] offset @start rows fetch next @count rows only
これの実行プランは以下です。
変数を使ってない方のクエリは、Sort が Top N Sort になっていますが、変数を使っている方のクエリは普通の Sort です。
で、データ量が大きいと Top N Sort の方が有利になるよというお話しです。
というわけで、実際に以下を実行してみます。
select count(*) from [PagingTest] declare @start int = 4 declare @count int = 10 set statistics time on set statistics io on select * from [PagingTest] order by [SortDate] offset 4 rows fetch next 10 rows only select * from [PagingTest] order by [SortDate] offset @start rows fetch next @count rows only
実行結果のメッセージを確認すると、elapsed time が一桁違ってました。
具体的な数値は検証クエリをみなさんで流してもらえば確認出来ると思います。
面白いのは start の値が大きくなるほど、Top N Sort も効きが弱くなってくるんですね。
350000 とかで試すと両方のクエリの実行時間はほとんど変わらないくらいになっていました。
まあなんとなく想像つきますよね。
というわけで、後ろのページになると差は無くなりますが、前の方のページだと結構変わってきそうです。
ただプランキャッシュは増えそうですね。
クエリ全文
create table [PagingTest] ( [ID] int not null primary key, [SortDate] date not null ) with [cte] as (select 1 as [seq] union all select [seq] + 1 from [cte] where [seq] <= 500000) insert into [PagingTest] select [seq], dateadd(day, [seq] % 100, getdate()) from [cte] option (maxrecursion 0) select count(*) from [PagingTest] declare @start int = 4 declare @count int = 10 -- set statistics time on -- set statistics io on select * from [PagingTest] order by [SortDate] offset 4 rows fetch next 10 rows only select * from [PagingTest] order by [SortDate] offset @start rows fetch next @count rows only drop table [PagingTest]
