techrepublicの無料SQL Serverニュースレターには、この強力なリレーショナルデータベース管理システムでより熟達するのに役立つ実践的なヒントが含 自動的に今日購読！

ストアドプロシージャ(またはsprocs)に関する一般的に受け入れられている知恵は、SQLがそれらを最適化してコンパイルできるため、QueryAnalyzerから実行される同等のSQL文(またはWebページやVBprogramなどのフロントエンドアプリから渡される可能性がある)よりも迅速に実行されるということです。 これはある程度真実です。まず、SQL Serverが新しいsprocで何をするのかを知る必要があります。 作成時に、構文をチェックします。 エラーが見つからない場合は、システムテーブルsysobjects、sysdepends、およびsyscommentsにsprocを追加します（後者はsprocの本体を格納します）。 デフォルトでは、作成時にsprocはコンパイルされません。

sprocを最初に実行すると、SQL Serverはsprocを最適化してコンパイルします。 これは、SQL Serverがqueryplanを考案し、そのプロシージャキャッシュに格納するときです。 その後の呼び出しでは、SQLは最初にキャッシュを見て、そこにsprocを見つけ、コンパイルしません。 Sprocがキャッシュにない場合、SQL Serverはそれをコンパイルしてキャッシュに配置します。

WITH RECOMPILEオプションを使用した私の経験

しばらく前に、ユーザーが複数の列のいずれかで検索できる検索ページをサポートしていました。 次に、ページはsprocと呼ばれ、どの列を検索するかを示すパラメータを渡します。 私はCASEブロックを使用してパラメータを調べ、検索する列に応じていくつかのクエリを実行しました。私はallegedlycleverストアドプロシージャをテストし始めたときに何かが間違っていることを知っていました。 理論的には、各検索のパフォーマンスは少なくともほぼ同じでなければなりませんが、それは起こったことではありません。 複数の検索を実行すると、順序に関係なく、最初の検索は高速になり、その後の検索ははるかに遅くなりました。

最後に、プロシージャが初めて呼び出されたときに、クエリプランが考案され、キャッシュに格納されていることに気付きました。 私がその特定の列を検索した限り、すべてがasexpectedで動作します。 しかし、列を切り替えた瞬間、パフォーマンスは急落しました。 なぜこれが起こったのですか？最初に実行した検索では、クエリプランとstoreditがキャッシュに作成されました。 たとえば、OrderDate列を検索していたとします。 検索をCompanyName列に切り替えた場合、SQLはキャッシュされたqueryplanを盲目的に使用し、OrderDateindexを使用してターゲット会社名を検索します。 パフォーマンスが劇的に急落するのも不思議ではありません。修正は非常に簡単です。

これは、既存のクエリプランを破棄して別のクエリプランを構築するようにSQL Serverに指示しますが、これは一度だけです。また、ASキーワードの直前にstoredprocedureに直接WITH RECOMPILEを追加することもできます。 これにより、sql Serverは、sprocの実行ごとにクエリプランを破棄するように指示します。

3番目のオプションもあります。 各検索メソッドに対してaseparate sprocを作成し、CASEブロック内でどのメソッドを実行するかを決定することができました。 このようにして、サブスプロックに関連付けられたクエリプランがキャッシュに残り、SQLがそれらを利用できるようになります。 各スプロックは正確に1つの列を検索するため、再コンパイルする必要はありません。storedprocedureを最適化してコンパイルするSQL Serverの機能は素晴らしいですが、注意しないと、少なくともそれを期待するときに噛むことがあります。 問題に対処する方法がわかったので、おそらくあなたが再訪したいかもしれないあなた自身のデータベースにafew状況があります。