[解決済み】CTE、サブクエリ、テンポラリテーブル、テーブル変数にパフォーマンスの違いはありますか？

質問

この優れた SO質問 との違い。 CTE と sub-queries が議論されました。

具体的にお聞きしたいのですが。

以下の各項目は、どのような場合に効率的／高速化されるのでしょうか？

• CTE
• サブクエリ
• テンポラリーテーブル
• テーブル変数

従来は、たくさんの temp tables を開発する際に stored procedures - というのは、たくさんのサブクエリが絡み合っているよりも読みやすいと思われるからです。

Non-recursive CTE それとも、最も効率的な解決策を見つけるために、常にさまざまなオプションをいじくり回さなければならない場合でしょうか？

EDIT

最近、効率性という点では、ヒストグラムすなわち統計を関連付けることができるテンポラリテーブルが最初の選択肢として良いと言われました。

どのように解決するのですか？

SQLは宣言型言語であり、手続き型言語ではありません。 つまり、欲しい結果を記述するためにSQL文を作成するのです。 SQLエンジンに どのように を実行する。

原則として、SQLエンジンとSQLオプティマイザに最適なクエリプランを見つけさせるのがよいでしょう。 SQLエンジンの開発には多くの人・年の労力がかかっているので、エンジニアが知っていることをやらせてあげましょう。

もちろん、クエリプランが最適でない状況もあります。 その場合は、クエリヒントを使ったり、クエリを再構築したり、統計情報を更新したり、一時テーブルを使ったり、インデックスを追加したりなどして、パフォーマンスを向上させたいものです。

ご質問の件ですが CTE とサブクエリは、どちらもクエリオプティマイザに同じ情報を提供するため、理論的には同じ性能になるはずです。 1つの違いは、複数回使用されるCTEを簡単に特定し、1回で計算できることです。 そして、その結果を保存し、複数回読み込むことができます。 残念ながら、SQL Serverはこの基本的な最適化方法（これを一般的なサブクエリの削除と呼ぶかもしれません）を利用していないようです。

テンポラリー・テーブルは、クエリの実行方法についてより多くのガイダンスを提供しているので、別の問題です。 大きな違いは、オプティマイザがクエリプランを確立するために、テンポラリーテーブルの統計情報を使用できることです。 その結果、パフォーマンスを向上させることができます。 また、複数回使用される複雑なCTE（サブクエリ）がある場合、それを一時テーブルに格納することで、パフォーマンスが向上することがよくあります。 クエリは一度だけ実行されます。

ご質問の答えは、特に定期的に実行される複雑なクエリでは、期待するパフォーマンスを得るために弄る必要があるということです。 理想的な世界では、クエリオプティマイザは完璧な実行経路を見つけることができます。 しかし、より良いパフォーマンスを得るための方法を見つけることができるかもしれません。