索引の死角:DAO `FindFirst` におけるワイルドカード検索の罠と、アーキテクチャによる回避策
業務システムの現場において、ある日突然「動作が重くなった」という報告を受けることがある。開発時には数千件のデータで問題なく動作していたシステムが、数万、数十万件のデータへと成長した途端、検索処理が致命的な遅延を引き起こす。
多くの初級・中級エンジニアは、この問題を「PCのスペック不足」や「ネットワークの遅延」に帰結させようとする。だが、真のアーキテクトは知っている。これはハードウェアの問題ではない。B-Treeインデックスの仕組みを無視し、検索エンジンの計算量を爆発させた、コード設計の敗北であるということを。
本稿では、Access VBAにおけるDAO(Data Access Objects)の `FindFirst` メソッドを用いたワイルドカード検索に潜む「インデックス無効化」のメカニズムを解剖し、極限のパフォーマンスを引き出すための実装論を提示する。
1. なぜ `Like “*keyword*”` は遅いのか:Sargabilityの欠如
DAOの `Recordset.FindFirst` メソッドは、非常に強力だが、使い方を誤れば最悪のパフォーマンスを招く。
最大の罠は、`Like` 演算子における前方一致ではないワイルドカード(Leading Wildcard)の使用だ。
インデックスの構造と検索アルゴリズム
Access(Jet/ACEエンジン)が利用するインデックスは、基本的にB-Tree構造をとっている。これは、データが特定のキーに基づいてソートされた状態を保持していることを意味する。
- `Like “ABC*”` (前方一致): インデックスの「ABC」で始まるセクションをバイナリサーチ(二分探索)で高速に特定できる。これを SARGable (Search ARGument ABLE) なクエリと呼ぶ。
- `Like “*ABC*”` (中間・後方一致): インデックスのソート順が意味をなさない。エンジンはインデックスを辿ることができず、全レコードを一つずつメモリに読み込み、パターンマッチングを行うフルテーブルスキャン(Full Table Scan)を強制される。
データ件数が $N$ のとき、インデックスが効いていれば計算量は $O(\log N)$ で済むが、前方ワイルドカードを使用すると $O(N)$ に跳ね上がる。この差が、数秒の差、あるいはシステムのフリーズを生む。
2. 検索戦略の再構築:`FindFirst` vs `Filter` プロパティ
設計者は、検索の目的に応じて「カーソル移動」と「レコードセットの再構成」を使い分ける必要がある。
`FindFirst` メソッド
`FindFirst` は、既存のレコードセット内を走査して特定のレコードにカーソルを移動させる操作である。
- 利点: レコードセットを再構築しないため、メモリ消費が比較的抑えられる。
- 欠点: すでにメモリ上に展開された(またはカーソルが移動可能な)範囲内での走査となる。SQLの `WHERE` 句で絞り込んだ後のレコードセットに対して実行する場合、その絞り込み自体が非効率であれば、`FindFirst` も無力となる。
`Filter` プロパティ
`Recordset.Filter` は、条件に合致するレコードのみを抽出した「新しいビュー」を生成する。
- 利点: フィルタリング後のレコードセットに対して、インデックスが効くような条件(前方一致)を与えれば、表示領域を劇的に絞り込める。
- 欠点: フィルタを適用するたびに、内部的に新しいレコードセットのコンテキストが構築されるため、頻繁な切り替えはオブジェクトのライフサイクル管理に負荷をかける。
3. 実践的実装:インデックスを殺さない検索コード
以下に、パフォーマンスを最大化するための実装パターンを示す。ここでは、単なる検索だけでなく、メモリリークを防ぐためのオブジェクト解放までを網羅する。
Option Compare Database
Option Explicit
' =================================================================
' 目的: 高速なインデックス利用を前提としたレコード検索
' 設計思想:
' 1. 前方一致(SARGable)を優先する
' 2. DAOオブジェクトの明示的解放によるメモリ最適化
' 3. エラーハンドリングによるトランザクションの整合性維持
' =================================================================
Public Sub OptimizedSearch(ByVal strSearchTerm As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim strCriteria As String
' 検索項目の正規化(不要な空白の除去)
strSearchTerm = Trim(strSearchTerm)
If strSearchTerm = "" Then Exit Sub
On Error GoTo ErrorHandler
Set db = CurrentDb
' 【極限の知見】
' FindFirstで回す前に、SQLレベルで可能な限り絞り込む。
' ここでは「前方一致」を前提としたSQLを構成する。
' もし中間一致が必須なら、設計自体を見直し、検索用インデックステーブルを別途作成すべきである。
strSql = "SELECT ID, CustomerName, OrderDate FROM T_Orders WHERE CustomerName Like '" & strSearchTerm & "*'"
' レコードセットのオープン(インデックスが効くSQLを使用)
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot) ' 読み取り専用ならSnapshotが最速
If Not rs.EOF Then
' 検索結果が見つかった場合の処理
Debug.Print "Found: " & rs!CustomerName
' 必要に応じて、さらに詳細な条件でFindFirstを実行
' ただし、既にSQLで絞り込んでいるため、ここでのFindFirstは極めて高速
rs.FindFirst "OrderDate > #2023/01/01#"
Else
Debug.Print "No record matching the criteria."
End If
CleanUp:
' 【重要】オブジェクトのライフサイクル管理
' メモリリークとファイルロックを防ぐため、必ず明示的にNothingを代入する
' 特にループ内でRecordsetを生成する場合、これを怠るとメモリ使用量が指数関数的に増大する
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If
Exit Sub
ErrorHandler:
' ログ出力やエラー通知(実務ではログファイルへの書き出しを推奨)
MsgBox "Critical Error in OptimizedSearch: " & Err.Description, vbCritical
Resume CleanUp
End Sub
4. アーキテクトの視点:さらなる高みへ
もし、ビジネス要件として「どうしても中間一致(`*keyword*`)が必要である」という場合は、VBAの書き方で解決しようとしてはいけない。それは根本的な解決ではなく、技術的負債の積み増しに過ぎない。
その場合、以下のアーキテクチャを選択せよ。
1. 検索用トークンテーブルの構築:
検索対象の文字列を単語単位に分解し、その単語とレコードIDを紐付けた「検索用インデックステーブル」を別途作成する。検索時はそのテーブルを `JOIN` する。これにより、中間一致を前方一致の結合へと変換できる。
2. SQL Server / Azure SQL Database への移行:
Accessのエンジン(ACE)には限界がある。フルテキスト検索(Full-Text Search)機能を持つRDBMSへ移行し、VBAからはADO経由でその機能を呼び出す。
3. メモリ最適化の徹底:
大量のレコードを扱う場合、`dbOpenDynaset` ではなく `dbOpenSnapshot` を使用せよ。変更を加えない検索において、スナップショットはメモリ消費を抑え、読み取り速度を最大化する。
結論
`FindFirst` の遅延は、多くの場合、エンジニアがインデックスの「向き」を理解していないことから生じる。
`*` を左側に置くことは、インデックスという武器を捨てて、素手で戦いに挑むに等しい。
コードを書くときは常に自問せよ。
「この検索条件は、インデックスの構造を尊重しているか?」
この問いに答えられない者は、真のシニアエンジニアとは呼べない。
