ASP.NET 2.0でカスタムのページング処理と並び替えを併用する

はじめに

　先週の投稿「Custom Paging in ASP.NET 2.0 with SQL Server 2005」では、ASP.NET 2.0でカスタムのページング処理を適切に実装する方法と、型指定されたDataSet（Typed DataSet）、ObjectDataSourceコントロール、SQL Server 2005の新しいROW_NUMBER()キーワードを使って大量のデータを効率良くページング処理する方法を紹介しました。カスタムのページング処理は、表示の必要があるレコードのサブセットのみをページごとに賢く取得するのに対し、既定のページング処理は、実装は簡単ですが、ページごとにすべてのレコードを取得するので、対象データがかなり多い場合はパフォーマンスが低下します。「Custom Paging in ASP.NET 2.0 with SQL Server 2005」で紹介した非科学的なテストでは、50,000件のレコードがあるデータベースで、既定のページング処理を指定すると各ページの表示に2秒かかったのに対し、カスタムのページング処理を指定すると各ページを0.03秒足らずで読み込むことができました。

　とはいえ、カスタムのページング処理にも問題はあります。カスタムのページング処理では、処理する結果が大きければパフォーマンスは驚くほど向上しますが、その分、実装が難しくなります。さらに、カスタムのページング処理を追加した場合は、結果の並べ替えを可能にする工夫をしなければなりません。本稿では、カスタムのページング処理を拡張して結果の昇順/降順の並べ替えのサポートを組み込む方法を説明します。それでは、始めましょう。

　なお、本稿に取り掛かる前に、「Custom Paging in ASP.NET 2.0 with SQL Server 2005」をお読みになることをお勧めします。

カスタムのページング処理と並べ替えを簡単に併用できない理由

　ASP.NET 1.xに比べて、ASP.NET 2.0では、主にGridView（ASP.NET 1.x DataGridの2.0アップグレード版）のおかげでデータの表示と処理が非常に簡単になりました。SqlDataSourceをGridViewにバインドしている場合、あるいは、並べ替えをサポートするオブジェクト（厳密に型指定されたDataTableなど）を返すObjectDataSourceをGridViewにバインドしている場合に、GridViewで昇順/降順の並べ替えを利用するには、GridViewのスマートタグで［並べ替えを有効にする］チェックボックスをオンにするだけ（または、手動でAllowSortingプロパティをTrueに設定するだけ）です。コーディングは必要ありません。

　このようにチェックボックスをオンにするだけで済むというアプローチは、データがページング処理に対応していない場合や既定のページング処理を使う場合に、結果をObjectDataSourceにバインドするときには非常に便利です。しかし、カスタムのページング処理で期待どおりの並べ替えを行うためには、もう少し工夫が必要です。カスタムのページング処理を使う場合も、並べ替えをサポートするようにGridViewを設定でき、GridViewは忠実に列見出しをLinkButtonに変換します。ただし、列見出しをクリックしても、その列を基準に並べ替えられるのは現在表示されているデータだけです。

　分かりやすく説明しましょう。例えば、「Custom Paging in ASP.NET 2.0 with SQL Server 2005」で行ったように、カスタムのページング処理を使って「Employees」データベーステーブルから50,000件のレコードを表示するとします。ユーザーは、このデータの1ページ目を表示します。ここで思い出してください。既定では、グリッドは最初にEmployeeIDを基準に並べ替えられるので、50,000件のレコードのうち先頭の10人の従業員が表示されます。

　次に、この企業の最低給与はいくらかを調べたいので、Salary列の見出しのLinkButtonをクリックするとします。このクリックによってポストバックが発生し、ObjectDataSourceに対してデータの再クエリが実行されます。ObjectDataSourceは、ベースオブジェクトからデータを取得した後、そのデータを要求したデータWebコントロールにデータを返すまでの間に、厳密に型指定されたDataTableを、ID順に並べ替えた後の行のSortExpression値（この場合はSalary）に基づいて並べ替えます。

　問題がお分かりでしょうか。カスタムのページング処理では、表示する必要がある10人の従業員しか返されないので、ObjectDataSourceも1ページ目の同じ10人の従業員しか返しません。つまり、表示される結果は、1ページ目の10人の従業員を「Salary」列の昇順に並べ替えたものです。

