2007-07-09

テーブル変数は遅い?

ASP.NET + SQL Server2005で作成したアプリケーションで特定のストアドプロシージャを呼ぶとかなりの頻度でタイムアウトが発生していました。
タイムアウトに達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。
 
ところが、SQL Server Management Studioで実行すると、数秒で戻ってきます。
Web.configのConnectionStringや TransactionScopeのタイムアウトを設定してみても改善しませんでした。
 
そこで問題のあるストアドプロシージャを調べてみると、どれもテーブル変数に集計結果を保存していることがわかりました。
 
ちなみに落ちているときのプロファイラの結果がこれ
CPU Reads Writes Duration
(1) 29,734 225,167 11 30,005
(2) 29,610 223,208 7 30,005
どうも Durationが30,000を超えると落ちているような気がします。
(どこかに設定するところがあるのでしょうか...)
 
ちなみにテーブル変数の主キーの有無でも変化はありませんでした。
インデックスを張ることもできないので、SQLを全面的に見直した方がよさそうですが、  
面倒くさい新たなバグを呼び込みそうなのでパス
 
ところが試しに集計結果をテーブル変数に格納するのをやめて、使用する毎にサブクエリーで結合するように改めてみたらタイムアウトが出なくなりました。
プロファイラの結果でも明らかにReads, Durationが激減しています。
CPU Reads Writes Duration
(3) 3,954 21,1557 10 1,020
 
本当はテーブル変数じゃなくて一時テーブルにインデックス張ってみたらどうかな...と思ってたのですが、この段階で動くようになったので今日はここまで。
面倒くさい余計なバグを作ったら大変ですしね。
 

2 件のコメント:

匿名 さんのコメント...

クエリプランのキャッシュが原因の気がしますが…
リコンパイルオプションを参考

kazuaki さんのコメント...

こんにちは。
貴重な情報ありがとうございます。

リコンパイルオプション...、この辺りの事でしょうか。

問題のストプロは手を離れてしまって すぐには試せないので、今度同じような事をするときにでも比較してみます。
ありがとうございました。