テーブル変数と一時テーブルの選択（ST011、ST012）

人々は、テーブル変数と一時テーブルの相対的なメリットについて多くのことを議論することができます。関数を書くときのように、選択の余地がない場合もあります。しかし、そうすると、両方に用途があり、どちらかが速い例を簡単に見つけることができます。この記事では、どちらかを選択する際の主な要因を説明し、最高のパフォーマンスを得るためのいくつかの簡単な「ルール」を示します。

基本的なエンゲージメントルールに従っていることを前提としています。 、次に、比較的小さなデータセットを操作する場合は、テーブル変数を最初の選択肢として検討する必要があります。一時テーブルを使用する場合と比較して、操作が簡単で、使用されるルーチンでトリガーされる再コンパイルが少なくなります。テーブル変数は、それらを作成したプロセスとバッチに「プライベート」であるため、必要なロックリソースも少なくて済みます。 SQLプロンプトは、この推奨事項をコード分析ルールとして実装します。ST011–一時テーブルの代わりにテーブル変数を使用することを検討してください。

一時データに対してより複雑な処理を行う場合、または適度に少量を超える量を使用する必要がある場合それらのデータの場合、ローカル一時テーブルの方が適している可能性があります。 SQL Code Guardには、彼の推奨事項に基づくコード分析ルールが含まれています。ST012–テーブル変数の代わりに一時テーブルの使用を検討してください。ただし、現在SQLプロンプトには実装されていません。

テーブル変数と一時テーブルの長所と短所

テーブル変数を使用するクエリは非常に非効率的な実行プランになることがあるため、テーブル変数は「悪いプレス」になる傾向があります。ただし、いくつかの簡単なルールに従う場合は、中間の「作業」テーブルや、データセットが小さく、必要な処理が比較的簡単なルーチン間で結果を渡す場合に適しています。

テーブル変数は、主に「メンテナンスがゼロ」であるため、非常に簡単に使用できます。スコープは作成されたバッチまたはルーチンに限定され、実行が完了すると自動的に削除されるため、長期間の接続で使用できます。 tempdbで「リソースホギング」の問題が発生するリスクはありません。テーブル変数がストアドプロシージャで宣言されている場合、そのストアドプロシージャに対してローカルであり、ネストされたプロシージャで参照することはできません。テーブル変数の統計ベースの再コンパイルもありません。 ALTERを使用することはできないため、それらを使用するルーチンは、一時テーブルを使用するルーチンよりも再コンパイルが少なくなる傾向があります。また、完全にログに記録されないため、作成と入力が高速になり、 tに必要なスペースが少なくて済みますトランザクションログ。これらをストアドプロシージャで使用すると、同時実行性が高い条件下で、システムテーブルでの競合が少なくなります。要するに、物事をきちんと整理するのが簡単です。

比較的小さなデータセットを扱う場合、それらは同等の一時テーブルよりも高速です。ただし、行数が約15K行を超えて増加すると、コンテキストによって異なりますが、主に統計のサポートがないために、問題が発生する可能性があります。テーブル変数にPRIMARY KEYおよびUNIQUE制約を適用するインデックスでさえ統計がありません。したがって、オプティマイザーは、テーブル変数から返される1行のハードコードされた推定を使用するため、小さなデータセットの操作に最適な演算子（結合のネストされたループ演算子など）を選択する傾向があります。テーブル変数の行が多いほど、推定と現実の間の不一致が大きくなり、オプティマイザーの計画の選択が非効率的になります。結果として得られる計画は恐ろしい場合があります。

経験豊富な開発者またはDBAは、この種の問題に注意を払い、OPTION

テーブル変数を使用するステートメントへのクエリヒント。テーブル変数を含むバッチを送信すると、オプティマイザーは最初にバッチをコンパイルし、その時点でテーブル変数は空になります。バッチの実行が開始されると、ヒントによってその単一のステートメントのみが再コンパイルされ、その時点でテーブル変数が入力され、オプティマイザーは実際の行数を使用してそのステートメントの新しいプランをコンパイルできます。まれですが、これでも役に立たない場合があります。また、このヒントに過度に依存すると、テーブル変数が一時テーブルよりも再コンパイルが少なくなるという利点がある程度無効になります。

