データベースレベルのページングをSQL Serverで実現する
データベースのページング処理について、海外のサイトでは見かけるが日本語のサイトではあまりお目にかかれないので書いておこうと思う。
システムを構築していく上でデータの一覧を表示する必要がある場合、データベースから多量のデータを持ってくるのは効率が悪くてパフォーマンスの低下につながる。ページング機能を実装して絞り込んだ件数を表示するようにコーディングする必要がある。その際に、データベースから全件を取得してからプログラム上で絞り込むのは意味がない。ディスクアクセスが一番のボトルネックになり得るからだ。また、メモリーも圧迫してしまう。件数が多ければ表示するまでに時間がかかるので、ユーザーの評価はがた落ちである。データベースの段階で表示する件数分だけを取得するようにしたい。
データベースからページングを実装する場合は、テーブルの何件目からどれぐらい取得するかを指定する必要がある。あらかじめアプリケーション上で表示するページのページ数と1ページの表示件数から表示する区間を計算しておき、取得開始行数と取得終了行数をクエリーに渡してあげればよい。また、一覧というからにはフィルタリングやソート処理にも動的に対応したい。その際、クエリー上でSQL文を構築して、その構築したSQLを実行するという手順を踏んでページングと動的なフィルタリング、ソート設定に対応する。取得レコードの特定はROW_NUMBER関数を用いる。ストアドプロシージャにしておくと楽かもしれない。
ID | Name ---|---------- 1 | Data-1 2 | Data-2 3 | Data-3 4 | Data-4 5 | Data-5 6 | Data-6 … 膨大な件数があるとする。
膨大な一覧の中からNameに5という文字が含まれているデータで、1ページ10件表示で2ページ目から表示したい場合のコーディング例。
declare @query nvarchar(max)
declare @startRowIndex int
declare @endRowIndex int
declare @sortExpression nvarchar(64)
declare @filterExpression nvarchar(64)
-- プログラムから値を渡す
set @startRowIndex = 11
set @endRowIndex = 20
set @sortExpression = 'ID asc'
set @filterExpression = 'Name like ''%5%''' --Nameに5という文字が含まれているものを抽出する
-- クエリーを動的に生成
set @query = 'select t.ID, t.Name
from (select ID, Name, ROW_NUMBER() over(order by ' + @sortExpression + ' ) rownum
from Table_1
' + case when LEN(@filterExpression) <> 0 then 'where ' + @filterExpression else '' end + ' ) t
where rownum between ' + CONVERT(nvarchar(16), @startRowIndex) + ' and ' + CONVERT(nvarchar(16), @endRowIndex)
-- 生成したクエリーを実行
exec sp_executesql @querySQL Server 2000などの古いデータベースの場合は、ROW_NUMBER関数がないのでtop句を活用してページングを行う。降順に並び替えた一覧のtop句で全件数から取得する開始ページ数分の件数を引いた値で先頭を決定し、それを昇順にして1ページ分の件数を取得すれば必要な箇所のみを抽出することができる。
全レコード : ----------------------------------------------- Step1. サブクエリー: <----------------------------------------| Step2. 親クエリー : |---------> 取得結果 : -----------
最初のサブクエリーが降順のtop句なので後ろから引いてきて末尾が実際の先頭レコードになる。次に親クエリーで昇順にするのでサブクエリーで引っ張ってきたレコードの末尾が先頭レコードになり、1ページ分のレコードを取得する形となる。この方法で実際にアプリケーションが取得する件数が1ページ分となる。なお、フィルター条件を指定すると件数がずれるので、フィルター条件を適用した件数を全件として取得してから計算する。
select top 10 t.ID, t.Name from (
select
-- top ((select COUNT(*) from Table_1 where Name like '%5%') - 10)
top 100000 -- <フィルター適用後の件数> * SQL Server 2000では上の行のコードはエラーとなる。
b.ID, b.Name
from Table_1 b
where Name like '%5%' order by ID desc) t
order by ID ascADO.NET Entity FrameworkのLINQ to EntitiesやLINQ to SQLなどのLINQ構文が使えるバージョンの.NET Frameworkで実装している場合、次のコードでページングのクエリーをはいてくれる。便利な世の中になったものだ。
using System.Linq;
namespace PagingTest
{
class Program
{
static void Main(string[] args)
{
var entities = new TestEntities(); // データベース名はTestにしたのでウィザードで作ってそのままの名前。
var list = from t in entities.Table_1
orderby t.ID
where t.Name.Contains("5")
select t;
var result = list.Skip(10).Take(10).ToList();
}
}
}Skipメソッドで読み飛ばす件数を、Takeメソッドで取得する件数を指定する。今回の場合、2ページ目からなので最初のページの10件を読み飛ばすためにSkip(10)として、1ページ文の10件を取得するのでTake(10)とした。
データの一覧表示はシステムにおいてほぼついて回るものなので、データベースレベルのページングに対応してなければ、この対応を行うだけでかなりのパフォーマンスアップを図ることができる。
Follow me!
