Web アプリケーションでは、大規模なデータベース結果セットのページ分割はよく知られた問題です。簡単に言うと、すべてのクエリ データを 1 つのページに表示する必要はないため、ページングを使用して表示する方が適切です。従来の ASP ではこれは簡単なタスクではありませんが、ASP.NET では、DataGrid コントロールによってこのプロセスが数行のコードのみに簡略化されます。したがって、asp.net ではページングは非常に単純ですが、デフォルトの DataGrid ページング イベントはデータベースからすべてのレコードを読み取り、それらを asp.net Web アプリケーションに置きます。 100 万を超えるデータがある場合、深刻なパフォーマンスの問題が発生します (これが信じられない場合は、アプリケーションでクエリを実行し、タスク マネージャーで aspnet_wp.exe のメモリ消費量を確認してください)。これが理由です。現在のページで必要なデータ レコードのみが取得されるように、ページング動作をカスタマイズする必要があります。
インターネット上には、この問題に関する記事や投稿が数多くあり、成熟した解決策もいくつかあります。この記事を書く目的は、すべての問題を解決するストアド プロシージャを示すことではなく、既存のメソッドを最適化し、開発のニーズに応じて実行できるようにテストするアプリケーションを提供することです。
しかし、私は現在ネット上で紹介されている方法にはあまり満足していません。まず、従来の ADO が使用されます。これは明らかに「古い」ASP 用に書かれています。残りのメソッドは SQL Server ストアド プロシージャであり、記事の最後にあるパフォーマンス結果からわかるように、応答時間が遅すぎて使用できないものもありますが、いくつか気になったものがいくつかあります。
一般化
現在一般的に使用されている 3 つのメソッド、つまり一時テーブル (TempTable)、動的 SQL (DynamicSQL)、および行カウント (Rowcount) を注意深く分析したいと思います。以下では、2 番目のメソッドを (昇順-降順) Asc-Desc メソッドと呼ぶことにします。動的 SQL ロジックは別のメソッドでも適用できるため、動的 SQL という名前は適切ではないと思います。これらすべてのストアド プロシージャに共通する問題は、主キー列 (PK 列) だけでなく、どの列に基づいて並べ替えるのかを見積もる必要があることです。これにより、クエリごとに一連の問題が発生する可能性があります。ページングを通じて表示します。これは、異なる並べ替え列ごとに多くの異なるページング クエリが必要になることを意味します。つまり、(どのページング方法が使用されているかに関係なく) 並べ替え列ごとに異なるストアド プロシージャを実行するか、または次のいずれかを実行する必要があります。動的 SQL を利用して、この機能をストアド プロシージャに組み込みます。これら 2 つの方法はパフォーマンスにわずかな影響を与えますが、特にこの方法を使用してさまざまなクエリを表示する必要がある場合は、保守性が向上します。したがって、この記事では、動的 SQL を使用してすべてのストアド プロシージャを要約しようとしますが、いくつかの理由により、部分的な汎用性しか実現できないため、複雑なクエリに対しては依然として独立したストアド プロシージャを作成する必要があります。
主キー列を含むすべての並べ替えフィールドを許可する場合の 2 番目の問題は、それらの列に適切にインデックスが付けられていない場合、これらの方法はいずれも役に立たないことです。これらすべての方法では、最初にページング ソースを並べ替える必要があります。大規模なデータ テーブルの場合、インデックス以外の列を使用した並べ替えのコストは無視できます。この場合、応答時間が長いため、実際の状況ではすべてのストアド プロシージャを使用できません。 (対応する時間は、テーブルのサイズと取得する最初のレコードに応じて、数秒から数分まで異なります)。他の列のインデックスにより、さらに望ましくないパフォーマンスの問題が発生する可能性があります。たとえば、毎日大量のデータをインポートすると、パフォーマンスが非常に遅くなる可能性があります。
一時テーブル
まず、一時テーブルの方法について説明します。これは、私がプロジェクトで何度か遭遇した、広く推奨されているソリューションです。このメソッドの本質を見てみましょう:
CREATE TABLE #Temp(
ID int IDENTITY 主キー、
PK /*ここにPKtypeがあります*/
)
INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn
SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow
すべての行を一時にコピーしますテーブルの場合、クエリ (SELECT TOP EndRow...) をさらに最適化できますが、重要なのは最悪のシナリオです。100 万レコードを含むテーブルでは、100 万レコードを含む一時テーブルが生成されます。
この状況を考慮し、上記の記事の結果を見て、このテストでは昇順降順
メソッドを放棄することにしました。
このメソッドでは、サブクエリでデフォルトのソートを使用し、メインクエリで逆ソートを使用します。原理は次のとおりです。
DECLARE @temp TABLE(
PK /* PKタイプ */
NULL ではないプライマリ
)
INSERT INTO @temp SELECT TOP @PageSize PK FROM
(
SELECT TOP(@StartRow + @PageSize)
PK、
SortColumn /* ソート列が PK と異なる場合、SortColumn は必須です
それ以外の場合は PK だけが必要です
*/
ORDER BY ソート列
/*
デフォルトの順序 - 通常は ASC
*/
)
ORDER BY 並べ替え列
/*
デフォルトの順序を逆にする - 通常は DESC
*/
SELECT FROM Table JOIN @Temp temp ON Table .PK= temp .PK
ORDER BY 並べ替え列
/*
デフォルトの順序
*/
行カウント
方法の基本ロジックはSQL の SET ROWCOUNT 式に依存しているため、不要な行をスキップして必要な行レコードを取得できます。
DECLARE @Sort /* ソート列のタイプ */
SET ROWCOUNT @ StartRow
SELECT @Sort=SortColumn FROM テーブル ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
私が検討したサブクエリの
方法は他にも 2 つありますが、それらのソースは異なります。 1 つ目は、よく知られているトリプル クエリまたは自己クエリ メソッドです。この記事では、他のすべてのストアド プロシージャを網羅する同様の共通ロジックも使用します。ここでのアイデアは、プロセス全体に接続することです。テストでは Recordcount が必要ないため、元のコードをいくつか削減しました)
SELECT FROM Table WHERE PK IN(
PK が入っていないテーブルから @PageSize PK をトップを選択します
(
SELECT TOP @StartRow PK FROM テーブル ORDER BY SortColumn)
ORDER BY 並べ替え列)
ORDER BY SortColumn
Cursor
Googleディスカッショングループを見ていたら、最後の方法を見つけました。この方法では、サーバー側の動的カーソルを使用します。多くの人はカーソルの使用を避けようとします。その理由は、カーソルは順序性のために無関係で非効率だからです。しかし、振り返ってみると、どの方法を使用しても、記録するには開始行に戻る必要があります。前の方法では、まず記録を開始する前にすべての行を選択し、記録に必要な行を追加してから、以前の行をすべて削除します。動的カーソルには、魔法のジャンプを実行する FETCH RELATIVE オプションがあります。基本的なロジックは次のとおりです。
DECLARE @PK /* PKType */
DECLARE @tblPK
TABLE(
PK /*PKType*/ NOT NULL 主キー
)
DECLARE PagingCursor CURSOR DYNAMICCREAD_ONLY FOR
SELECT @PK FROM テーブル ORDER BY SortColumn
OPEN PagingCursor
@PageSize>0 かつ @@FETCH_STATUS =0 の場合に、
PagingCursor INTO @PK から @StartRow を相対的に取得します
始める
@tblPK(PK) 値(@PK)を挿入
PagingCursor INTO @PK から次をフェッチします
SET @PageSize = @PageSize - 1
終了
クローズ
ページングカーソル
割り当てを解除する
PagingCursor
SELECT FROM Table JOIN @tblPK temp ON Table .PK= temp .PK
ORDER BY SortColumn での
複雑なクエリの一般化
前に、すべてのストアド プロシージャは動的 SQL を使用して一般化を実現しているため、理論上はあらゆる種類の複雑なクエリを使用できることを指摘しました。以下は、Northwind データベースに基づく複雑なクエリの例です。
SELECT Customers.ContactName AS Customer, Customers.Address + ' , ' + Customers.City + ', '+ Customers. Country
AS アドレス、SUM([注文詳細].単価*[注文詳細] .数量)
AS [Totalmoneyspent]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID
WHERE Customers. Country <> 'USA' AND Customers. Country <> 'Mexico '
顧客.連絡先名、顧客.住所、顧客.市、顧客.国によるグループ化
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000
ORDER BY Customer DESC ,Address DESC は、
次のように 2 ページ目のページング ストレージ呼び出しを返します:
EXEC ProcedureName
/*Tables */
'
お客様
INNER JOIN 注文 ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
、
/* PK */
'
顧客.顧客ID
'
、
/* オーダーバイ */
'
Customers.ContactName DESC、Customers.AddressDESC
'
,
/*ページ番号 */
2
,
/*ページサイズ*/
10
,
/*フィールド */
'
Customers.連絡先名 AS Customer、
Customers.Address+'' , '' +Customers.City+ '' , '' +Customers. Country ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
、
/*フィルター */
'
Customers.国<> '' 米国 '' ANDCustomers.国<> '' メキシコ '' 、
/*GroupBy */
'
顧客.顧客ID、顧客.連絡先名、顧客.住所、
顧客.市、顧客.国
HAVING(SUM([注文詳細].単価*[注文詳細].数量))>1000
'
元のクエリの ORDER BY ステートメントでエイリアスを使用していることに注意してください。ただし、記録を開始する前に行をスキップするのは時間がかかるため、ページング ストアド プロシージャではこれを行わない方がよいでしょう。実際には実装方法はたくさんありますが、原則として最初にすべてのフィールドを含めるのではなく、主キー列 (RowCount メソッドの並べ替え列に相当) のみを含めることで、完了を高速化できます。タスク。リクエスト ページでのみ、すべての必須フィールドが取得されます。さらに、最終的なクエリにはフィールドの別名はなく、スキップ行クエリでは事前にインデックス列を使用する必要があります。
RowCount ストアド プロシージャには、複数の列を並べ替えることができるにもかかわらず、ORDER BY ステートメントでは 1 つの列しか使用できないという別の問題があります。主キーにはフィールドが 1 つだけ存在することを確認する必要があります。これは、より動的 SQL を使用すれば解決できると思いますが、私の意見では、それだけの価値はありません。このような状況が発生する可能性はありますが、それほど頻繁に発生するわけではありません。通常、上記の原則を使用してストアド プロシージャを個別にページングすることもできます。
パフォーマンス テスト
テストでは 4 つの方法を使用しました。より良い方法がある場合は、知りたいと思います。とにかく、これらの方法を比較し、そのパフォーマンスを評価する必要があります。まず、私の最初のアイデアは、ページング DataGrid を含む asp.net テスト アプリケーションを作成し、ページの結果をテストすることです。もちろん、これはストアド プロシージャの実際の応答時間を反映しているわけではないため、コンソール アプリケーションの方が適しています。 Web アプリケーションも含めましたが、これはパフォーマンス テストのためではなく、DataGrid のカスタム ページネーションとストアド プロシージャが連携する例として含まれています。
テストでは、自動生成された大規模なデータ テーブルを使用し、約 500,000 個のデータを挿入しました。実験するためのそのようなテーブルがない場合は、ここをクリックして、データを生成するためのテーブル設計とストアド プロシージャ スクリプトをダウンロードできます。自動インクリメントされる主キー列を使用する代わりに、一意の識別子を使用してレコードを識別しました。前述のスクリプトを使用する場合は、テーブルの生成後に自動インクリメント列を追加することを検討してください。これは、ページ分割されたストアド プロシージャを使用することを意味します。主キーソートを使用して現在のページのデータを取得します。
パフォーマンス テストを実装するために、ループを通じて特定のストアド プロシージャを複数回呼び出し、平均応答時間を計算しました。キャッシュの理由を考慮すると、実際の状況をより正確にモデル化するために、ストアド プロシージャへの複数の呼び出しで同じページがデータを取得するのにかかる時間は、通常は評価に適さないため、同じストアド プロシージャを呼び出す場合、 、各呼び出しで要求されるページ番号はランダムである必要があります。もちろん、ページ数は 10 ～ 20 ページに固定されており、異なるページ番号のデータがランダムに何度も取得される可能性があると想定する必要があります。
すぐにわかることの 1 つは、応答時間は、結果セットの開始位置から取得されるページ データの相対的な距離によって決まるということです。結果セットの開始位置から離れるほど、より多くのレコードが生成されます。これは、ランダムなシーケンスに上位 20 を含めない理由でもあります。別の方法として、2^n ページを使用します。ループのサイズは、必要なさまざまなページの数 * 1000 です。したがって、各ページはほぼ 1000 回フェッチされます (ランダムな理由による偏差は確実にあります)。
結果は
次のとおり
です。私のテスト結果は次のとおりです。 
結論
テストは、行数、カーソル、昇順/降順、サブクエリのパフォーマンスが最も優れたものから最も悪いものへの順序で実行されました。興味深い点の 1 つは、通常、ユーザーが最初の 5 ページ以降のページにアクセスすることはほとんどないため、この場合、結果セットのサイズとページの出現頻度を予測する距離に応じて、サブクエリ方法がニーズに合う可能性があることです。 、これらの方法を組み合わせて使用することもあるでしょう。私だったら、どのような場合でも行カウント方法を好みます。最初のページであっても、かなりうまく機能します。ここでの「任意のケース」は、一般化が難しいいくつかのケースを表しています。この場合、私は次のようにします。カーソル。 (おそらく最初の 2 つはサブクエリ方式を使用し、その後はカーソル方式を使用するでしょう)