データベースレベルのページングをSQL Serverで実現する

データベースのページング処理について、海外のサイトでは見かけるが日本語のサイトではあまりお目にかかれないので書いておこうと思う。

システムを構築していく上でデータの一覧を表示する必要がある場合、データベースから多量のデータを持ってくるのは効率が悪くてパフォーマンスの低下につながる。ページング機能を実装して絞り込んだ件数を表示するようにコーディングする必要がある。その際に、データベースから全件を取得してからプログラム上で絞り込むのは意味がない。ディスクアクセスが一番のボトルネックになり得るからだ。また、メモリーも圧迫してしまう。件数が多ければ表示するまでに時間がかかるので、ユーザーの評価はがた落ちである。データベースの段階で表示する件数分だけを取得するようにしたい。

データベースからページングを実装する場合は、テーブルの何件目からどれぐらい取得するかを指定する必要がある。あらかじめアプリケーション上で表示するページのページ数と1ページの表示件数から表示する区間を計算しておき、取得開始行数と取得終了行数をクエリーに渡してあげればよい。また、一覧というからにはフィルタリングやソート処理にも動的に対応したい。その際、クエリー上でSQL文を構築して、その構築したSQLを実行するという手順を踏んでページングと動的なフィルタリング、ソート設定に対応する。取得レコードの特定はROW_NUMBER関数を用いる。ストアドプロシージャにしておくと楽かもしれない。

膨大な一覧の中からNameに5という文字が含まれているデータで、1ページ10件表示で2ページ目から表示したい場合のコーディング例。

SQL Server 2000などの古いデータベースの場合は、ROW_NUMBER関数がないのでtop句を活用してページングを行う。降順に並び替えた一覧のtop句で全件数から取得する開始ページ数分の件数を引いた値で先頭を決定し、それを昇順にして1ページ分の件数を取得すれば必要な箇所のみを抽出することができる。

最初のサブクエリーが降順のtop句なので後ろから引いてきて末尾が実際の先頭レコードになる。次に親クエリーで昇順にするのでサブクエリーで引っ張ってきたレコードの末尾が先頭レコードになり、1ページ分のレコードを取得する形となる。この方法で実際にアプリケーションが取得する件数が1ページ分となる。なお、フィルター条件を指定すると件数がずれるので、フィルター条件を適用した件数を全件として取得してから計算する。

ADO.NET Entity FrameworkのLINQ to EntitiesやLINQ to SQLなどのLINQ構文が使えるバージョンの.NET Frameworkで実装している場合、次のコードでページングのクエリーをはいてくれる。便利な世の中になったものだ。

Skipメソッドで読み飛ばす件数を、Takeメソッドで取得する件数を指定する。今回の場合、2ページ目からなので最初のページの10件を読み飛ばすためにSkip(10)として、1ページ文の10件を取得するのでTake(10)とした。

データの一覧表示はシステムにおいてほぼついて回るものなので、データベースレベルのページングに対応してなければ、この対応を行うだけでかなりのパフォーマンスアップを図ることができる。