　これを見る限りでは、Dave Johnsonの30,180ドルが最低給与のようですが、実際にはこの給与は1ページ目の従業員の中で一番低いだけに過ぎません（30,000ドルというもっと低い給与の従業員が7人います）。

並べ替え後の正しいページを表示する

　カスタムのページング処理と並べ替えを正しく実装するには、まず特定の列を基準に並べ替えた後の従業員レコードの正しいサブセットを取得する必要があります。「Custom Paging in ASP.NET 2.0 with SQL Server 2005」で紹介した、従業員の適切なサブセットを返す場合に使う次のようなストアドプロシージャを思い出してください。

SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary,
         HireDate, DepartmentName
FROM
   (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary,
         HireDate, d.Name as DepartmentName,
         ROW_NUMBER() OVER(’’ORDER BY EmployeeID’’) as RowNum
    FROM Employees e
      INNER JOIN Departments d ON
         e.DepartmentID = d.DepartmentID
   ) as EmpInfo
WHERE RowNum BETWEEN @startRowIndex AND
    (@startRowIndex + @maximumRows) - 1

　このストアドプロシージャは、連続する行番号を、EmployeeID順に並べ替えたそれぞれの従業員レコードに関連付けます。次に、@startRowIndexパラメータと@maximumRowsパラメータで指定された範囲のレコードを返します。

　ほかの列（Salaryなど）を基準として従業員を並べ替えたうえで、特定のページを取得する場合は、ORDER BY句を変更する必要があります。次の例では、Salaryを基準として昇順で（最低給与から順に）結果が並べ替えられます。この結果をSalaryの降順で並べ替える場合は、ORDER BY句にDESCキーワードを追加します。

SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary,
         HireDate, DepartmentName
FROM
   (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary,
         HireDate, d.Name as DepartmentName,
         ROW_NUMBER() OVER(’’ORDER BY Salary’’) as RowNum
    FROM Employees e
      INNER JOIN Departments d ON
         e.DepartmentID = d.DepartmentID
   ) as EmpInfo
WHERE RowNum BETWEEN @startRowIndex AND
    (@startRowIndex + @maximumRows) - 1

　理論上は、並べ替える列ごとに別々のストアドプロシージャを用意するか、処理を担当する1つのストアドプロシージャ内にT-SQLのIF/ELSEステートメントを複数追加する必要があります。これは大変です。

　しかし嬉しいことに、ストアドプロシージャにはダイナミックSQLの選択肢がいくつかあります。1つは、「Dynamic ORDER BY Statements in Stored Procedures」と「The Power of SQL CASE Statements」で説明したように、ORDER BY句でCASEステートメントを使う方法です。残念ながら、この方法で非文字列の列を並べ替えると、変換要件などが原因でパフォーマンスが低下します。

　パフォーマンスが向上する選択肢は、動的SQLステートメントを作成して実行する方法です。この方法は「Dynamic ORDER BY Statements in Stored Procedures」でも説明しました。このアプローチでは、並べ替える列にインデックスを使うことによって、並べ替えのないカスタムのページング処理と同じパフォーマンスが実現されます。

-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = ’SELECT EmployeeID, LastName, FirstName, DepartmentID,
         Salary, HireDate, DepartmentName
FROM
   (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary,
         HireDate, d.Name as DepartmentName,
         ROW_NUMBER() OVER(ORDER BY ’ + @sortExpression + ’)
         as RowNum
    FROM Employees e
      INNER JOIN Departments d ON
         e.DepartmentID = d.DepartmentID
   ) as EmpInfo
WHERE RowNum BETWEEN ’ + CONVERT(nvarchar(10), @startRowIndex) +
            ’ AND (’ + CONVERT(nvarchar(10), @startRowIndex) + ’ + ’
            + CONVERT(nvarchar(10), @maximumRows) + ’) - 1’

-- Execute the SQL query
EXEC sp_executesql @sql

　本稿のダウンロードサンプルに収録されているデータベースには、次の2つのストアドプロシージャが含まれています。

• GetEmployeesSubset(@startRowIndex, @maximumRows)
@startRowIndex行から@maximumRows行までのレコードのサブセットを返し、結果をEmployeeID列を基準に並べ替えます。
• GetEmployeesSubsetSorted(@sortExpression, @startRowIndex, @maximumRows)
@startRowIndex行から@maximumRows行までのレコードのサブセットを返し、結果を@sortExpressionを基準に並べ替えます。

