こんにちは。開発プロジェクトのリーダーである私だ。
これまで数多くのAccess大規模システムを見てきたが、パフォーマンスチューニングの現場で最も多い誤解、そして最も簡単に劇的な改善を生み出せるポイントをご存知だろうか？
それが今回解説する「QueryDefのキャッシュと再利用戦略」だ。
ループ処理の中で毎回SQL文字列を生成し、`CurrentDb.Execute` で直叩きしたり、毎回ゼロからクエリを作り直したりしていないだろうか？
もし心当たりがあるなら、あなたのAccessアプリは本来の性能の10%も発揮できていない。今回は、Jet/ACEデータベースエンジンの深層メカニズムに踏み込み、大量データ処理を極限まで高速化するプロの設計思想を伝授しよう。
—
なぜ「毎回SQLを実行するコード」は遅いのか？
多くの開発者がやりがちなアンチパターンを見てみよう。
‘ 【非効率なアンチパターンの例】
Dim i As Long
For i = 1 to 10000
‘ ループの度にSQL文字列を結合し、エンジンにパース・最適化を強制している
CurrentDb.Execute “UPDATE T_Stock SET StockQty = StockQty – 1 WHERE ItemID = ” & i, dbFailOnError
Next i
このコードの何が問題か？
Accessの裏側で動いているデータベースエンジン（Jet / ACE）は、SQL文を受け取るたびに以下の重たい処理を行っている。
1. 構文解析（Parsing）: 文字列としてのSQLが文法的に正しいかチェックする。
2. クエリ最適化（Optimization）: どのインデックスを使うべきか、どの実行計画が最速かを計算する（これが一番重い）。
3. 実行（Execution）: 実際のデータ処理を行う。
つまり、1万回のループを回せば、まったく同じ構造のSQLに対して、無駄に1万回も「最適化の計算」を行わせていることになる。これでは業務システムとして使い物にならない。
—
解決策：QueryDefのプリコンパイル特性を支配する
この無駄を根絶するのが QueryDefオブジェクトのキャッシュと再利用 だ。
あらかじめクエリの骨組み（QueryDef）をデータベースに登録しておく（あるいはメモリ上に保持する）。これにより、エンジンは最初の1回だけパースと最適化（プリコンパイル）を行い、以後はその実行計画をキャッシュし続ける。
ループ内では、コンパイル済みのオブジェクトに対して「パラメータの値だけを入れ替えて実行（Execute）」する。この差は、大量データ処理（数千〜数万件のインサート・アップデート）において、処理時間を数分から数秒へと劇的に短縮する。
—
【実践】プロダクションコード：堅牢かつ高速なQueryDef再利用パターン
実務でそのまま使える、堅牢性とパフォーマンスを極限まで高めたVBAコードを提示しよう。
ここでは、一時的なQueryDefをあらかじめ作成し、パラメータをバインドして高速に一括処理するデザインパターンを採用している。
Option Compare Database
Option Explicit
”’
”’
Public Sub ExecuteHighSpeedBatchUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim startTime As Double
Dim i As Long
startTime = Timer
Set db = CurrentDb
On Error GoTo ErrorHandler
‘ 1. 永続的、または明示的な一時QueryDefの構築
‘ 毎回CreateQueryDefするオーバーヘッドを避けるため、存在チェックして使い回すか、
‘ あらかじめデザインビューでクエリを作成しておき、それを呼び出すのが最も堅牢。
‘ 今回はコード内で動的にQueryDefオブジェクトを安全に生成・管理する手法を示す。
Const QRY_NAME = “qdefTempUpdate”
‘ 既に同名のクエリが存在する場合は削除してクリーンな状態で作成
On Error Resume Next
db.QueryDefs.Delete QRY_NAME
On Error GoTo ErrorHandler
‘ パラメータクエリとしてQueryDefを定義（この時点でプリコンパイルの準備が整う）
Set qdf = db.CreateQueryDef(QRY_NAME, _
“PARAMETERS pStockQty Long, pItemID Long; ” & _
“UPDATE T_Stock SET StockQty = [pStockQty] WHERE ItemID = [pItemID];”)
‘ トランザクションを開始し、ディスクI/Oとログ書き込みを最適化
db.BeginTrans
‘ 2. ループ内ではパラメータの代入とExecuteのみを行う（最適化済みの計画を再利用）
For i = 1 To 10000
‘ パラメータに値をバインド（型安全性の確保とSQLインジェクション/意図せぬ型の誤作動防止）
qdf.Parameters(“pStockQty”) = i 10 // 例としての計算値
qdf.Parameters(“pItemID”) = i
‘ 3. dbFailOnErrorを指定し、エラー時に即座にトランザクションをロールバックできるようにする
qdf.Execute dbFailOnError
Next i
‘ トランザクションをコミット
db.CommitTrans
MsgBox “高速バッチ処理が完了しました。処理時間: ” & Format(Timer – startTime, “0.00”) & “秒”, vbInformation
CleanUp:
‘ 資源の解放
On Error Resume Next
If Not qdf Is Nothing Then
db.QueryDefs.Delete QRY_NAME ‘ 一時クエリのクリーンアップ
End If
Set qdf = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
‘ エラー発生時はトランザクションをロールバック
If Not db Is Nothing Then db.Rollback
MsgBox “予期せぬエラーが発生しました: ” & Err.Description, vbCritical
Resume CleanUp
End Sub
—
開発現場で絶対に押さえるべき「3つの鉄則」
プロのアーキテクトとして、この手法を実務導入する際に絶対に守るべき注意点を伝授しておこう。
1. `dbFailOnError` を必ず付与せよ
DAOの `Execute` メソッドでこのフラグを忘れてはならない。これが無いと、ループ途中でエラー（一意制約違反やデータ型の不一致など）が発生しても、VBAはエラーを無視して処理を続行し、「一部だけデータが書き換わった壊れたデータベース」が完成してしまう。バグの温床になるため、一括処理の `Execute` には必須だ。
2. トランザクション（`BeginTrans` / `CommitTrans`）との組み合わせ
QueryDefで高速化しても、1回1回ディスクに書き込みを行っていれば遅い。トランザクションで囲むことで、メモリ上で一連の処理を完結させ、最後にまとめてディスクへ書き込ませる。これにより速度がさらに数倍跳ね上がる。ただし、長すぎるトランザクションはロック競合を招くため、数万件単位で適度に分割するのがベストプラクティスだ。
3. 文字列連結によるSQL構築の完全排除
パラメータクエリ（`PARAMETERS …;`）を使う最大のメリットは、パフォーマンスだけではない。「SQLインジェクション」や「シングルクォーテーションのエスケープ漏れによる構文エラー」を100%防げることだ。変数を直接SQL文字列に組み込むのは、現代の開発においては「技術的負債」でしかない。常にパラメータをバインドする習慣をつけよう。
—
まとめ
Access VBAにおけるパフォーマンス低下の多くは、データベースエンジンとの対話方法を誤っていることに起因する。
- 「毎回SQLを書いて実行するな。QueryDefに仕事を覚えさせろ。」
この原則を胸に刻むだけで、あなたの作るAccessアプリは見違えるほど軽快で、堅牢なプロフェッショナルシステムへと生まれ変わるはずだ。
次の開発から、ぜひこのQueryDefキャッシュ戦略を標準装備してほしい。健闘を祈る。