次に、テーブル変数に関する特定のインデックス制限が、処理する際の要因になります。大規模なデータセット。インラインインデックス作成構文を使用して、テーブル変数に非クラスター化インデックスを作成できるようになりましたが、いくつかの制限があり、関連する統計はまだありません。

行数が比較的少ない場合でも、結合であるクエリを実行しようとすると、クエリのパフォーマンスの問題が発生する可能性があり、PRIMARYの定義を忘れます。結合に使用している列に対するKEYまたはUNIQUE制約。提供するメタデータがないと、オプティマイザーはデータの論理的な順序や、結合列のデータに重複する値が含まれているかどうかを認識できず、非効率的な結合操作を選択する可能性があり、クエリが遅くなります。テーブル変数ヒープを使用している場合は、1回の一括処理（テーブルスキャン）で処理される可能性が高い単純なリストのみを使用できます。 OPTION (RECOMPILE)ヒントの両方の使用を組み合わせて、正確なカーディナリティ推定を行う場合と、結合列のキーを組み合わせてオプティマイザを便利にする場合メタデータの場合、データセットが小さい場合は、ローカルの一時テーブルを使用する場合と同等またはそれ以上のクエリ速度を実現できることがよくあります。

行数がテーブル変数のコンフォートゾーンを超えて増加した場合、またはより複雑なデータを実行する必要がある場合処理する場合は、一時テーブルを使用するように切り替えるのが最適です。ここでは、インデックス作成に使用できるすべてのオプションがあり、オプティマイザーには、これらの各インデックスの統計を使用する贅沢があります。もちろん、欠点は、一時テーブルのメンテナンスコストが高くなることです。 tempdbの輻輳を回避するために、自分の後で必ず片付ける必要があります。一時テーブルを変更したり、その中のデータを変更したりすると、親ルーチンの再コンパイルが発生する可能性があります。

多数の削除と挿入が必要な場合は、一時テーブルの方が適しています（行セットの共有）。 ）。これは、一時テーブルのみが切り捨てをサポートするため、データをテーブルから完全に削除する必要がある場合に特に当てはまります。統計の欠如や再コンパイルなど、テーブル変数の設計における妥協点は、データが揮発性である場合にそれらに対抗します。

テーブル変数を使用することで利益が得られる場合

テーブル変数が理想的であり、パフォーマンスが向上する例から始めましょう。アドベンチャーワークスの従業員のリスト、彼らが働いている部門、および彼らが働いているシフトを作成します。小さなデータセット（291行）を処理しています。

結果を次のバッチに渡すかのように、結果を2番目の一時テーブルに配置します。リスト1にコードを示します。

これが私の遅いテストマシンでの典型的な結果です：

一時テーブルの使用は一貫して遅くなりますが、個々の実行はかなり異なる可能性があります。

スケールの問題とキーまたはヒントの提供を忘れる

2つのテーブル変数を結合した場合のパフォーマンスはどうなりますか？試してみましょう。この例では、2つの単純なテーブルが必要です。1つは英語のすべての一般的な単語（CommonWords）で、もう1つはBramStokerの「ドラキュラ」のすべての単語のリストです。 （WordsInDracula）。 TestTVsAndTTsのダウンロードには、これら2つのテーブルを作成し、関連するテキストファイルからそれぞれにデータを入力するためのスクリプトが含まれています。一般的な単語は60,000語ありますが、BramStokerはそのうち10,000語しか使用していません。前者は、一時テーブルを好むようになる損益分岐点のかなり外側にあります。

4つの単純な外部結合クエリを使用して、NULL値、ドラキュラにない一般的な単語、ドラキュラにある一般的な単語、珍しいドラキュラの単語、最後にドラキュラにある一般的な単語を見つけるための別のクエリを検索しますが、反対方向に結合します。テストリグのコードを表示すると、すぐにクエリが表示されます。

以下は、最初のテスト実行の結果です。最初の実行では、両方のテーブル変数に主キーがあり、2番目の実行では、両方ともヒープであり、テーブル変数でインデックスを宣言しないという問題を誇張しているかどうかを確認します。最後に、一時テーブルを使用して同じクエリを実行します。すべてのテストは、説明のために、低速の開発サーバーで意図的に実行されました。本番サーバーでは非常に異なる結果が得られます。