　また、「Employees」テーブルでは、並べ替えることができる列のそれぞれに充てん率（Fill Factor）を90とするインデックスが割り当てられています。このインデックスがないと、結果の並べ替えに時間がかかり、全体的な実行時間が著しく長くなる可能性があります。この後で紹介する非常に非科学的なテスト結果から分かるように、インデックスを使わなかった場合の並べ替え時間は平均0.189秒だったのに対し、インデックスを追加した場合の平均時間は0.038秒でした。

カスタムのページング処理と並べ替えを併用できるようにObjectDataSourceを設定する

　GetEmployeesSubsetSortedストアドプロシージャが完成したら、最後に、ObjectDataSourceがこのストアドプロシージャにGridViewのSortExpression値を渡すようにします。このためには、型指定されたDataSetのEmployeesTableAdapterクラスに、GetEmployeesSubsetSortedストアドプロシージャを呼び出すGetEmployeesSubsetSortedというメソッドを追加します。次に、この新しいメソッドを使うようにObjectDataSourceを設定します。最後に、ObjectDataSourceのSortParameterNameプロパティに、並べ替え式を受け取るパラメータの名前（sortExpression）を指定します。

　ObjectDataSourceの宣言マークアップは次のようになります。

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    SelectMethod="GetEmployeesSubsetSorted"
    TypeName="EmployeesTableAdapters.EmployeesTableAdapter"
    EnablePaging="True"
    SelectCountMethod="GetEmployeesRowCount"
    ’’SortParameterName="sortExpression"’’>
</asp:ObjectDataSource>

　本稿のダウンロードサンプルには、並べ替えのないカスタムのページング処理と、並べ替えのあるカスタムのページング処理の両方を示す例が含まれています。並べ替えのないカスタムのページング処理の例については、「Default.aspx」のデモを参照してください。カスタムのページング処理と並べ替えの両方を使っている例については、「CustomPagingAndSorting.aspx」のデモを参照してください。

並べ替えのあるカスタムのページング処理のパフォーマンス

　動的なORDER BY句を使って並べ替え機能を追加すると、カスタムのページング処理のパフォーマンスに影響しますが、それでも、データセットがかなり大きい場合は、既定のページング処理に比べて実行時間は著しく短くなります。次の表に、既定のページング処理、カスタムのページング処理、および並べ替えのあるカスタムのページング処理の非常に非科学的なトレース結果を示します。

ASP.NETのトレース結果

　列にインデックスを付けずに並べ替えを行った場合、データを昇順にするASP.NETページの平均読み込み時間は0.189秒です。充てん率を90とする非クラスタ化インデックスを追加すると、平均読み込み時間は0.038秒に短縮され、パフォーマンスは大幅に向上します。この差は、データセットが大きい場合にさらに顕著になります（理想的なパフォーマンスを考えた場合、インデックスの実際の充てん率と埋め込み設定は、アプリケーションの「Employees」テーブルに対する読み取り対書き込みの比率によって異なります）。

まとめ

　カスタムのページング処理は、結果セットがかなり大きければアプリケーションのパフォーマンスを大幅に向上させることができますが、「Custom Paging in ASP.NET 2.0 with SQL Server 2005」で説明したように、実装は既定のページング処理の簡便さに遠くおよびません。カスタムのページング処理を実装すると、既定のページング処理に内在する昇順/降順の並べ替え機能が使えなくなってしまいます。カスタムのページング処理と並べ替えの両方を利用するには、まず、データを指定の順序に並べ替えて正しいページのデータを返すストアドプロシージャを追加します。その後、データアクセス層（この例では型指定されたDataSet）に、並べ替えの基準列を入力パラメータとして受け取り、このストアドプロシージャを呼び出すメソッドを組み込みます。最後に、ObjectDataSourceのSortParameterNameパラメータに、並べ替えの基準となる入力パラメータの名前を指定します。

　本稿で説明したように、動的なORDER BY句を使って並べ替えを行う単一のストアドプロシージャを作成することもできますが、これを使うと最適なパフォーマンスを得ることができなくなります。

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