SQL Server2008 R2でクエリータイムアウトが発生

Question

###現象
主キーとインデックス(7個)を設定しています。

DBのデータは、週に１回20万件程度の登録を行って、
１年程度稼動しています。(1200万件程度)

Excelマクロで、ADODBを使って、１日１回データ集計を行っていますが、
週に１回程度、クエリータイムアウトが発生します。

エラーが発生するのは、Excelマクロが起動して最初に発行する
Select文でエラーとなっています。
クエリーに問題があるように見えず、対処方法に悩んでます。

###環境
OS:Windows Server 2008
DB:SQL Server 2008 R2
Excel:Excel2010

###エラーとなるソースコード
SELECT MAX(日付のカラム) FROM CHIPREPLY
WHERE DATATYPE='データタイプ' AND PERIOD = '年月' AND POINT = 'データ登録日'

adors.Open sql, adoCon, adOpenForwardOnly

###試したこと
クエリータイムアウトを180秒に設定することで一時改善できましたが、
レコード件数が増えているせいか、現状は週１回程度現象が発生しています。

Answer 1

実際に実行計画を見ないと確証はありませんが、
こちらのQAによるとMAXもTOPもパフォーマンス的差異はないか、
むしろインデックスが定義されていないケースではTOPの方が実行速度が落ちる可能性もあります。
比較検証しているサイトがあったので参考までに。

ですので根本的に改善すべきはインデックスの設定となりそうです。
(質問でおっしゃっている７つ設定したインデックスの中に重複があればすみません・・・)

提示していただいているSQLのみに焦点を当てると、

• DATATYPE、PERIOD、POINTに対しての複合インデックスを付与
• 日付のカラムに対してもインデックスを付与

この二つが可能ならば上記を実施するだけでパフォーマンス的にかなり有利となるように思います。

特にMAX、MINなどの極値を得る集計関数は、
インデックスを定義しているとそれをそのまま利用してくれるため、
かなりコストを抑えることが可能です。
(インデックス自体が基本的にソート済のため)

以下は蛇足となります。
カラム数が多いテーブルならまだ良いのですが、
カラムはそこそこしかなくインデックスの数だけ増えてくると、
逆にパフォーマンスを損なうことになりますので注意して下さいね。

Comments

[yas0703]

Panzer_vorさん、ありがとうございます。

社内と顧客の環境でTOPにかえたクエリーを投げたところ
応答速度はかなり早かったです。
明日から顧客の環境で、変更したモジュールで様子見となりました。

インデックスのつけ方も良くないかもしれないですね。
インデックスの見直しも含めて、再度見てみたいと思います。

[Panzer_vor]

> yas0703さん
なるほど、通説のように遅くなるという訳でもないのですね。
バージョンが新しくなって最適化とかも行われてるのかもしれませんね、勉強になりました。

やはり究極的に一番信用できるのは実行計画見ることしかないということですかね＾＾；

[yas0703]

Panzer_vorさん、Topでやっても状況は変わらずでした。
あとは、インデックスの見直ししかないかと思います。
まだ、解決したわけではありませんが、ベストアンサーとさせていただきます。
ありがとうございました。

[Panzer_vor]

> yas0703さん
なるほど、状況は分かりました。

最終的には実行計画を見ながら判断が必要となると思いますので以下のコメントはあくまで目安として下さい。

インデックスの見直す上で下記の観点でそれぞれ確認してみてください。

①WHERE句そのままでSELECT * でレコード取得し計測
②WHERE句を外して日付カラムのMAXのみ取得し計測

①の時点で時間がかかるなら複合インデックスの検討を、
②で時間がかかるなら日付カラムへのインデックス付与がそれぞれ効果が大きいと思われます。

Answer 2

MAX関数を使うとレコード数に比例して処理速度が遅くなってしまうので、TOPを使ってみてはどうでしょうか？
やっていることは同じことで、日付のカラムの降順でソートかけて、最初の1レコードだけを取得しています。

SELECT TOP 1 日付のカラム FROM CHIPREPLY  
WHERE DATATYPE='データタイプ' AND PERIOD = '年月' AND POINT = 'データ登録日'
ORDER BY 日付のカラム DESC

Comments

[yas0703]

ijufumiさん、ありがとうございます。

変更してみて、様子見てみたいと思います。

[yas0703]

現地環境で確認して頂きましたが、TOPに変更しても状況変わらずでした。
インデックスの見直しが先決のようです。