カーソルは、開発者へのショートカットのように見えます。 実行する複雑なジョブがあり、テーブル内の行を操作する必要がある場合、Transact-SQLカーソルを使用して行を一つずつ繰り返す最も簡単な方法があります。 結局のところ、クライアント側の独自のコードでデータ構造を反復処理する必要があるため、SQL Serverデータを扱うときに同じことをするように誘惑される, しかし、Transact-SQLカーソルを使用してデータを反復することは、しばしばうまくスケーリングできないため、デザインやアーキテクチャにも優れていないことを

カーソルエクスペリエンス

数ヶ月前、データベースコンポーネントをベンダーのアプリケーションの新しいバージョンにアップグレードしたベンダーのTransact-SQLス こうして設計された、スクリプトのピボットは非常に大きなテーブルに該当するデータを新しいテーブルを水平にして処理してくれるかどうかは，文字列です。, ベンダーは、テーブルを小さくすることでパフォーマンスを向上させたかったので、詳細データを各親idのコンマ区切り文字列として水平に格納することに クライアントアプリケーションは、結果のコンマ区切り文字列を個々の行として取得するよりも速くクエリすることができ、コンテキストでは、変更が理

ただし、アップグレード中にデータをピボットするベンダーのTransact-SQLスクリプトは、テストコンピューターで実行するのに16時間かかり、アップグレードのためのダウンタイムの数時間以上の余裕がありませんでした。, ベンダーのスクリプトを調べたところ、開発者がピボットプロセスを二つのステップでコーディングしていることがわかりました：すべての親テーブルidを反復処理して空白の事前フォーマットされたテーブルを構築するカーソルと、カーソルを使用して文字列を連結する別のスクリプト。

セットベースのアプローチを使用することにより、処理時間を16時間以上から五分未満に短縮することができました。 私たちは、SELECTステートメントを使用して空白のテーブルを構築する、開発者の元の戦略に従って、私たちは二分未満にそのステップの時間を短縮しました。, 次に、親idごとに実行されるUPDATE文を使用して文字列を連結しました。 親idを介した反復はWHILEループを使用し、三分未満で終了しました。

反復の必然性

データベースデータへの多くのアクセスは、さらなる操作のためにデータを準備するために、何らかの方法で反復する必要があります。 SQL Serverエンジンでも、使用可能なさまざまなタイプの結合を使用してデータをスキャンまたは結合するときにデータを反復処理します。 これは、大きなデータセットから多くの行を返すクエリについてSQL Serverクエリプランを調べるときに確認できます。, 結合の場合、最も一般的にはネストされたループが表示されますが、時にはマージまたはハッシュ結合も表示されます。 のための簡単な質問をされており、見学することが塊-非クラスタ化されたインデックススキャン！ SQL Serverが単一の行または小さな行セットを返すことができ、テーブルに適切なインデックスがある場合にのみ、インデックスを使用してシークが表示さ

それについて考える：Microsoftは、可能な限り効率的にその利用可能なデータを反復処理するために何年もSQL Serverエンジンを最適化し、調整してきました。, あなたが時間を持っていて、エネルギーを費やす意思があれば、おそらくかなり効率的なデータベースデータファイルへの低レベルのアクセスを書くことが ただし、目の前の個々のタスクに対してのみ効率的であり、デバッグする必要があり、データアクセスの範囲が変更された場合は完全に書き直す必要が おそらく、コードを完全に最適化して一般化するには何年もかかりますが、それでもSQL Serverストレージエンジン内のコードの効率に近づくことはありません。,

だから、車輪を再発明することの利益はどこにありますか？ これは、SQL Serverエンジンが非常にうまく最適化され、デバッグされているため、反復処理を行い、データベースに既に埋め込まれている広範な開発とテストを

データ処理タスクをより詳しく見ると、カーソルが必要な場合はほとんどありません。 まず第一に、多くの場合、Transact-SQLのセットベースのSQLコマンドに依存し、テーブルの行の順序を無視することで、目標を達成できます。, 次に、Transact-SQLカーソルは、テーブルを行ごとに反復処理するための単なる方法です。 反復処理が必要なテーブルのすべての行を一意に識別できる場合は、カーソルではなくWHILEループを使用でき、パフォーマンスが向上する可能性があります。 その理由を示すために例を紹介しましょう。

反復戦略の比較

テーブルには一意のキーまたは一意の列グループがあるため、テーブルの各行を一意に識別できると仮定します。, WHILEループでは、一意の条件の最低値を見つけてから、反復処理するたびに次の最高値を見つけるだけです。 次に、SQL Server2005AdventureWorksサンプルデータベースの生産の例を示します。TransactionHistoryテーブル。 これは主キーにクラスタ化インデックスを持ち、WHILEループは毎回行を探すことができます。,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

