どちらがよりパフォーマンスに優れていますCTE
かTemporary Tables
?
ベストアンサー1
場合によります。
初めに
共通テーブル式とは何ですか?
(非再帰的) CTE は、SQL Server でインライン テーブル式としても使用できる他の構成要素と非常によく似た方法で処理されます。派生テーブル、ビュー、およびインライン テーブル値関数。BOL では CTE は「一時的な結果セットとして考えることができる」とされていますが、これは純粋に論理的な説明です。ほとんどの場合、それ自体は実体化されません。
一時テーブルとは何ですか?
これは、tempdb のデータ ページに格納されている行のコレクションです。データ ページは、メモリ内に部分的または完全に保存される場合があります。また、一時テーブルにはインデックスが付けられ、列の統計情報が含まれる場合があります。
テストデータ
CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);
INSERT INTO T(B)
SELECT TOP (1000000) 0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
master..spt_values v2;
例1
WITH CTE1 AS
(
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780
上記のプランではCTE1について言及されていないことに注意してください。これはベーステーブルに直接アクセスし、
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
WHERE A = 780
ここで CTE を中間一時テーブルに具体化して書き直すと、非常に逆効果になります。
CTE定義の具体化
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
約 8 GB のデータを一時テーブルにコピーする必要があり、そこから選択するオーバーヘッドもまだあります。
例2
WITH CTE2
AS (SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0)
SELECT *
FROM CTE2 T1
CROSS APPLY (SELECT TOP (1) *
FROM CTE2 T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA
上記の例は私のマシンでは約 4 分かかります。
ランダムに生成された 1,000,000 個の値のうち述語に一致するのは 15 行だけですが、これらを見つけるためにコストのかかるテーブル スキャンが 16 回実行されます。
これは中間結果を実現するのに適した候補です。同等の一時テーブルの書き換えには 25 秒かかりました。
INSERT INTO #T
SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0
SELECT *
FROM #T T1
CROSS APPLY (SELECT TOP (1) *
FROM #T T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA
クエリの一部を一時テーブルに中間的にマテリアライズすることは、一度しか評価されない場合でも、マテリアライズされた結果の統計情報を利用してクエリの残りの部分を再コンパイルできる場合に役立つことがあります。このアプローチの例は、SQL Catの記事にあります。複雑なクエリを分割するタイミング。
状況によっては、SQL Server はスプールを使用して中間結果 (CTE など) をキャッシュし、そのサブツリーを再評価する必要がなくなります。これについては、(移行された) Connect 項目で説明します。CTE または派生テーブルの中間実体化を強制するためのヒントを提供しますただし、これに関する統計は作成されず、スプールされた行数が推定と大きく異なる場合でも、進行中の実行プランがそれに応じて動的に適応することはできません (少なくとも現在のバージョンでは。将来的には適応クエリ プランが可能になる可能性があります)。