超小ネタ。
総件数が欲しい
SQL でデータを問い合わせるアプリを作るとき、データはページングして一定量ずつ返しながらも、総件数は欲しいっていうこと、ありますよね。
総件数を別のクエリで取得してもいいですが、こうやると、1回のクエリで取れるんですって。
SELECT *, COUNT(*) OVER () AS TOAL_COUNT FROM table_name WITH (NOLOCK) WHERE /* 絞り込み条件をここに書く */ ORDER BY sort_key, id OFFSET 100 ROWS FETCH FIRST 20 ROWS ONLY ;
知らなかったー。
sort_key 列が日付などで、厳密に行を一意に特定しない場合、同じ値を持つ行の順番は不定になり得ます。
その場合、主キーなどの必ず一意になる列を併用してソートします。
ただ、このクエリ、遅い。
テスト用に 5 千万件くらいデータを突っ込んだテーブルを用意したんですが、20 件取るのに 10 秒くらいかかりました。
OFFSET 使っていいの?
「SQL ページング」等のキーワードで検索すると、取得開始位置を OFFSET 任せにするのは遅いので、ちゃんとインデックスが効く列で WHERE 句で絞り込みましょうという記事を複数見かけます。
また、そもそも、行番号だけに頼っていると、1ページ目のクエリと2ページ目のクエリの間でデータが追加されたり削除されたりした場合に、ページ境界で抜けや重複が発生することがあるので、行の値に基づくページングの方が望ましいとされます。
でも、単純に WHERE でページングすると、総件数が取れなくなっちゃうんですよね。
大抵の場合、WHERE 句には、何か別の条件がついていると思います。
そもそも取得するデータを絞り込むための WHERE(こっちは総件数に影響すべき)と、ページングのための WHERE(こっちは総件数に影響してほしくない)が混在してしまうのが良くないですね。
というわけで、こんな風にしてみたり。
SELECT TOP (20) * FROM ( SELECT *, COUNT(*) OVER() AS TOAL_COUNT FROM table_name WITH (NOLOCK) WHERE /* 業務的な絞り込み条件をここに書く */ ) AS x WHERE /* ここはページングのための条件 */ /* @sort_key と @id は前のページの最後の行の値 */ sort_key > @sort_key AND id > @id ORDER BY sort_key, id ;
でも、先の例と大してスピードが変わりませんでした。
まぁ、俺、クエリ チューニングは苦手なんで、何かよくわかってない可能性は大いにあるけど…*1
内側の WHERE に時間がかかっているので、OFFSET FETCH を使った場合でも、スキップする件数や1ページ当たりの件数には影響されませんでした。
2回やるとどうなるか
じゃあ、愚直に総件数を別に取るとどうかというと…
SELECT COUNT(*) AS TOAL_COUNT FROM table_name WITH (NOLOCK) WHERE /* 業務的な絞り込み条件をここに書く */ ; SELECT TOP (20) * FROM table_name WITH (NOLOCK) WHERE /* 業務的な絞り込み条件をここにも書く */ /* @sort_key と @id は前のページの最後の行の値 */ sort_key > @sort_key AND id > @id ORDER BY sort_key, id ;
えっとね。
前のやつより爆速でした。*2
この場合、COUNT(*) で取得した総件数が、2回目のクエリの(潜在的)件数と厳密には一致しない可能性もありますが、まぁ、総件数とか参考情報だし、問題にはならないよね…?
というわけで
おしえてえろいひと。