ここでは、データを読み取るだけで最も効率的なTransact-SQLカーソルである早送りカーソルを使用して同じループです。

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

私のラップトップで、データがすべてキャッシュにあることを確認するために数回実行した後、WHILEループは九秒かかり、カーソルは17秒かかります。 あなた自身の期間は異なる場合があります。 この例では実際にはデータに対して何も行われませんが、WHILEループはより高速です。 カーソ,

カーソルには追加のコマンドも必要で、コードが雑然と見えます。 MICROSOFT SQL Server2005Books Onlineでmicrosoftが完全に説明しているカーソルの仕組みの詳細に触れることなく、WHILEループを使用するときに、何かを宣言、開く、閉じる、および割り当て ロジックはより簡単で、途中で行を自由に更新することさえできます。 カーソルを使用して行を更新するには、カーソルタイプを変更する必要があります。

WHILEループでも反復のオーバーヘッドが追加されます。, セットベースのSELECTコマンドで置き換えたり、ループ内で行う更新をセットベースのUPDATEコマンドで置き換えて、sql Serverエンジンに反復処理を残すことができます。 上記のカーソルとWHILEループと同じデータを取得するための単純なSELECTステートメントは、3秒未満かかり、クライアントに行を返します。

SELECT *FROM Production.TransactionHistory

この選択は、データを反復処理するためにSQL Serverに依存しており、これまで見てきたデータアクセスの三つの方法の中でははるかに,

バッグからセットへ

時にはカーソルが必要なように見えるかもしれません。 単にデータベースデータを行ごとに物理的な順序で繰り返す必要がある場合、カーソルだけが機能することがあります。 これは、行が重複していて、テーブル内の特定の行を一意に識別する方法がない場合に最も一般的に発生します。 これらのテーブルは、セットではなくデータのバッグであり、”バッグ”はセットのように重複した値を排除しないためです。

このようなデータのバッグは、通常、外部ソースからデータをインポートし、データを完全に信頼できない場合に発生します。, たとえば、AdventureWorksトランザクション履歴テーブルにuniqueと呼ぶことができる列のグループがなかったり、行が重複していたりする場合は、カーソルを使用する必要があると考えることがあります。

ただし、行のバッグを正規化されたテーブルに変換することはいつでもできます。 テーブルに重複する行がある場合、または一意性に依存できる列のセットがない場合でも、id列をテーブルに追加し、idをシードして1で番号付けを開始 これにより、テーブルに一意のキーが追加され、カーソルの代わりにWHILEループを使用できます。, 一意のキーを取得したら、Transact-SQLセットベースのUPDATEコマンドを使用して重複を削除できます。

データベースデータへの論理API

セットベース操作を使用することは、少なくとも二つの方法でデータを自分で反復処理するよりも優れています。

まず、sql Serverの高度に最適化されたエンジンを使用して反復を実行しているため、セットベースのSQLコマンドがより効率的です。 自分でデータを反復処理する場合は、SQL Serverストレージエンジンを最適に使用していません。 代わりに、一度に単一の行だけを取得するコマンドを使用しています。, 単一の行を要求するたびに、コマンドがストレージエンジンに到達する前にSQL Serverオプティマイザを通過する必要があり、SQL Serverストレージエンジンの最 自分で繰り返し処理した場合、データを処理するときに、テーブルに関する無関係な物理情報、つまり行の順序にも依存しています。 Set-base Transact-SQLのSELECT、UPDATE、およびDELETEコマンドを使用すると、行の順序を無視して、データの特性に基づいて行に影響を与えるだけで、高速になります。,

第二に、セットベースのコマンドは、セット内のデータについて考えると、データが実際にどのように順序付けされるかに関する余分な詳細から離れてしまうため、より論理的です。 実際、SELECT、UPDATE、DELETEなどのセットベースのコマンドは、カーソルやWHILEループではなくテーブルに直接適用すると、データの順序を無視できるため、論理的にデータに近づ,ストアドプロシージャは、アプリケーションがプログラムでSQL Serverとインターフェイスするための最も自然なAPIであるため、セットベースのSQLコマンドは、リレーショナルデータにアクセスするための適切なAPIです。 保管手続を切り離からのお申込データベース内部は、無効にする必要がありまad hocます。 同様に、Transact-SQL内のset-base SQLコマンドは、リレーショナルデータへの論理インターフェイスを提供し、データの反復処理にSQL Serverストレージエンジンを使用するため、よ,

一番下の行は、データを反復処理することが悪いということではありません。 実際には、しばしばそれは避けられません。 むしろ、重要なのは、ストレージエンジンがそれを実行し、代わりにsetベースのTransact-SQLコマンドの論理インターフェイスに依存させることです。 実際にTransact-SQLカーソルを使用する必要がある状況があれば、ほとんど見つからないと思います。