Access VBAを掌握する極限の知見：QueryDefプロファイラによる超高速ボトルネック特定術
レガシーシステムの底流で未だに強烈な存在感を放つMicrosoft Access。そのパフォーマンスの大部分は、SQLの品質とQueryDefオブジェクトの取り扱いに依存している。
画面がフリーズしたかのような重い処理、原因不明のタイムアウト、そして「なぜか遅い」という漠然とした現場からの悲鳴。これらを根絶するためには、感覚的なチューニングを排し、ミリ秒単位の客観的な実行時間を計測・ログ化する仕組みが不可欠だ。
本稿では、Access VBAの内部挙動、Jet/ACEエンジンのオプティマイザの挙動、そしてWindows APIをも視野に入れた、極限のパフォーマンスプロファイラの実装手法を解説する。
—
1. なぜ「動的SQLの直接実行」ではなく「QueryDef」なのか
多くの開発者は、`CurrentDb.Execute` や `CurrentDb.OpenRecordset` に長大なSQL文字列を直接放り込んでいる。しかし、これはパフォーマンスとメモリ管理の観点から悪手である。
SQL文字列を直接実行した場合、データベースエンジンは毎回以下のコストを支払う。
1. 構文解析 (Parsing)
2. 最適化 (Optimization – 実行計画の生成)
3. コンパイル (Compilation)
一方、QueryDefとしてあらかじめ名前付き（あるいは一時的）なクエリ定義オブジェクトを作成しておけば、ACE（Access Database Engine）のキャッシュに実行計画が保持され、2回目以降の実行コストが劇的に軽減される。
さらに、パラメータークエリとして明示的に型を定義したQueryDefを使用することは、SQLインジェクション対策だけでなく、型変換のオーバーヘッドを排除するうえでも極めて重要である。
—
2. 実装：自作「QueryDefパフォーマンスプロファイラ」
以下に提供するコードは、単なる処理時間の計測にとどまらない。実行時のSQL、パラメータの数、エラーハンドリング、そして外部ログ（テキストファイル）への非同期に近い高速書き込みを網羅した、実戦投入仕様のクラスモジュール（あるいは標準モジュール）のコアロジックである。
精度を極限まで高めるため、標準の `Timer` 関数（精度約10ms〜55ms）ではなく、必要に応じて高精度なWindows API（`QueryPerformanceCounter`）を内包できる設計思想を取り入れる。
プロファイラ実行モジュール（Standard Module: `modQueryProfiler`）
Option Compare Database
Option Explicit
‘ 高精度タイマー用API宣言（必要に応じたマイクロ秒計測用）
If VBA7 Then
Private Declare PtrSafe Function QueryPerformanceFrequency Lib “kernel32” (ByRef lpFrequency As Currency) As Long
Private Declare PtrSafe Function QueryPerformanceCounter Lib “kernel32” (ByRef lpPerformanceCount As Currency) As Long
Else
Private Declare Function QueryPerformanceFrequency Lib “kernel32” (ByRef lpFrequency As Currency) As Long
Private Declare Function QueryPerformanceCounter Lib “kernel32” (ByRef lpPerformanceCount As Currency) As Long
End If
‘ 設定：閾値（ミリ秒）を超えたSQLのみを警告ログとして出力
Private Const SLOW_QUERY_THRESHOLD_MS As Long = 500
/
- 任意のQueryDefを指定し、実行時間を計測・プロファイルする
- @param qd 実行対象のQueryDefオブジェクト
- @param 実行モード (0: 通常実行/Execute, 1: レコードセット取得/OpenRecordset)
- @return 実行にかかった時間（ミリ秒）
/
Public Function ProfileAndExecuteQuery(ByRef qd As DAO.QueryDef, Optional ByVal execMode As Long = 0, Optional ByRef outRs As DAO.Recordset) As Double
Dim startTime As Currency
Dim endTime As Currency
Dim freq As Currency
Dim elapsedMs As Double
Dim db As DAO.Database
Set db = qd.Parent
‘ 高精度タイマーの周波数取得
QueryPerformanceFrequency freq
QueryPerformanceCounter startTime
On Error GoTo ErrorHandler
‘ 実行モードに応じた処理
If execMode = 0 Then
‘ アクションクエリ（更新・追加・削除）
qd.Execute dbFailOnError
Else
‘ セレクトクエリ（レコードセット返却）
Set outRs = qd.OpenRecordset(dbOpenSnapshot, dbForwardOnly)
End If
QueryPerformanceCounter endTime
‘ 経過時間の計算（ミリ秒換算）
elapsedMs = CCur((endTime – startTime) / freq) 1000
uid_WriteLog qd.Name, qd.SQL, elapsedMs, True, “”
ProfileAndExecuteQuery = elapsedMs
‘ クリーンアップ
GoTo Finally
ErrorHandler:
QueryPerformanceCounter endTime
elapsedMs = CCur((endTime – startTime) / freq) 1000
‘ エラー発生時も計測時間を記録し、エラー内容をログへ
uid_WriteLog qd.Name, qd.SQL, elapsedMs, False, Err.Description
‘ 呼び出し元へエラーを伝播
Dim errDesc As String
errDesc = Err.Description
On Error Resume Next
Err.Raise vbObjectError + 1000, “ProfileAndExecuteQuery”, “Query Error [” & qd.Name & “]: ” & errDesc
Finally:
Set db = Nothing
Exit Function
End Function
/
- プロファイル結果をCSV形式でログファイルに出力する
/
Private Sub uid_WriteLog(ByVal queryName As String, ByVal sqlText As String, ByVal ms As Double, ByVal isSuccess As Boolean, ByVal errorMsg As String)
Dim logPath As String
Dim fileNum As Integer
Dim cleanedSQL As String
‘ ログファイルのパス（カレントDBと同階層の Logs フォルダ）
logPath = CurrentProject.Path & “\QueryPerformance.log”
‘ SQL内の改行やタブを除去して1行にする（ログの構造化のため）
cleanedSQL = Replace(Replace(sqlText, vbCrLf, ” “), vbTab, ” “)
cleanedSQL = Replace(cleanedSQL, “”””, “”””””) ‘ CSVのエスケープ
fileNum = FreeFile
Open logPath For Append As #fileNum
‘ ログフォーマット: タイムスタンプ, クエリ名, 実行時間(ms), 成否, エラー内容, SQL
Print #fileNum, Format(Now, “yyyy-mm-dd hh:nn:ss”) & “,” & _
“””” & queryName & “””,” & _
Format(ms, “0.000”) & “,” & _
IIf(isSuccess, “SUCCESS”, “FAILURE”) & “,” & _
“””” & errorMsg & “””,” & _
“””” & cleanedSQL & “”””
Close #fileNum
‘ 閾値を超えた遅いクエリの場合、イミディエイトウィンドウに警告を飛ばす
If ms > SLOW_QUERY_THRESHOLD_MS Then
Debug.Print “【SLOW QUERY ALERT】 ” & queryName & ” ―― ” & Format(ms, “0.000”) & ” ms”
End If
End Sub
—
3. チーフアーキテクトが教える：メモリ最適化とオブジェクトのライフサイクル
Access VBAにおける最大の悪夢は「メモリリーク」と「Bloat（データベースファイルの肥大化）」である。
特にDAOオブジェクト（`Database`, `QueryDef`, `Recordset`）をループ内で不適切に生成・破棄すると、ACEエンジン内部のメモリ管理が破綻し、デスクトップアプリ全体のパフォーマンス低下を招く。
1. `CurrentDb` の乱用に警戒せよ
`CurrentDb` は呼び出すたびに新しいDatabaseオブジェクトのインスタンスをメモリ上に生成する。
これをループの条件式やクエリ実行の都度呼び出すのは自殺行為である。
‘ 【アンチパターン】ループ内でCurrentDbを叩く
Dim i As Long
For i = 1 to 1000
CurrentDb.QueryDefs(“q_Update”).Execute ‘ 毎回インスタンスが生成され、解放が遅れる
Next i
‘ 【推奨される実装】インスタンスを一度変数に収め、スコープを制御する
Dim db As DAO.Database
Set db = CurrentDb
Dim qd As DAO.QueryDef
Set qd = db.QueryDefs(“q_Update”)
Dim i As Long
For i = 1 to 1000
qd.Execute dbFailOnError
Next i
‘ 明示的解放
Set qd = Nothing
Set db = Nothing
2. クエリ定義の動的生成と確実な破棄（Temporary QueryDef）
既存のQueryDefを永続的に保存する必要がない場合、一時的なQueryDefをコード上から動的に生成し、使用後に即座に削除（あるいは `Close`）すべきだ。永続的なクエリをシステムカタログ（MSysObjects）にゴミとして残し続けることは、パフォーマンス劣化の直結要因となる。
Public Sub ExecuteAdhocQuery(ByVal sql As String)
Const TEMP_QUERY_NAME = “Temp_Adhoc_Query”
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
‘ 同名の一時クエリが残っていれば削除
On Error Resume Next
db.QueryDefs.Delete TEMP_QUERY_NAME
On Error GoTo 0
‘ 動的QueryDefの生成
Set qd = db.CreateQueryDef(TEMP_QUERY_NAME, sql)
‘ プロファイラ経由で実行
ProfileAndExecuteQuery qd, 0
‘ 使用後、速やかにコレクションから削除してシステムカタログをクリーンに保つ
db.QueryDefs.Delete TEMP_QUERY_NAME
Set qd = Nothing
Set db = Nothing
End Sub
—
4. レガシーシステム・大規模連携における実践的チューニング哲学
数百万件のレコードを抱える外部基幹システムとのCSV/ODBC連携において、Access単体での処理限界を超える場面に直面する。このプロファイラツールを用いることで、以下のボトルネックが手に取るように判明する。
1. ODBCリンクテーブル経由の非効率なJOIN
- ローカルの一時テーブル（TempTable）に一度データをバルクインポート（Pass-Throughクエリ等）してから処理するアーキテクチャへの切り替え判断基準を、プロファイラのログ（数千ミリ秒単位の乖離）から客観的に導き出せる。
2. インデックス欠如の検知
- 抽出条件（WHERE句）を変更していないにもかかわらず、レコード数の増加に比例して実行時間が指数関数的に増大するクエリを、`SLOW_QUERY_THRESHOLD_MS` のアラートによって一網打尽にできる。
—
5. 結びに代えて
Access VBAは「おもちゃの言語」ではない。その内部構造、ACEエンジンの特性、そしてメモリのライフサイクルを完全に理解したアーキテクトが手綱を握れば、基幹系に匹敵する堅牢性と速度を引き出すことが可能だ。
今回紹介した「QueryDefパフォーマンスプロファイラ」をあなたのソリューションに組み込み、感覚に頼ったチューニングの時代に終止符を打ってほしい。コードの裏側で何が起きているのかを把握することこそが、真のエンジニアリングである。
