2007年07月09日
テーブル変数は遅い？
ASP.NET + SQL Server2005で作成したアプリケーションで特定のストアドプロシージャを呼ぶとかなりの頻度でタイムアウトが発生していました。
ところが、SQL Server Management Studioで実行すると、数秒で戻ってきます。
Web.configのConnectionStringや TransactionScopeのタイムアウトを設定してみても改善しませんでした。
そこで問題のあるストアドプロシージャを調べてみると、どれもテーブル変数に集計結果を保存していることがわかりました。
ちなみに落ちているときのプロファイラの結果がこれ
タイムアウトに達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。
ところが、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が激減しています。
本当はテーブル変数じゃなくて一時テーブルにインデックス張ってみたらどうかな...と思ってたのですが、この段階で動くようになったので今日はここまで。
(削除) 面倒くさい (削除ここまで)余計なバグを作ったら大変ですしね。
(どこかに設定するところがあるのでしょうか...)
ちなみにテーブル変数の主キーの有無でも変化はありませんでした。
インデックスを張ることもできないので、SQLを全面的に見直した方がよさそうですが、
ところが試しに集計結果をテーブル変数に格納するのをやめて、使用する毎にサブクエリーで結合するように改めてみたらタイムアウトが出なくなりました。
プロファイラの結果でも明らかにReads, Durationが激減しています。
CPU
Reads
Writes
Duration
(3)
3,954
21,1557
10
1,020
本当はテーブル変数じゃなくて一時テーブルにインデックス張ってみたらどうかな...と思ってたのですが、この段階で動くようになったので今日はここまで。
登録:
コメントの投稿 (Atom)
2 件のコメント:
クエリプランのキャッシュが原因の気がしますが…
リコンパイルオプションを参考
こんにちは。
貴重な情報ありがとうございます。
リコンパイルオプション...、この辺りの事でしょうか。
問題のストプロは手を離れてしまって すぐには試せないので、今度同じような事をするときにでも比較してみます。
ありがとうございました。
コメントを投稿