この素晴らしい質問です、CTEとの違いについてsub-queries議論されました。
具体的には次のことをお聞きしたいです。
以下のそれぞれはどのような状況でより効率的/高速になりますか?
- 熱膨張係数
- サブクエリ
- 一時テーブル
- テーブル変数
temp tables従来、私は開発時に を多用してきましたstored procedures。これは、多数の絡み合ったサブクエリよりも読みやすいと思われるためです。
Non-recursive CTEはデータセットを非常にうまくカプセル化し、非常に読みやすいのですが、常にパフォーマンスが向上すると言える特定の状況はあるのでしょうか。それとも、最も効率的なソリューションを見つけるために、常にさまざまなオプションをいじくり回す必要があるのでしょうか。
編集
最近、効率性の観点から、関連するヒストグラム、つまり統計情報があるため、一時テーブルが最初の選択肢として適切であると聞きました。
ベストアンサー1
SQL は宣言型言語であり、手続き型言語ではありません。つまり、SQL ステートメントを作成して、必要な結果を記述します。SQL エンジンに作業の実行方法を指示するわけではありません。
一般的なルールとして、SQL エンジンと SQL オプティマイザーに最善のクエリ プランを見つけさせるのが得策です。SQL エンジンの開発には何年もの労力がかかるため、エンジニアには得意なことをやらせましょう。
もちろん、クエリ プランが最適ではない状況もあります。その場合は、クエリ ヒントの使用、クエリの再構築、統計の更新、一時テーブルの使用、インデックスの追加などを行って、パフォーマンスを向上させる必要があります。
ご質問についてですが、CTE とサブクエリのパフォーマンスは、どちらもクエリ オプティマイザーに同じ情報を提供するため、理論上は同じになるはずです。1 つの違いは、複数回使用される CTE は簡単に識別して 1 回計算できることです。その結果は、複数回保存して読み取ることができます。残念ながら、SQL Server はこの基本的な最適化方法 (これを一般的なサブクエリの削除と呼ぶかもしれません) を活用していないようです。
一時テーブルは別の問題です。クエリの実行方法についてより多くのガイダンスを提供するためです。大きな違いの 1 つは、オプティマイザーが一時テーブルの統計を使用してクエリ プランを確立できることです。これにより、パフォーマンスが向上します。また、複数回使用される複雑な CTE (サブクエリ) がある場合、それを一時テーブルに格納すると、パフォーマンスが向上することがよくあります。クエリは 1 回だけ実行されます。
質問に対する答えは、特に定期的に実行される複雑なクエリの場合、期待するパフォーマンスを得るためには試行錯誤する必要があるということです。理想的な世界では、クエリ オプティマイザーが最適な実行パスを見つけます。多くの場合、最適なパスが見つかりますが、パフォーマンスを向上させる方法を見つけられるかもしれません。