April 8, 2010.

検索クエリの高速化

手がけているシステムの一つは７〜８年前に作成されたパッケージを元に作られたもので、いろいろと問題が多い。ある画面の機能変更を行っている最中に、あまりに検索が遅いのでパフォーマンスを試験してみた。なんと、条件なしの１日分のデータの検索に50秒以上もかかった。50万件程度のテーブル中、一日のデータは数千行。それに対し、同じぐらいのデータ量のテーブルを何テーブルも複雑に結合するSQLが発行されているのだが、それにしても遅すぎる。

で、SQLのデザインがおかしいのではと考え、試行錯誤で同じ結果が得られるSQLを作成。一気に高速化して調子にのっていたのだが、検索条件の設定次第で一気にスピードダウンしてしまった。どうやら、インデックスの設計に問題がありそうだと言うことで、webを検索。MSのサイトで調査方法を発見。その結果、一番足を引っ張っているのはあるひとつの項目だった。その項目はPKに含まれているため、既にインデックス対象であり、新規にインデックスを貼ることは想定していなかったのだが、試しにその項目一つだけのインデックスを作成したところ、めちゃくちゃ高速になった。53秒⇒1秒未満という凄まじさだ。

当たり前といえば当たり前なのだが、インデックスと検索条件が１対１でないとあまり効果がない。例えば、「性別・年齢・苗字・名前」という順で主キー（＝自動的にインデックスとなる）が貼られていた場合、その４つを指定して検索すると非常に速い。性別・年齢で検索してもかなり速い。しかし、名前だけで検索すると非常に遅い。今回の例はそれに近かった。

こういう場合、主キーとは別に名前だけでインデックスを作ると検索が早くなる。そのかわり、INSERT時にインデックス構築のオーバーヘッドが発生するため、大量にデータをinsertする場合、多少パフォーマンスが落ちる。

ところで、今回話題にあげているシステムにおいて、一番問題だったのが挿入・更新トリガー。挿入・更新のタイミングで走るのだが、そのトリガーの処理が異常に重たく、その結果、あるバッチ処理に場合によっては30分程度かかってしまう。夜間バッチではなく昼間の作業であるにもかかわらず、だ。で、一度トリガーをやめる見積りを書いたことがある。トリガーで更新する項目をプログラムでやるようにした方が早いからだ。その結果、xxx万円の見積りとなった。

ところで、このトリガーが重い原因は更新対象の検索が遅いため。で、同じ要領でボトルネックを探し、indexを貼りまくった結果....なんと、サーバで16分かかる処理が、baristaの開発PCで4分半で終わってしまった。そんなわけで、xxx万円の作業の90%ぐらいの性能を一日で実現してしまったことになる。

今年はこういう「地味だけど効果的なこと」をしっかり実現していきたいと思う。

ちなみに調査方法はこんな感じ。負担になっているSQLクエリ一覧を取得するSQLクエリを実行。

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

これでパフォーマンスワースト10のクエリのコストや実行回数などが一覧ででてくる。で、得られたgroup_handleを指定して以下のSQLクエリを実行する。

SELECT migs.group_handle, mid.*

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details AS mid

ON (mig.index_handle = mid.index_handle)

WHERE migs.group_handle = 24;

今度はどの項目を検索キーに使っているせいで遅いのかが出てくる。これをみてインデックスの最適化を図れば良い。実に御手軽だけど、webには余り情報が転がってないので、あえてここに転記しておこうと思う。

検索キー：

SQLServer クエリ 高速化 遅い 調査方法 SQL

Copyright 2010 barista. All rights reserved.