CTEと温度テーブルの違いは何ですか?


150

Common Table Expression(CTE)と一時テーブルの違いは何ですか?そして、いつ私はもう一方を使うべきですか?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
     SELECT Column1, Column2, Column3
     FROM SomeTable
)

SELECT * FROM cte

温度テーブル

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable
+3

[SQL Serverの一時テーブルとテーブル変数の違いは何ですか?](http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table -variable-in-sql-server) 15 2月. 122012-02-15 17:02:17

+1

SOに関連して:[どれがより高性能なのか、CTEそれとも一時テーブルですか?](http://stackoverflow.com/a/698634/27535) 16 2月. 122012-02-16 07:52:38

178

これはかなり広いですが、私はできる限り一般的な答えをします。

CTE ...

  • 索引付けできません(ただし、参照されているオブジェクトに対して既存の索引を使用できます)。
  • 制約を持つことはできません
  • 基本的に使い捨てですVIEW s
  • 次のクエリが実行されるまでだけ持続する
  • 再帰的にできます
  • 専用の統計を持っていない(基礎となるオブジェクトの統計に頼る)

#テーブルを一時保存...

  • tempdbに存在する実際のマテリアライズ表です。
  • 索引を付けることができます
  • 制約を持つことができます
  • 現在のCONNECTIONの存続期間中持続する
  • 他のクエリまたはサブプロシージャから参照可能
  • エンジンによって生成された専用の統計を持っている

それぞれを使用する場合に限り、それらは非常に異なるユースケースを持っています。非常に大きな結果セットがある場合、またはそれを複数回参照する必要がある場合は、 #temp表に入れてください。再帰的である必要がある場合、使い捨て可能な場合、または論理的に何かを単純化するためだけの場合は、 CTEが推奨されます。

また、 CTEパフォーマンスに決して使われない。CTEを使用しても処理速度が向上することはほとんどありません。これもまた、単なる使い捨てのビューだからです。あなたは彼らといくつかのきちんとしたことをすることができますが、クエリを高速化することは本当にそれらの一つではありません。


23

編集:

下記のMartinのコメントをご覧ください。

CTEはメモリ内のテーブルとして具体化されない。これは単なるクエリ定義のカプセル化方法です。OPの場合はインライン化され、単に実行するのと同じになります。SELECT Column1, Column2, Column3 FROM SomeTable。ほとんどの場合、それらは最初から実体化されません。そのため、これは行を返さないのです。WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.Xまた、実行計画を確認してください。時々スプールを得るために計画をハックすることは可能です。このためのヒントを要求する接続項目があります。- Martin Smith 12年2月15日17:08


元の答え

CTE

Read more on MSDN

CTEはメモリ内で使用されているテーブルを作成しますが、それに続く特定のクエリに対してのみ有効です。再帰を使用するとき、これは効果的な構造体になります。

テーブル変数の使用も検討する必要があるかもしれません。これが使われているとして一時テーブルが使用され、結合ごとに再実体化する必要なしに複数回使用できます。また、今すぐいくつかのレコードを保持する必要がある場合は、次の選択の後にさらにレコードをいくつか追加し、別の操作の後にさらにレコードをいくつか追加し、次にそれらのほんの一握りのレコードを返します。実行後にドロップする必要はありません。ほとんどの場合、構文糖だけです。ただし、行数を少なくすると、ディスクには実体化されません。見るWhat's the difference between a temp table and table variable in SQL Server?詳細については。

温度テーブル

Read more on MSDN - Scroll down about 40% of the way

一時テーブルは文字通りディスク上に作成されたテーブルであり、誰もが知っている特定のデータベースの中で削除することができます。不要になったテーブルを破棄するのは良い開発者の責任ですが、DBAがそれらを拭くこともできます。

一時テーブルには、ローカルテーブルとグローバルテーブルの2種類があります。MS Sql Serverに関しては、#tableName地域の指定##tableNameグローバルの指定(識別特性として単一または二重の#の使用に注意してください)。

一時テーブルでは、テーブル変数やCTEとは対照的に、通常の意味では合法的にテーブルであるため、インデックスなどを適用できます。


一般的に、もっと長い、またはもっと大きいクエリには一時テーブルを使用し、小さいデータセットが既にあり、小さいコード用に少しだけコードをすばやく作成したい場合はCTEまたはテーブル変数を使用します。経験と他の人の助言によると、そこから返される行数が少ない場合にはCTEを使用するべきです。あなたが多数を持っているならば、あなたはおそらく一時テーブルにインデックスを付ける能力から利益を得るでしょう。

+11

CTEはメモリ内のテーブルとして具体化されない。これは単なるクエリ定義のカプセル化方法です。OPの場合、それはインライン化され、 'SELECT Column1、Column2、Column3 FROM SomeTable'を実行するのと同じになります。 15 2月. 122012-02-15 16:55:26

+4

ほとんどの場合、それらは事前に実体化されていません。そのため、これは行を返さないのです。 'WITH T(X)AS(SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X = T2.X'、実行計画も確認してください。時にはそれはスプールを得るために[計画をハックする](http://explainextended.com/2009/05/28/generating-xml-in-subqueries/)することが可能です。[接続項目]があります(https://connect.microsoft.com/SQLServer/feedback/details/218968/provide-a-hint-to-force-intermediate-materialization-of-ctes-or-derived-tables)このためのヒントを要求します。 15 2月. 122012-02-15 17:08:33


12

CTEはクエリ内で繰り返し呼び出され、参照されるたびに評価されます。このプロセスは再帰的な場合があります。一度だけ参照した場合、CTEはパラメータ化できますが、サブクエリのように動作します。

一時テーブルは物理的に永続化されており、インデックスを付けることができます。実際には、クエリオプティマイザはスプール操作のように裏で中間結合またはサブクエリの結果を永続化することもあるので、CTEの結果がディスクに永続化されることは絶対にありません。

IIRCテーブル変数(一方で)は常にインメモリ構造です。

+4

CTEはパラメータ化できますか?どうやって?また、テーブル変数は*常に*インメモリ構造ではありません。関連質問については、Martinの[すばらしい答え](http://dba.stackexchange.com/a/16386/1192)を参照してください。 30 9月. 162016-09-30 06:14:36


10

一時テーブルはtempdbの実際のオブジェクトですが、cteは複雑なクエリを囲む一種のラッパーにすぎません。


12

ここのaccepted answerは「CTEは決してパフォーマンスのために使われるべきではない」と言っています - しかしそれは誤解を招く可能性があります。CTEと一時テーブルの関係では、一時テーブルを使用することにほとんどまたはまったくオーバーヘッドがないと考える人もいたはずなので、一連のストアドプロシージャから大量のジャンクを削除したところです。合法的にプロセスを通して再利用されることになるものを除いて、私はCTEに多くを押し込みました。すべての測定基準で約20%のパフォーマンスを達成しました。次に、再帰処理を実行しようとしていたすべてのカーソルを削除することにしました。これが私が最大の利益を得た場所です。応答時間が10倍短縮されました。

CTEと一時テーブルのユースケースはまったく異なります。万能薬ではないが、CTEを理解し正しく使用することで、コード品質/保守容易性および速度の両面で非常に優れた改善がもたらされることを強調したい。私はそれらを処理したので、一時テーブルとカーソルをSQL処理の大きな悪と見ています。テーブル変数とCTEを使用すれば、ほとんどすべての作業で問題なく使用できます。私のコードはきれいで速いです。

  0

さて、公正にしましょう - カーソルは*偉大な*悪です。一時テーブルは最悪の場合*悪い*悪です。:-)あなたがあなた自身を見たように、それらを同じレベルに置くのは*本当に*不公平です。 22 1月. 182018-01-22 20:50:17

  0

@RDFozzそのとおり、[私たち全員が知っているように9つのサークルがある](https://en.wikipedia.org/wiki/Inferno_(Dante)#Nine_circles_of_Hell)。2番目に一時テーブル、7番目にカーソルを置いてみましょう。;) 22 1月. 182018-01-22 21:52:08


6

CTEを使用する主な理由は、 row_number()などのウィンドウ機能にアクセスすることです。

つまり、グループごとに最初の行または最後の行をmore efficiently than other means in most practical cases迅速かつ効率的に取得することができます - more efficiently than other means in most practical cases

with reallyfastcte as (
select *, 
row_number() over (partition by groupingcolumn order by sortingcolumn) as rownum
from sometable
)
select *
from reallyfastcte
where rownum = 1;

相関副照会または副照会を使用して上記と同様の照会を実行できますが、CTEはほとんどすべてのシナリオで高速になります。

さらに、CTEはコードを単純化するのに役立ちます。クエリをよりよく理解し、オプティマイザをより選択的にするのに役立つより多くのビジネスロジックを導入できるため、これはパフォーマンスの向上につながります。

さらに、CTEのは、あなたのビジネスロジックを理解していれば、パフォーマンスを向上し、クエリの部分が最初に実行されなければならないかを知ることができます-一般的に、参加彼らの次にインデックスを使用して追加することができますセット生じると、最初にそのリードをあなたの最も選択クエリを置くoption(force order)クエリヒント

最後に、CTEはデフォルトではtempdbを使用しないため、それらを使用することでそのボトルネックに対する競合を減らすことができます。

データを複数回クエリする必要がある場合、またはクエリを測定して一時テーブルに挿入し、パフォーマンスが向上したことを示すインデックスを追加することでそれを発見する場合は、一時テーブルを使用する必要があります。

  0

すべての良い点... +1 30 1月. 182018-01-30 13:05:25


5

ここではCTEに向けて少し否定的なことがあるようです。

CTEについての私の理解は、それが基本的に一種の特別な見方であるということです。SQLは宣言型でもセットベースの言語でもあります。CTEは集合を宣言する素晴らしい方法です。あなたがする必要はないので、CTEを索引付けできないことは実際には良いことです。クエリを読み書きしやすくするのは、本当に一種の構文糖です。まともなオプティマイザは、基礎となるテーブルのインデックスを使用して最適なアクセスプランを作成します。これは、基礎となるテーブルのインデックスアドバイスに従って、CTEクエリを効果的にスピードアップできることを意味します。

また、セットをCTEとして定義したからといって、セット内のすべての行を処理する必要があるわけではありません。クエリに応じて、オプティマイザはクエリを満たすために「ちょうど十分な」行を処理する場合があります。たぶんあなたはあなたのスクリーンのために最初の20かそこらだけを必要としました。一時テーブルを作成した場合は、実際にこれらすべての行を読み書きする必要があります。

これに基づいて、CTEはSQLの優れた機能であり、クエリを読みやすくする場所であればどこでも使用できると思います。私は本当にすべての単一レコードを処理する必要があるだろうバッチプロセスのための一時テーブルについて考えるだけでしょう。それでも、一時テーブルではデータベースがキャッシングとインデックスを手助けするのがはるかに難しいため、実際にはお勧めできません。トランザクションに固有のPKフィールドを持つ永続テーブルを用意することをお勧めします。

私の経験は主にDB2に関するものであることを認めなければならないので、CTEは両方の製品で同様に機能すると想定しています。CTEがどういうわけかSQLサーバで劣っていれば私は喜んで直立します。;)