SQL Server で、ページングしつつ総件数を取る

超小ネタ。

総件数が欲しい

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回目のクエリの(潜在的)件数と厳密には一致しない可能性もありますが、まぁ、総件数とか参考情報だし、問題にはならないよね…?

というわけで

おしえてえろいひと。

*1:インデックスがあるのに、内側の WHERE が Index Scan になってるのはなんでなんだぜ?

*2:こっちは Index Seek になってました。