はじめに

　大量のデータをユーザーに表示する場合は、すべての情報を一気に表示するのではなく、ある程度のまとまりごとに表示することが重要です。例えば、Googleで「Microsoft」という単語を検索すると約2,190,000,000もの結果が返されますが、一度に表示されるのはそのうちの10個です。特に大量の結果セット（1万、10万、または100万のレコードから成るテーブル）にページングアクセスを提供する場合は、要求された特定のページのデータだけが返されるように、データのクエリに注意を払う必要があります。

　ASP.NET 1.xのDataGridとASP.NET 2.0のGridViewは、2種類のページング方法を備えています。

　既定ページングは短時間で簡単に実装できますが、必要な大きさの結果セットだけを取得したい場合はカスタムページングが不可欠です。私は2006年3月に、カスタムページングを利用して大量の結果セットを効率的にページングする方法についての記事を本サイト（4GuysFromRolla.com）にいくつか投稿しましたが、そこでは、ObjectDataSourceとSQL Server 2005の新しいROW_NUMBER()キーワードを使ってGridViewでカスタムページングを行いました。ROW_NUMBER()という新しいキーワードは、行番号で並べられたデータの特定のサブセットを効率的に取得するものです。3月の投稿の後、多くの読者から、ROW_NUMBER()のないSQL Server 2000でこのような効率的なカスタムページングを実装する方法について問い合わせがありました。

　本サイト（4GuysFromRolla.com）に以前に投稿された「Paging through Records using a Stored Procedure」（Daniel Anderson著）は、既定ページングの実装をはるかにしのぐ1つの方法を示しています。しかし、パフォーマンスという点では改善の余地があります。本稿では、Danielのストアドプロシージャに手を加えて、さらに効率的な方法を示します。本稿の最後に示すストアドプロシージャは、典型的なASPアプリケーションや、ASP.NET 1.xのDataGridによるカスタムページングで使用できます。また、ObjectDataSourceで使用してASP.NET 2.0アプリケーションのGridViewでカスタムページングを実現することもできます。詳細については、以下を参照してください。

Danielの方法

　「Paging through Records using a Stored Procedure」の中で、Daniel Andersonは、sp_PagedResultsという名前のストアドプロシージャを作成しました。これは、2つの整数型の入力パラメータ（@Pageおよび@RecsPerPage）を受け取り、基になるデータベーステーブルから、指定ページのデータに属するレコードを返します。例えば、1ページが10レコードの場合に2ページ目のデータを表示するときは、ストアドプロシージャsp_PagedRecordsを呼び出して@Pageと@RecsPerPageにそれぞれ2と10の値を渡します。

　テーブルの一意キー（ID）が連番になっていて、番号に抜けがない場合、このようなテーブルからページ単位でデータを取得するのは簡単です。単純なWHERE句を指定すればよいのです。例えば、次のようなデータのテーブルがあるものとします。

　EmployeeIDの値は連続していて番号に抜けがないため、汎用的なクエリで特定のページのデータを簡単に取得できます。

SELECT ...
FROM Employees
WHERE EmployeeID BETWEEN (’’page * recsPerPage’’) + 1
AND (’’page * recsPerPage’’) + ’’recsPerPage ’’

　例えば、1ページに表示する従業員が3人の場合、1ページ目のデータを表示するには、次のように指定します。

SELECT ...
FROM Employees
WHERE EmployeeID BETWEEN (0 * 3) + 1 AND (0 * 3) + 3
-- Which is tantamount to... SELECT ...
FROM Employees
WHERE EmployeeID BETWEEN 1 AND 3

　これにより、最初の3人の従業員が返されます。2番目のページを表示するには、次のように指定します。

SELECT ...
FROM Employees
WHERE EmployeeID BETWEEN (1 * 3) + 1 AND (1 * 3) + 3
-- Which is tantamount to... SELECT ...
FROM Employees
WHERE EmployeeID BETWEEN 4 AND 6

　これにより、2ページ目のデータであるEmployeeID 4、5、6が返されます。

