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
ROWCOUNTを使って、一時テーブルに配置するレコード数を制限する方法が推奨されています。この方法の場合、一時テーブルに転送する必要があるレコード数は非常に少なくて済むため、パフォーマンスは大きく向上します。この改善については、今後の投稿記事で詳しく扱います。ROWCOUNTの詳細については、「Retrieving the First N Records from a SQL Query」を参照してください。古い方法と新しい方法のパフォーマンスの比較
ページング対象テーブルの主キー列のみをテーブル変数に読み込むという方法でパフォーマンスがどれだけ改善されるかを確認するために、SQLプロファイラを使用して、さまざまなレコード件数の「Employees」テーブルに対してさまざまなクエリを行い、結果を比較しました。この結果はあまり厳正なものではないという点に注意してください。それぞれを何度も実行して「ウォームアップ」したのち、9回のテスト実行を記録して、平均を計算しました。ただし、このテストは、多様なプログラムが動作している私の個人用マシンで行ったものです。また、Webサイトへの複数の同時アクセスによる負荷もシミュレートしていません。結果は次に示すとおりです（ダウンロードサンプルに、テスト結果のExcelスプレッドシートも含まれています）。修正版ストアドプロシージャのテスト結果の欄には、修正前の方法と比較した場合の改善率を参考までに示しています。
| Employees内のレコード数 | 平均読み取り | 平均時間（ミリ秒単位） | |
| 修正前 | 50,000 | 55,360 | 425 |
| 100,000 | 110,492 | 850 | |
| 200,000 | 220,726 | 1,567 | |
| 修正後 | 50,000 | 51,637 （6.7%の改善） | 307 （27.7%の改善） |
| 100,000 | 103,028 （6.7%の改善） | 536 （36.9%の改善） | |
| 200,000 | 205,876 （6.7%の改善） | 1,033 （34.1%の改善） |
テストの際、私は、テーブル変数の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()キーワードを使ってください。
それでは、ハッピープログラミング！
