動的SQLにおける「IN句」の壁を粉砕する——配列データを一時テーブル経由でJOINさせるエンタープライズDAOアーキテクチャ
エンタープライズの過酷な運用に耐えうるAccessソリューションを構築してきたエンジニアなら、誰もが一度は「IN句の限界」という名の壁に激突しているはずだ。
画面上のマルチセレクトListBoxから選択された数千件のキー、あるいは外部APIやCSVから取り込んだ可変長ID配列をSQLに渡す際、初心者は決まって以下のような文字列結合に走る。
SELECT FROM tbl_Orders WHERE OrderID IN (1001, 1002, 1003, …);
断言するが、このアプローチは技術的負債以外の何物でもない。
ACE（Access Database Engine / 旧Jet）において、SQLステートメントの最大長（約64KB）による物理制限に引っかかるだけでなく、構文解析エンジン（Parser）に過大な負荷を与え、実行計画のキャッシュを破壊する。さらに、VBA側で配列を走査してカンマ区切り文字列を巨大結合するプロセスのメモリフットプリントとCPUコストは、データ量に比例して指数関数的に悪化していく。そもそも、IN句の引数リストをパラメータクエリ（`DAO.QueryDef`）としてバインドすることはRDBMSの構造上不可能だ。
本稿では、この悪弊を根絶し、数千から数万件規模の可変長キー配列をミリ秒単位で処理するための「ローカル一時ワークテーブル＋INNER JOIN」アーキテクチャを徹底解説する。
—
1. なぜ「IN句の動的生成」は破綻するのか
一時テーブル設計に入る前に、文字列結合によるIN句構築がACE内部で引き起こす致命的な問題を解剖しておく。
パラメータクエリ化の構造的破綻
`QueryDef.Parameters` は単一のスカラー値（数値、文字列、日付）をバインドするための仕組みである。`WHERE OrderID IN (?)` に対してカンマ区切りの文字列 `”1, 2, 3″` を渡せば、エンジンはそれを単一の文字列リテラルと解釈するため、型不一致（Type Mismatch）または「IDが『1, 2, 3』という1つの値に一致するか」という評価になり、意図した結果は得られない。結果として開発者は「動的SQLの文字列結合」を強いられ、SQLインジェクション脆弱性とエスケープ処理の泥沼に足を踏み入れることになる。
オプティマイザのオーバーヘッドと実行計画
ACEのクエリオプティマイザは、実行されたSQLをコンパイルし、インデックス統計に基づいた実行計画（Rushmoreテクノロジー）をメモリ上に構築する。しかし、要素数が変動する動的IN句は、実行されるたびに「完全に未知の新規クエリ」として扱われる。結果として、実行の都度ハードパース（構文解析・最適化・コンパイル）が発生し、Jetエンジンのワーキングメモリを圧迫し続ける。
メモリの断片化（Heap Fragmentation）
VBAの文字列（`BSTR`）はイミュータブル（不変）である。`strSQL = strSQL & arr(i) & “,”` というループは、反復のたびにメモリの再割り当てと既存バッファの破棄を繰り返す。要素数が数千を超えた時点で、OLEオートメーションの文字列ヒープ領域はズタズタに断片化し、ガベージコレクションのオーバヘッドによってプロセス全体が急激に失速する。
—
2. 解決策：ローカル・リレーショナル・パイプライン
解法はシンプルかつ堅牢だ。「配列データをローカルの一時テーブルへ超高速にバルクインサートし、ターゲットクエリとINNER JOINさせる」。
このアプローチには以下の絶対的優位性がある。
1. 完全なパラメータ化と静的SQL化: 実行するクエリは `SELECT A. FROM tbl_Target AS A INNER JOIN wtmp_IDList AS B ON A.ID = B.ID` という完全に固定化された静的クエリ定義（`QueryDef`）となり、オプティマイザの実行計画が半永久的に再利用される。
2. 限界突破: IN句の構文的限界を超え、数万〜数十万件のID集合であっても、リレーショナルエンジンのインデックスマージ（Join処理）の土俵で極めて高速に処理できる。
3. MDB肥大化（Bloat）の抑制: 一時テーブルのライフサイクルを厳密に制御（トランザクション内での利用、またはセッション単位の切り回し）することで、Access特有のファイルサイズ肥大化を極小化する。
—
3. 超高速インサートを実現するDAO最適化手法
一時テーブルアプローチの唯一の懸念点は「VBAの配列からテーブルへの書き込みコスト」である。ここを `CurrentDb.Execute “INSERT INTO …”` のループで実装しては本末転倒だ。
極限の書き込み性能を叩き出すための鉄則は以下の3点にある。
- トランザクション（`DBEngine.BeginTrans` / `CommitTrans`）の明示的利用:
ディスクI/Oのフラッシュを1回に集約する。これを行わない場合、インサートごとにディスクコミットが発生し、数千件で数十秒を要することになる。
- `dbAppendOnly` オプションによるRecordsetオープン:
既存レコードの読み込みをバイパスし、書き込み専用バッファのみを確保することで、メモリとロックのオーバーヘッドを極限まで削減する。
- COMオブジェクトのスコープ最適化:
ループ内で `Field` オブジェクトの参照解決（名前解決）を発生させない。インデックス参照、または `DAO.Field` オブジェクトへの参照をループ外でキャッシュする。
—
4. プロダクションコード：実装の全貌
以下に、配列データを一時テーブルに流し込み、JOINクエリによってターゲットデータを抽出する完全な実装例を示す。精密集計のためにWin32 APIの `QueryPerformanceCounter` を組み込み、実測マイクロ秒単位でのプロファイリングを可能にしている。
事前準備
ローカルデータベースに以下のワークテーブルを作成しておくこと。
- テーブル名: `wtmp_IDList`
- フィールド名: `TargetID`（Long型 / 符号あり32bit整数。主キーまたはインデックス（重複あり/なし）を設定）
標準モジュール（`mod_DynamicJoinOptimizer`）
Option Compare Database
Option Explicit
‘ ==============================================================================
‘ Win32 API 高精度タイマー宣言
‘ ==============================================================================
If VBA7 Then
Private Declare PtrSafe Function QueryPerformanceCounter Lib “kernel32” (ByRef lpPerformanceCount As Currency) As Long
Private Declare PtrSafe Function QueryPerformanceFrequency Lib “kernel32” (ByRef lpFrequency As Currency) As Long
Else
Private Declare Function QueryPerformanceCounter Lib “kernel32” (ByRef lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib “kernel32” (ByRef lpFrequency As Currency) As Long
End If
Private Const TEMP_TABLE_NAME As String = “wtmp_IDList”
Private Const TARGET_TABLE_NAME As String = “tbl_Orders”
‘ ==============================================================================
‘ 外部公開エントリーポイント
‘ 大量ID配列を一時テーブルに注入し、JOINクエリによってRecordsetを取得する
‘ ==============================================================================
Public Sub ExecuteHighPerformanceJoin()
Dim db As DAO.Database
Dim rsResult As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim testIDs() As Long
Dim i As Long
Const RECORD_COUNT As Long = 10000 ‘ 1万件のテストデータ
‘ 1. ダミー配列データの生成 (例: 1万件のランダムID)
ReDim testIDs(0 To RECORD_COUNT – 1)
For i = 0 To RECORD_COUNT – 1
testIDs(i) = i + 100000
Next i
Dim startTime As Currency
Dim endTime As Currency
Dim freq As Currency
QueryPerformanceFrequency freq
QueryPerformanceCounter startTime
‘ DBインスタンスの取得
Set db = DBEngine(0)(0)
On Error GoTo ErrorHandler
‘ 2. ワークテーブルの初期化およびデータの一括投入
BulkInsertIDs db, testIDs
‘ 3. 固定化されたJOINクエリの定義・実行
‘ 注: プロダクション環境ではクエリをGUI側で保存済みQueryDefとして作成しておくのが最善
Const QUERY_NAME As String = “zqry_TempJoinExecution”
On Error Resume Next
db.QueryDefs.Delete QUERY_NAME
On Error GoTo ErrorHandler
Dim strSQL As String
strSQL = “SELECT T. ” & _
“FROM ” & TARGET_TABLE_NAME & ” AS T ” & _
“INNER JOIN ” & TEMP_TABLE_NAME & ” AS W ON T.OrderID = W.TargetID;”
Set qdf = db.CreateQueryDef(QUERY_NAME, strSQL)
‘ ForwardOnly, ReadOnlyで最速オープン
Set rsResult = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
‘ 計測終了
QueryPerformanceCounter endTime
Debug.Print “=== 処理完了 ===”
Debug.Print “データ注入 & JOINクエリ準備時間: ” & _
Format$(((endTime – startTime) / freq) 1000, “0.000”) & ” ms”
‘ 4. レコードセットの消費（ここではテストのためEOF確認のみ）
If Not rsResult.EOF Then
Debug.Print “データ検出: 正常にJOIN抽出が機能しました。”
Else
Debug.Print “データ検出: 0件（モックデータのため）”
End If
CleanUp:
‘ 明示的なオブジェクト破棄（ライフサイクルの厳密な管理）
On Error Resume Next
If Not rsResult Is Nothing Then rsResult.Close: Set rsResult = Nothing
If Not qdf Is Nothing Then qdf.Close: Set qdf = Nothing
‘ ワークテーブルのクリーンアップ（肥大化防止）
If Not db Is Nothing Then
db.Execute “DELETE FROM ” & TEMP_TABLE_NAME & “;”, dbFailOnError
Set db = Nothing
End If
Exit Sub
ErrorHandler:
Debug.Print “実行時エラー: ” & Err.Number & ” – ” & Err.Description
Resume CleanUp
End Sub
‘ ==============================================================================
‘ 高速バルクインサート処理
‘ トランザクション + dbAppendOnly + Field参照キャッシュによる極限最適化
‘ ==============================================================================
Private Sub BulkInsertIDs(ByRef db As DAO.Database, ByRef ids() As Long)
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim i As Long
Dim lowerBound As Long
Dim upperBound As Long
Dim isTransactionActive As Boolean
lowerBound = LBound(ids)
upperBound = UBound(ids)
‘ 既存ワークデータの切断
db.Execute “DELETE FROM ” & TEMP_TABLE_NAME & “;”, dbFailOnError
‘ 追加専用（dbAppendOnly）でオープンし、読み込みI/Oを一切発生させない
Set rs = db.OpenRecordset(TEMP_TABLE_NAME, dbOpenTable, dbAppendOnly)
‘ フィールドオブジェクトへの参照をキャッシュ（名前解決のオーバーヘッドをループから排除）
Set fld = rs.Fields(“TargetID”)
On Error GoTo BulkError
‘ トランザクション開始: ディスクフラッシュを保留
DBEngine.BeginTrans
isTransactionActive = True
For i = lowerBound To upperBound
rs.AddNew
fld.Value = ids(i)
rs.Update
Next i
‘ トランザクションコミット: 一括ディスク書き込み
DBEngine.CommitTrans
isTransactionActive = False
BulkExit:
On Error Resume Next
Set fld = Nothing
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
BulkError:
If isTransactionActive Then
DBEngine.Rollback
End If
Set fld = Nothing
If Not rs Is Nothing Then rs.Close: Set rs = Nothing
Err.Raise Err.Number, Err.Source, “BulkInsertIDs失敗: ” & Err.Description
End Sub
—
5. アーキテクチャ解説と設計の肝
上記の実装には、シニアエンジニアが押さえるべきいくつかの重要な設計意図が埋め込まれている。
1. `dbAppendOnly` と `dbOpenTable` のシナジー
通常の `OpenRecordset` では、カレントレコードの読み込みやバッファへのインデックスツリーのロードが発生する。しかし、`dbOpenTable` かつ `dbAppendOnly` を指定すると、エンジンは既存レコードを無視し、新規追加専用のポインタのみを初期化する。これにより、すでにワークテーブルに何百万レコードが存在していようと、挿入処理の初期化コストは常にゼロに近似する。
2. COMオブジェクトの参照局所性（Reference Locality）
ループ内で `rs.Fields(“TargetID”).Value = …` と記述すると、VBAは1回の反復ごとに以下のコストを支払う。
1. `Fields` コレクションの取得（COMインターフェース呼び出し）
2. 文字列 `”TargetID”` のハッシュ計算およびフィールド検索（ディスパッチ）
3. 返された `Field` オブジェクトへの `Value` プロパティ設定
配列要素が10,000件あれば、この解決処理が10,000回繰り返される。
コード内で実践している通り、`Set fld = rs.Fields(“TargetID”)` としてループ外で参照を固定（Early Bindingに近い状態）し、ループ内では `fld.Value = ids(i)` のみを行う。これだけで挿入速度は劇的に跳ね上がる。
3. 一時データによる「Database Bloat（MDB肥大化）」の制御
Accessはレコードを `DELETE` しても物理ファイルサイズは縮小しない（領域が空きスロットとしてマークされるのみ）。したがって、一時テーブルを頻繁にクリア＆インサートすると、数日間でファイルサイズが2GB上限に向かって急膨張するリスクがある。
本番運用における回避策は以下の2点である。
- 専用のテンポラリMDBの動的生成/アタッチ:
フロントエンドとは別に、ローカルのテンポラリフォルダに `temp_workspace.accdb` をAPI等で動的に作成し、そこにリンクテーブルを張る。バッチ処理終了後にそのファイルごと `Kill` するアーキテクチャ。
- ワークテーブルの再利用（TRUNCATE的運用）:
小規模であれば、本実装のように `DELETE FROM wtmp_IDList` で運用し、Access終了時の「閉じるときに最適化する」オプションを有効にしておく。
—
6. さらなる極限へ：外部RDBMSとの連携（パススルーの壁）
本稿のパターンは、バックエンドがSQL ServerやAzure SQL Database、PostgreSQLである場合にもそのまま昇華できる。
外部DBに対して1,000件のIN句を投げると、ネットワークのパケット制限やT-SQLのパラメータ上限（2,100個制限）に激突する。
この場合、ローカルのワークテーブルではなく、SQL Server側のテンポラリテーブル（`#temp_ids`）にODBC経由でバルクコピー（bcp、あるいはADOのバッチ更新）を行い、パススルークエリ内でJOINさせる。
Access VBAを単なる「簡易フロントエンド」として見るか、「高度なインメモリ/リレーショナルパイプラインのコントローラー」として掌握するか。その境界線は、こうしたRDBMSの基本原則に忠実なアーキテクチャをコードに落とし込めるかどうかにある。文字列の結合に頼る愚を捨て、リレーショナルの力でリレーショナルを制する設計を選択してほしい。
