こんにちは！
ROBOT PAYMENT ペイメントシステム課の片岡です。
2024年度に入社し、あっという間に2年目になりました。時間が過ぎるのは本当に早いですね。
今回は、社内の管理サイトにおいて検索速度を改善した取り組みについてご紹介します。
はじめに
弊社では、クレジットカード決済以外にも、以下のような複数の決済サービスを取り扱っています。
- クレジットカード
- 口座振替
- 払込票
- コンビニペーパーレス
- 銀行振込
この中で、社内用の管理画面の「銀行振込」機能の検索速度が特に遅く、日常業務に支障をきたしていました。
具体的には、検索ボタンを押して結果が表示されるまで平均 29.640秒、またページング時にも同程度の 29.640秒を要している状況でした。
前提（環境・制約）
本記事で扱う検索処理の前提は以下のとおりです。
- DB：SQL Server
- 数十万件規模のテーブルと、数百万件規模のテーブルを結合して検索
- セキュリティ・契約上の制約により、詳細なスキーマやインデックス定義は非公開
これらの制約の中で、検索性能の改善に取り組みました。
原因
検索速度が低下していた主な原因は、以下の三点です。
1. 取得するデータ件数を制限していなかった
2. インデックスが有効に使われないクエリを発行していた
3. 適切なインデックスが存在していなかった
1. 取得データ件数の未制限
数十万件規模のテーブル同士を結合し、条件に一致するデータをすべて取得していました。その結果、以下の問題が発生していました。
- アプリケーション側の描画・処理件数の増加
- DBおよびアプリケーションのメモリ圧迫
- DBインスタンスとアプリケーション間のネットワーク負荷増大
2. インデックスが効かないクエリ
検索フォームで未入力の項目についても、すべてWHERE句の条件に含めていました。
例）「名前」は入力されているが、「メールアドレス」は未入力の場合
SELECT * FROM dbo.test_table WITH (NOLOCK) WHERE (@name = '' OR name = @name) AND (@email = '' OR email = @email)
このような条件指定では、インデックスが存在していても最適に利用されず、パフォーマンス低下を招いていました。
3. インデックス不足
検索条件や結合条件に対して適切なインデックスが存在しておらず、全件検索時には特にパフォーマンスが劣化していました。
解決策
1. ページング処理の改善
OFFSET-FETCH を利用し、検索ごとに取得する件数を 100件 に制限しました。これにより、
- アプリケーションの処理負荷
- DBおよびアプリケーションのメモリ使用量
- ネットワーク負荷
を大幅に削減することができました。
なお、ページ番号（OFFSET）が大きくなるほど処理件数が増加し、効率が低下する点は認識していました。TOP と WHERE 句を組み合わせ、主キーで絞り込む設計の方がインデックスシークを効かせやすいケースもあります。
一方で、既存実装や実行コストを比較した結果、本ケースでは OFFSET-FETCH の方が総合的に実行コストが低く、実装影響も抑えられると判断し採用しました。
2. 動的なWHERE句の生成
クエリビルダーを利用し、入力された項目のみをWHERE句に追加する方式へ変更しました。
例）「名前」は入力されているが、「メールアドレス」は未入力の場合
SELECT * FROM dbo.test_table WITH (NOLOCK) WHERE name = @name
これにより、インデックスが適切に利用されるようになりました。
3. 汎用的なインデックスの追加
追加したインデックスは 単一インデックス です。詳細な定義は公開できませんが、
- 結合条件で頻繁に使用されているカラム
を主な対象としてインデックスを作成しました。
今回の検索機能だけでなく、同じテーブルを参照している他機能からも利用されているカラムであったため、結果として汎用的に効果のあるインデックスとなりました。
今回改修した検索機能だけでなく、同じテーブルを参照している他機能でも利用可能な、汎用性の高いインデックスを追加しました。
結果
本改善により、特に 全件検索時の性能 が大きく向上しました。
改修前は、数十万件規模と数百万件規模のテーブルが結合され、WHERE句で十分に絞り込まれない状態で実行されていました。その結果、結合後のすべてのデータをDBおよびアプリケーションの両方で扱う必要があり、最も時間のかかる処理となっていました。
改修前は平均 29.640秒 かかっていた全件検索が、改修後は平均 0.244秒 まで短縮されました。
平均 29.640秒 かかっていた検索処理は、平均 0.244秒 まで短縮されました。約 99.2% の時間削減となり、体感でも大きな改善を実感できる結果となりました。
（パーセント表記にすると、よりインパクトがありますね。）
まとめ
今回の改修を通じて、以下のような点を学ぶことができました。
また、性能改善を目的とした改修ではありましたが、結果として神クラス化していたクエリ処理を解消することができ、保守性・可読性の向上という副次的な効果も得られました。
- アプリケーションからインフラまでの処理経路の重要性
- レガシーコードの安全なリファクタリング方法
- 効果的なインデックス設計の考え方
特に「アプリケーションとインフラの処理経路」については、新たな視点を得ることができました。
近年はマネージドDBサービスの普及により、DB運用の負担は軽減されていますが、クエリ設計やデータ取得方法次第で引き出せるパフォーマンスには大きな差が出ます。
今後も、サービスの特性を理解したうえで、パフォーマンスを意識した開発を心がけていきたいと思います。
We are hiring！！
ROBOT PAYMENTでは一緒に働く仲間を募集しています!!!