　この方法は、ページングされるデータの主キー値に抜けがなければうまくいきます。しかし、レコードが削除されると、欠番が生じます。例えば、Sam LeeとHans Smithが解雇されると、「Employees」テーブルは次のようになります。

　EmployeeID 1と4の間に欠番があるため、先ほど使用した単純なアルゴリズムではうまくいきません。

　このような欠番に対処するため、Daniel Andersonのストアドプロシージャでは偽の連番IDを作成しています。この処理は、次のアルゴリズムによって行われます。

1. まず、ページング対象のデータの各列に対応する列と、IDという名前の列を含んだ一時テーブルを作成します。このID列を「IDENTITY列」としてマークします（IDENTITY列としてマークされた列の値は、レコードが挿入されるたびに自動的に増分します）。
2. ページング対象のテーブルの内容全体を、ステップ1で作成した一時テーブルにダンプします。これによって、ID列の値は1、2、3、...に設定されます。
3. この一時テーブルに対してSELECTクエリを行い、WHERE句に前述の単純な式を適用します。

　Danielのストアドプロシージャの詳細については、「Paging through Records using a Stored Procedure」を参照してください。ストアドプロシージャの具体的なコードは、ここで参照できます。

　一見するとこの方法は、すべてのレコードをそのまま返すより良いように思えます。しかし、結局は一時テーブルにダンプしているだけで、「Employees」のすべてのレコードを処理していることに変わりありません。とはいえ、Danielの方法は、データベースサーバーとWebサーバーとの間のトラフィックを大きく削減します。「Employees」テーブルのデータ全部を処理するという点は同じですが、ストアドプロシージャが返すのは10レコード（または、設定されている1ページ当たりのレコード数）だけです。

Danielのストアドプロシージャの現代化

　Danielのストアドプロシージャのパフォーマンスを改善する前に、これを少し現代風にしておきましょう。このストアドプロシージャが書かれたのは1999年であり、「ベストプラクティス」とは言えない方法もいくつか使われています。その1つがストアドプロシージャの名前で、「sp_」というプレフィックスが使用されていますが、このように指定されると、SQL Serverは最初にmasterデータベースを検索します。これがパフォーマンスに及ぼす影響はごくわずかですが、この機会に修正しておきましょう（このトピックの詳細については、「Should I Use the sp_ Prefix for Procedure Names?」を参照してください）。

　次に、一時テーブルではなくテーブル変数を使用するようにしましょう。Microsoftも、「大量のデータを扱うテーブルを何度も使用する場合を除いては、可能な限りテーブル変数を使用すること」と推奨しています。Danielのストアドプロシージャは、一時テーブルにデータを1回だけダンプし、それを読み取れば処理は終了するため、Microsoftの提言に従うならばテーブル変数を使用するべきです。

　最後に、ASP.NET 2.0のObjectDataSourceとGridViewで使われるカスタムページングロジックは、アクセスするレコードを定義するときに、ページインデックスと1ページ当たりのレコード数ではなく、開始行インデックスと返される最大行数を用います。そこで、ストアドプロシージャが受け取る整数型の入力パラメータを、@Pageと@RecsPerPageではなく、@startRowIndexと@maximumRowsにします。これによって、最終的なクエリのWHERE句で使用される式が単純化されます。

ストアドプロシージャのパフォーマンスの改善

　Danielの方法は、ページング対象のテーブルのすべての列を一時テーブルにダンプするため、幾分コストがかかります。この方法では、対象データについての完全なテーブルスキャンが必要です。そこで代わりの方法として、ページング対象テーブルの主キー列だけを保持するテーブル変数を作成することにします。このようなレコードはクラスタ化インデックスに存在する可能性が高いため、ディスクを読みに行かなくてもテーブル変数の値を完全にメモリから読み込める率が高まります。

　ページング対象テーブルのその他の列は、ストアドプロシージャの最後のクエリで返すようにします。最後のクエリでは、テーブル変数に基づいて適切なレコードのサブセットを取得します。Danielの元のストアドプロシージャを現代化し、最適化すると、次のようになります。

