☰

SQL Server でテーブル変数と一時テーブルはいつ使用すればよいですか? 質問する

tag-icon • tag-icon sql-server temp-tables table-variable
SQL Server でテーブル変数と一時テーブルはいつ使用すればよいですか? 質問する

テーブル変数の詳細を学んでいます。一時テーブルは常にディスク上にあり、テーブル変数はメモリ内にあると書かれています。つまり、テーブル変数は一時テーブルよりも IO 操作が少なくなるため、テーブル変数のパフォーマンスは一時テーブルよりも優れています。

ただし、テーブル変数にメモリに格納できないほど多くのレコードがある場合、テーブル変数は一時テーブルのようにディスク上に置かれることがあります。

しかし、「レコードが多すぎる」とはどういうことかわかりません。100,000 レコードですか? それとも 1000,000 レコードですか? 使用しているテーブル変数がメモリ内にあるかディスク上にあるかを知るにはどうすればよいですか? SQL Server 2005 には、テーブル変数のスケールを測定したり、テーブル変数がメモリからディスクに配置されるタイミングを知らせる機能やツールはありますか?

ベストアンサー1

あなたの質問は、テーブル変数と一時テーブルに関する一般的な誤解のいくつかにあなたが屈していることを示しています。

私は書いたDBAサイトにかなり詳しい回答がある2 つのオブジェクト タイプ間の違いを確認します。これにより、ディスクとメモリに関する質問にも答えることができます (2 つの動作に大きな違いは見られませんでした)。

タイトルの質問に関してですが、テーブル変数とローカル一時テーブルをいつ使用するかについては、必ずしも選択肢があるわけではありません。たとえば、関数ではテーブル変数のみ使用でき、子スコープのテーブルに書き込む必要がある場合はテーブルのみ#temp使用できます（テーブル値パラメータを使用すると、読み取り専用アクセス）。

選択肢がある場合は、以下にいくつかの提案を示します (ただし、最も信頼性の高い方法は、特定のワークロードで両方をテストすることです)。

  1. テーブル変数に作成できないインデックスが必要な場合は、もちろんテーブルが必要になります#temporary。ただし、詳細はバージョンによって異なります。SQL Server 2012 以前では、テーブル変数に作成できるインデックスは、UNIQUEまたはPRIMARY KEY制約によって暗黙的に作成されたインデックスのみでした。SQL Server 2014 では、で使用可能なオプションのサブセットに対してインライン インデックス構文が導入されましたCREATE INDEX。これは、フィルター処理されたインデックス条件を許可するように拡張されました。INCLUDEただし、-d 列または列ストア インデックスを含むインデックスは、テーブル変数に作成することはできません。

  2. テーブルに大量の行を繰り返し追加したり削除したりする場合は、テーブルを使用してください#temporary。これはTRUNCATE（大きなテーブルよりも効率的ですDELETE）をサポートし、さらに、 に続く挿入は、 に続く挿入TRUNCATEよりもパフォーマンスが向上します。DELETE ここで示されているように。

  3. 多数の行を削除または更新する場合、行セット共有を使用できるのであれば、一時テーブルの方がテーブル変数よりもはるかに優れたパフォーマンスを発揮する可能性があります (例については、以下の「行セット共有の影響」を参照してください)。
  4. テーブルを使用した最適なプランがデータに応じて変化する場合は、テーブルを使用します#temporary。テーブルは統計の作成をサポートしており、データに応じてプランを動的に再コンパイルできます（ただし、ストアドプロシージャ内のキャッシュされた一時テーブルの場合）。再コンパイル動作別途理解する必要があります。
  5. テーブルを使用するクエリの最適なプランが変更される可能性が低い場合は、統計の作成と再コンパイルのオーバーヘッドをスキップするためにテーブル変数を検討できます (必要なプランを修正するにはヒントが必要になる可能性があります)。
  6. テーブルに挿入されるデータのソースが潜在的にコストの高いSELECTステートメントからのものである場合、テーブル変数を使用すると並列プランを使用する可能性がブロックされることを考慮してください。
  7. テーブル内のデータが外部ユーザー トランザクションのロールバック後も保持される必要がある場合は、テーブル変数を使用します。これを使用する例としては、長い SQL バッチ内のさまざまなステップの進行状況をログに記録することが考えられます。
  8. ユーザー トランザクション内でテーブルを使用する場合、#tempテーブル変数よりも長くロックを保持できます (ロックの種類と分離レベルに応じて、トランザクションの終了またはステートメントの終了まで保持される可能性があります)。また、tempdbユーザー トランザクションが終了するまでトランザクション ログの切り捨てを防ぐこともできます。そのため、テーブル変数の使用が有利になる可能性があります。
  9. ストアドルーチン内では、テーブル変数と一時テーブルの両方をキャッシュできます。キャッシュされたテーブル変数のメタデータのメンテナンスは、テーブルの場合よりも少なくなります#temporary。Bob Wardは、tempdbプレゼンテーションこれは、同時実行性が高い状況ではシステムテーブルで追加の競合を引き起こす可能性がある。さらに、少量のデータを扱う場合、パフォーマンスに測定可能な違い。

行セット共有の影響

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

おすすめ記事