はじめに
リレーショナルデータベースでは、操作は一連の行に対して行われます。たとえば、SELECTステートメントは、結果セットと呼ばれる行のセットを返します。アプリケーションロジックは、結果セット全体を一度に処理するのではなく、一度に1行ずつ処理する必要がある場合があります。 T-SQLでは、これを行う1つの方法はCURSORを使用することです。
プログラミングスキルがある場合は、FORやWHILEなどのループを使用して、一度に1つの項目を反復処理し、次の操作を実行します。データとジョブが完了します。 T-SQLでは、CURSORは同様のアプローチであり、同じロジックに従うため、推奨される場合があります。ただし、この方法をとると問題が発生する可能性があることに注意してください。
CURSORを使用してもそれほど混乱しない場合もありますが、通常は回避する必要があります。以下に、CURSORを使用するとパフォーマンスの問題が発生するいくつかの例を示し、同じジョブを他の多くの方法で実行できることを確認します。
このデモンストレーションでは、AdventureWorks2012データベースを使用します。からデータを取得したいとします。テーブル、製造に1日もかからないすべての製品、つまりテーブルから..
カーソルの例
CURSORを使用して開始し、次の構文を記述します。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
USE AdventureWorks2012
GO
DECLARE @id int
DECLARE cursorT CURSOR
–LOCAL STATIC
–LOCAL FAST_FORWARD
–LOCAL READ_ONLY FORWARD_ONLY
FOR
SELECT ProductId
FROM AdventureWorks2012.Production.Product
WHERE DaysToManufacture < = 1
OPEN cursorT
FETCH NEXT FROM cursorT INTO @id
WHILE @@ FETCH_STATUS = 0
BEGIN
SELECT * FROM Production.ProductInventory
WHERE ProductID = @ id
FETCH NEXT FROM cursorT INTO @id
END
CLOSE cursorT
DEALLOCATE cursorT
|
短い休憩の後、クエリの実行が終了し、以下に示す時間で833行が返されました。上記で選択した構文はデモのみを目的としており、速度を上げるためにインデックスの調整は行っていません。
カーソルの実行には、2つのステップがあります。ステップ1、カーソルが結果セットの行に位置を設定するときのポジショニング。ステップ2、FETCHと呼ばれる操作でその特定の行からデータを取得するときの取得。
この例では、カーソルは最初のSELECTによって返された最初の行に位置を設定し、@ id変数のWHERE条件に一致するProductID値をフェッチします。次に、2番目のSELECTは変数値を使用してからデータを取得します。次の行がフェッチされます。これらの操作は、処理する行がなくなるまで繰り返されます。
最後に、CLOSE構文は現在の結果セットを解放し、カーソルが使用する行からロックを削除し、DEALLOCATEはカーソル参照を削除します。
デモテーブルは比較的小さく、約1,000を含みます。と500行。数百万行のテーブルをループする必要がある場合、それはかなりの時間続き、結果は私たちを満足させません。
カーソルにもう一度チャンスを与えて、行–LOCALSTATICのコメントを外しましょう。カーソル定義で使用できる引数はたくさんあります。詳細については、このリンクCURSOR引数を参照してください。ただし、ここでは、この2つの単語の意味に焦点を当てましょう。
LOCALキーワードを指定すると、カーソルのスコープは、カーソルが作成されたバッチに対してローカルであり、このスコープでのみ有効です。バッチの実行が終了すると、カーソルの割り当てが自動的に解除されます。また、カーソルは、ストアドプロシージャ、トリガー、またはバッチ内のローカルカーソル変数によって参照できます。
STATICキーワードは、一時テーブルのtempdbでカーソルが使用するデータの一時コピーを作成します。 。ここにいくつかの落とし穴があります。 STATICカーソルは読み取り専用であり、開いた時点からのデータでのみ機能するため、スナップショットカーソルとも呼ばれます。つまり、データベースで行われた変更は、によって使用されるデータのセットに表示されません。カーソル。基本的に、カーソルを開いてから再度開くまで、カーソルを開いた後に行われた更新、削除、または挿入は、カーソル結果セットに表示されません。
これらの引数を使用する前にこれに注意し、ニーズに一致するかどうかを確認してください。しかし、カーソルが速いかどうか見てみましょう。結果を以下に示します。
12秒は4分47秒よりもはるかに優れていますが、上で説明した制限。
この引数LOCALREAD_ONLY FORWARD_ONLYを使用して構文を実行すると、同じ結果が得られます。 READ_ONLY FORWARD_ONLYカーソルは、最初の行から最後の行にのみスクロールできます。 STATICキーワードが欠落している場合、カーソルは動的であり、使用可能な場合は動的プランを使用します。ただし、動的プランが静的プランよりも悪い場合があります。
–LOCAL FAST_FORWARDのコメントを解除するとどうなりますか？
FAST_FORWARDはREAD_ONLYおよびFORWARD_ONLYカーソルと同等ですが、静的または動的なプランからより適切なプランを選択する機能があります。
LOCAL FAST_FORWARDは、柔軟性があるため、最良の選択のようです。静的プランと動的プランのどちらかを選択しますが、FORWARD_ONLYも非常に効果的です。この両方の方法を使用して平均12秒で結果が得られましたが、いずれかを選択する前に、これらの引数を適切にテストする必要があります。
同じ結果を得るには、はるかに迅速かつパフォーマンスへの影響が少ない。
カーソルの代替
1つの方法はJOINを使用することであり、次に示すように、結果はかなり良くなります。
まず、 SQL Serverの実行時間を測定するために統計時間を有効にし、2つのテーブル間で内部結合を作成する必要があります。および。 ProductID列。 EXECUTEボタンを押した後、カーソルメソッドからの04:47時間と比較して、330ミリ秒で同じ結果が生成され、笑顔になりました。
|
1
2
3
4
5
6
7
8
9
|
USE AdventureWorks2012
GO
SET STATISTICS TIME ON
SELECT * FROM Production.ProductInventory as pinv
INNER JOIN Production.Product as pp
ON pinv.ProductID = pp.ProductID
WHERE pp.DaysToManufacture < = 1
|
必要なものを取得するためにすべての行を反復処理する必要はありません。ループ、while句、反復子はありません。代わりに、データセットを使用して、必要なものをより速く取得し、より少ないコードを記述しています。
カーソルの適切な使用
これで、どの程度の損傷があるかがわかりました。カーソルでできるので、それを利用できる例を見てみましょう。
データベースから特定のテーブルのみの行のサイズと数を選択するとします。これを実現するために、information_schema.tablesからの基準に基づいてすべてのテーブル名を取得し、CURSORを使用して、その各テーブル名をループし、一度に1つのテーブル名を渡してストアドプロシージャsp_spaceusedを実行し、必要な情報を取得します。 。
同じAdventureWorks2012データベースを使用し、「Sales」という名前を含むSalesスキーマからすべてのテーブルを取得します。返されたすべてのテーブル名について、information_schema.tablesからのすべての情報を確認する必要があります。
以下にT-SQL構文と取得した結果を示します。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
USE AdventureWorks2012
GO
DECLARE @TableName VARCHAR（50）-「Sales」スキーマのテーブル名
DECLARE @Param VARCHAR（50）-「sp_spaceused」プロシージャのパラメータ
DECLARE db_cursor CURSOR FOR
-「Sales」タブのみを選択es
SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_NAME like “％Sales％” and TABLE_TYPE = “BASE TABLE”
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TableName
WHILE @@ FETCH_STATUS = 0
BEGIN
-変数内の各テーブル名を連結し、それをストアドプロシージャに渡します
SET @ Param = “Sales。”+ @ TableName
-一度にすべてのテーブル名のストアドプロシージャを実行します
EXEC sp_spaceused @Param
FETCH NEXT FROM db_cursor INTO @ TableName-次のテーブル名を取得します
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
これは、CURSORがデータを一度に1行ずつ繰り返して必要な結果を取得するのに役立つ方法のひとつです。この特定のケースでは、カーソルはパフォーマンスに影響を与えることなくジョブを実行し、使いやすいです。
結論
これで完了です。カーソルを使用するときの良い例、悪い例、醜い例をいくつか示しました。ほとんどの場合、WHILE句、SSISでもJOINSを使用できます。パッケージまたは他の代替メソッドを使用して、同じ結果をより迅速に取得し、パフォーマンス出力への影響を少なくし、構文の行数を減らします。
OLTP環境と処理する大量のデータセットを扱っている場合、「CURSOR」という言葉は話さないでください。
SQLでは、考えるのではなく、データセットに対して操作を行うことを検討することをお勧めします。この種のアプローチは推奨されておらず、この使用を目的としていないため、プログラム的な方法で、反復またはループを使用します。プログラミング言語からFORやFOREACHのようなループを使用し、そのロジックをSQL操作に関連付けようとすると、ニーズに適切なソリューションを得る上での障害になります。必要なデータを取得するには、一度に1行ではなく、セットベースの操作を検討する必要があります。
カーソルは、上記の例に示すように、シリアル化された操作の一部のアプリケーションで使用できますが、通常はそうする必要があります。特に大量のデータを操作する場合は、パフォーマンスに悪影響を与えるため、避けてください。
- 作成者
- 最近の投稿
私はコンピューターエンジニアリングの学士号を持っており、.NET、C＃、およびWindowsServerのスキルも持っています。私は、パフォーマンスの調整と監視、T-SQL開発、バックアップ戦略、管理、データベースの構成の経験を積みました。
IT関連のもの、ビデオゲーム、優れた映画、エレクトロハウスミュージックに情熱を注いでいます。騒々しい。
SergiuOnetによる投稿をすべて表示
- SQL Serverカーソルの使用–長所と短所– 2016年3月23日
