背景
SQLserverでとあるスカラ関数を作成して性能検証していたところ実行に思いのほか時間がかかった
関数では再帰クエリを使用しており、パフォーマンス改善を実施した
関数の概要
- 引数1（一般職ユーザー）が引数2（管理職ユーザー）の部下であるか確認するための関数
- ユーザーは1つ以上の組織に所属している
- 組織は階層情報を持ち、各組織データに親組織データを持つ
- 引数2のユーザーの組織に所属するユーザーとさらにその下部組織に所属するユーザーをWITH句を使って再帰的に取得し、そのユーザーの中に引数1が含まれることを確認する
例えば下のような組織の場合、下記のような結果になる
【TRUE】
引数1：捜査第一課のCさん
引数2：警視庁直下のAさん
【FALSE】
引数1：刑事部直下のBさん
引数2：捜査第一課のDさん
課題
管理職ユーザーを起点に下部組織のすべてのユーザーを取得し、その中に指定したユーザーがいるかを確認していたため、下部組織が多くなるほど取得するユーザー件数が多くなり処理時間が長くなっていた
例えば1組織10名のユーザーがいた場合、警視庁直属のAさんの場合40人のユーザを読み込む必要があった
やったこと
解決策①
管理職ユーザーを起点にするのではなく、一般職ユーザーを起点に検索する方法に変更する
所属組織の上位の組織のみを検索よいため、捜査第一課のCさんの場合、30人のユーザーを読み込めばよくなる
分かりやすく示すため例として記載した組織数は少ないが、実際に運用されている組織は1組織10以上の下部組織を持っているため読み込むユーザー数の違いが大きくなることが見込まれる
再帰クエリ自体はなくならないため、性能改善は限定的である可能性がある
解決策②
あらかじめ再帰部分をビューもしくはテーブルに保存することで、性能改善のボトルネックとなっている再帰クエリ自体を利用しないようにする
今回の場合は組織階層をテーブルやビューで保持しておくようにすることで再帰クエリのコストがなくなるため性能の向上が見込まれる
再帰クエリを実行しない分、①より大幅な性能改善が見込めるもののテーブルやビューの設計が必要になる
解決策③
適切なインデックスを作成する
クエリ実行計画を確認し、インデックスが適切に利用されるよう修正する
まとめ
クエリの性能改善ではインデックスやテーブルのチューニングにフォーカスしがちだったが、クエリそのものの見直しも有効である