SE の雑記

SQL Server のカーソルの動作について、理解が薄い個所がありましたので、調べた内容をまとめておこうと思います。

カーソルについては次の情報を参考にするのが最初のステップとなるかと思います。

• SQL Server カーソル
• DECLARE CURSOR (Transact-SQL)
• @@FETCH_STATUS (Transact-SQL)

DMF を使用したカーソルの動作モードをの確認

最初に重要となるのが「自分が実行したカーソルのクエリがどのような動作モードで動作しているか」を正しく把握することです。

SQL Server のカーソルは次のような構文で実行することができます。

• LOCAL / GLOBAL
• FORWARD_ONLY / SCROLL
• STATIC / KEYSET / DYNAMIC / FAST_FORWARD

これらのオプションを組み合わせてカーソルを定義するのが一般的な方法となるかと思います。

カーソルがどのような設定で動作しているかについては、sys.dm_exec_cursors で確認することができ、カーソルを使用しているセッションとは別のクエリとして、上記の DMF をカーソルを使用している Session ID を指定して実行することで、どのような設定でカーソルが使用されているのかを確認することができます。

オプション無しで使用した場合の先入観をなくす

カーソルをオプション無しで使用する場合には次のようなクエリとなるかと思います。

DECLARE @n_nationkey int, @n_comment varchar(152);

DECLARE cur_NATION CURSOR 
FOR SELECT TOP 4 N_NATIONKEY, N_COMMENT FROM NATION2 ORDER BY N_NATIONKEY ASC;

OPEN cur_NATION;

FETCH NEXT FROM cur_NATION INTO @n_nationkey, @n_comment;

WHILE @@FETCH_STATUS = 0
BEGIN
	-- カーソルを使用した処理
END

CLOSE cur_NATION;
DEALLOCATE cur_NATION;

このクエリを実行した場合、どのようなオプションでカーソルが実行されるでしょうか？

カーソルのオプションについては、テーブルの状態によって異なります。

上がプライマリキーがある状態 / 下がヒープの状態のテーブルに対してカーソルを使用したクエリとなりますが、使用されているオプションが異なっていることが確認できます。

プライマリキーがある場合は、Keyset / Optimistic のカーソルが使用されており、ヒープの状態では Snapshot / Read Only が使用されています。

オプションを指定することで、どのようなカーソルが使用されるか制御が可能となりますが、オプションを指定しない場合には、どのようなカーソルが使用されるかは、テーブルの定義により変わってきますので、オプションを指定しなかった場合は、どのような設定でカーソルが動作するのかは一度確認してみるとよいのではないでしょうか？

実行プランを確認する

カーソルの実行プランについては、次の 2 つのステートメントで確認をすることになるかと思います。

• OPEN
• FETCH NEXT FROM

基本的にはカーソルを使用する場合には、OPEN したタイミングで「tempdb」に「CWT_PrimaryKey」というテーブルを作成し、操作対象のデータを判別するための情報が格納されます。

Snapshot の場合は対象となるデータのコピーが格納されますが、Keyset の場合は、レコードを判別するためのキー情報のみが格納されるという認識でいます。

FETCH NEXT FROM で処理対象となる行が取得されますが、Keyset と Snashot では、処理が異なり、tempdb / 実データ参照のオーバーヘッドが変わってきます。

Keyset を使用したカーソルの場合

Keyset が使用されたカーソルの場合、「CWT_PrimaryKey」には、レコードを一意に識別するための情報が格納されています。

データの実体については、ベースのテーブルを参照して情報を取得する必要があるためFETCH をしたタイミングで実テーブルに対してのデータ参照が発生し、現在のデータの参照が行われることになります。

実テーブルに対しての参照が行われますので、キー項目が変更 / レコードがされ、OPEN したタイミングでは存在していたが、FETCH するタイミングで存在していないデータができた場合には「@@FETCHSTATUS = -2」となります。

Snapshot を使用したカーソルの場合

Snapshot が使用されたカーソルの場合、「CWT_PrimaryKey」には、OPEN したタイミングのデータのスナップショットが格納されます。

そのため、Keyset とは異なり、FETCH 時の処理としては実データではなくスナップショット (CWT_PrimaryKey) のデータが参照されます。

スナップショットが使用されている場合は、OPEN～FETCH までのデータ変更の影響を受けないため、スナップショット生成時のデータを確認することができます。

まとめ

基本的にはカーソルの使用は最小限にし、結果セットベースでの処理を検討するのが良いのですが、T-SQL 側でロジックを実装しなくてはいけない場合はカーソルを使用した行ベースの処理が必要となります。

カーソルを使用した場合、自分の使用したいデータに対してどのようなカーソルが使用されるのかは意識しておくとよいのではないでしょうか。