CREATE PROCEDURE PagedResults_New
(
   @startRowIndex      int,
   @maximumRows      int
)
AS

--Create a table variable
DECLARE @TempItems TABLE
(
   ID int IDENTITY,
   EmployeeID int
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO @TempItems (EmployeeID)
SELECT EmployeeID
FROM Employees

-- Now, return the set of paged records
SELECT e.*, d.[Name] as DepartmentName
FROM @TempItems t
   INNER JOIN Employees e ON
      e.EmployeeID = t.EmployeeID
   INNER JOIN Departments d ON
      d.DepartmentID = e.DepartmentID
WHERE ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
GO

古い方法と新しい方法のパフォーマンスの比較

　ページング対象テーブルの主キー列のみをテーブル変数に読み込むという方法でパフォーマンスがどれだけ改善されるかを確認するために、SQLプロファイラを使用して、さまざまなレコード件数の「Employees」テーブルに対してさまざまなクエリを行い、結果を比較しました。この結果はあまり厳正なものではないという点に注意してください。それぞれを何度も実行して「ウォームアップ」したのち、9回のテスト実行を記録して、平均を計算しました。ただし、このテストは、多様なプログラムが動作している私の個人用マシンで行ったものです。また、Webサイトへの複数の同時アクセスによる負荷もシミュレートしていません。結果は次に示すとおりです（ダウンロードサンプルに、テスト結果のExcelスプレッドシートも含まれています）。修正版ストアドプロシージャのテスト結果の欄には、修正前の方法と比較した場合の改善率を参考までに示しています。

　テストの際、私は、テーブル変数のID列を主キーとして指定すれば、パフォーマンスが改善されるのではないかと考えました。ID列を主キーとしてマークすれば、クラスタ化インデックスが作成されます。つまり、最終クエリで正しいレコードのサブセットを検索するときに、結果がID列でソートされ、インデックスがスキャンされるというわけです。しかし、ページング対象テーブルの内容をテーブル変数にダンプするときに、インデックスは新たなオーバーヘッドを招きます。詳しい数値はダウンロードサンプルのスプレッドシートに記載してありますが、ID列を主キーとしてマーキングした場合のパフォーマンスは、テーブルの内容全体を一時テーブルにダンプするという古い方法よりは優れていますが、主キーとしてマーキングしなかった場合よりは劣ります。

　この方法は、SQL Server 2000で大量の結果セットをページングするときにはそれなりに効果を発揮しますが、「Custom Paging in ASP.NET 2.0 with SQL Server 2005」に書かれているとおり、SQL Server 2005のROW_NUMBER()はそれよりはるかに優れたパフォーマンスを実現します。全部で50,000のテーブルから10レコードを返すのに、平均してわずか3ミリ秒しかかからないのです。本稿の方法では同じ処理に307ミリ秒かかりますから、なんと2桁も差があります。

　しかし、本稿で説明した方法にまったくメリットがないわけではありません。すべてのレコードを返すよりも、それをテーブル変数にダンプした方が短時間で済むということを思い出してください。「Custom Paging in ASP.NET 2.0 with SQL Server 2005」によると、GridViewの既定ページングを使って50,000レコードをページングすると、Employeesテーブルのすべてのレコードを取得するのに平均1,411ミリ秒もかかります。また、ROW_NUMBER()はSQL Server 2005の新機能であるため、SQL Server 2000を使用している場合は、引き続きテーブル変数の方法を使用することになります。

まとめ

　本稿では、ストアドプロシージャを使って特定のページのデータを取得する方法について説明しました。この方法は、大量の結果セットに対して、ASP.NET 1.xのDataGridやASP.NET 2.0のGridViewを使って効率的なカスタムページングを行う場合に役立ちます。また、典型的なASPアプリケーションや、ページングデータを処理する必要があるその他のデータドリブンアプリケーションでも、この方法を使用することができます。SQL Server 2005を使用している場合は、この方法ではなく、「Custom Paging in ASP.NET 2.0 with SQL Server 2005」で説明しているROW_NUMBER()キーワードを使ってください。

　それでは、ハッピープログラミング！