結果は、テーブル変数がヒープの場合、クエリが100ミリ秒ではなく10分間実行されるリスクがあることを示しています。これらは、ルールを知らない場合に体験できる恐ろしいパフォーマンスの素晴らしい例です。ただし、主キーを使用する場合でも、処理する行数は、一時テーブルの使用が2倍高速になることを意味します。

これらの背後にある実行プランの詳細については詳しく説明しません。主な違いのいくつかの大まかな説明を与える以外のパフォーマンスメトリック。一時テーブルクエリの場合、カーディナリティと主キー制約からのメタデータの完全な知識を備えたオプティマイザは、効率的なマージ結合演算子を選択して結合操作を実行します。主キーを持つテーブル変数の場合、オプティマイザは結合列の行の順序を認識しており、重複が含まれていないことを認識していますが、1つの行のみを処理していると想定しているため、代わりにネストされたループの結合を選択します。ここでは、1つのテーブルをスキャンし、返された行ごとに、他のテーブルの個別のシークを実行します。これは、データセットが大きくなるほど効率が低下し、CommonWordsテーブル変数をスキャンする場合は特に悪くなります。これは、テーブル変数。ネストされたループの結合は、CommonWordsの何千ものテーブルスキャンを伴うため、テーブル変数ヒープを使用した2つの10分間のクエリで「ピーク非効率」に達します。興味深いことに、2つの「ドラキュラの一般的な単語」クエリのパフォーマンスははるかに優れています。これは、これら2つについて、オプティマイザが代わりにハッシュ一致結合を選択したためです。

全体として、一時テーブルが最良の選択であるように見えます。 、しかしまだ完了していません！主キーを持つテーブル変数を使用するクエリにOPTION (RECOMPILE)ヒントを追加しましょう。これらのクエリのテストと、一時テーブルを使用した元のクエリを再実行します。当面の間、貧弱なヒープは除外します。

ご覧のとおり、一時テーブルのパフォーマンス上の利点はなくなります。正しい行数と順序付けられた入力により、オプティマイザーははるかに効率的なマージ結合を選択します。

これらの貧弱なヒープにOPTIONを指定すると、どうなるでしょうか。 (RECOMPILE)ヒントもありますか？そうですね、3つのタイミングすべてがはるかに近くなるように、ストーリーが変わります。

興味深いことに、2つの「ドラキュラの一般的な単語」はだったヒープ上でも高速になるようになりました。正しい行数で武装すると、オプティマイザーはその戦略を変更しますが、制約とキーを定義するときに利用できる有用なメタデータがまだないため、悪い選択になります。 CommonWordsヒープをスキャンしてから、「部分集約」を試行し、60K行から数百行に集約されると推定します。重複がないことを認識していないため、実際、それはまったく集約されず、集約とそれに続く結合がtempdbに流出します。

テストリグ

これは最終的な形式のテストリグであることに注意してください。 3つの異なるタイプのテーブルでほぼ同等のパフォーマンスを示しています。元に戻すには、OPTION (RECOMPILE)ヒントを削除する必要があります。

リスト2

結論

テーブル変数の使用について無謀なことは何もありません。それらは、意図された目的に使用されたときに、より良いパフォーマンスを提供し、独自のモップアップを行います。ある時点で、パフォーマンスを向上させる妥協点（再コンパイルのトリガーなし、統計情報の提供なし、ロールバックなし、並列処理なし）が失敗に終わります。

多くの場合、SQLServerの専門家は賢明なアドバイスを提供します。テーブル変数に問題を引き起こす結果のサイズ。この記事で示した結果は、これが問題を単純化しすぎていることを示唆しています。 2つの重要な要素があります。たとえば、1000行を超える結果が得られた場合（この数値はコンテキストによって異なります）、PRIMARY KEYまたはUNIQUEキー。ある時点で、再コンパイルをトリガーして、独自のオーバーヘッドを持つ適切な実行プランを取得する必要もあります。

それでも、特により複雑な処理を実行している場合は、パフォーマンスが大幅に低下する可能性があります。 、オプティマイザはまだ統計にアクセスできないため、クエリ述語の選択性に関する知識がありません。このような場合は、一時テーブルの使用に切り替える必要があります。

参考資料