Accessクエリ高速化の技術｜遅い原因とアンチパターン改善

結論：Accessクエリが遅い原因の8割はインデックス不足とドメイン集計関数の乱用

当社がこれまで対応してきたAccess改善案件の経験から言うと、クエリ速度の問題は大抵２パターンに分かれます。結合列や絞り込み列にインデックスがない、あるいはDLookupやDSumをクエリの列として並べている、その２つです。どちらも「動いているから問題ない」と放置されやすく、データが増えるにつれて急激に遅くなるため、ある日突然「重い」と気づく。対処法は難しくないので、この記事で順番に見ていきます。

JET/ACEエンジンがクエリをどう処理するか

Accessのデータベースエンジンは旧JETを基盤とする後継エンジンACE（Access Connectivity Engine）です。以下はJET 4.0の公開資料で説明されている仕組みで、ACEはJETを基盤にしているため概ね当てはまると考えられます。クエリを受け取ったエンジンは大きく4段階で処理します。SQL文を解析して内部形式に変換（コンパイル）し、コスト計算で実行計画を選び（最適化）、実行コードを生成してから結果を返す（実行）、という流れです。

最適化フェーズでACEが使うのがRushmoreテクノロジです。もともとFoxProの技術を取り込んだもので、インデックスのビットマップを作り、複数インデックスの積集合（AND）や和集合（OR）を高速に処理します。ポイントは、Rushmoreはインデックスが存在する列にしか機能しない点です。WHERE句の絞り込み列にインデックスがなければ、その条件についてはテーブルを先頭から全件なめる「テーブルスキャン」で評価されます（他の列にインデックスがあれば、そちらで候補を絞ってから評価します）。

JET/ACEのテーブルアクセス戦略は3種類あります。テーブルスキャン、単一インデックスによるインデックスレンジ、複数インデックスを使うRushmore制限です。ACEはコストと選択性を比較して戦略を選ぶため、選択性の低いインデックスではテーブルスキャンの方が速いと判断されることもあります。Rushmoreが有効に使えるとデータページをほぼ読まずにインデックスページだけで結果を返せる場合もある、というのがJET 4.0の公開資料（Access 2002 Desktop Developer's Handbook | Microsoft Learn）での説明です。

もう一つ知っておきたいのは、ACEはクエリのコンパイル時のテーブル統計（行数・ページ数・インデックス選択性）を使って実行計画を決める点です。インデックスを追加したり行数が大幅に変わったりした後は、クエリをデザインビューで開いて保存し直すと再コンパイルが走り、実行計画が更新されます。

遅くなる典型パターンとその原因

よくある遅いクエリのパターンを表にまとめます。それぞれACEがどう処理してしまうか、原因の理解が改善の糸口になります。

インデックス設計の実務ポイント

Microsoftの公式ドキュメント（Create and use an index to improve performance）では「よく検索・ソート・結合に使うフィールドにインデックスを付けること」と明記されています。一方、データ追加や更新のたびにインデックスも更新されるため、書き込みが多いテーブルで無闇にインデックスを増やすとむしろ遅くなります。

設計の基本は次のとおりです。

• 主キーは自動的にインデックス付き。外部キー列は、参照整合性を設定したリレーションシップではAccessが自動でインデックスを作成しますが、単に結合線を引いただけの場合は作成されません。テーブルのデザインビューで確認しておくと安心です
• 重複が多い列（性別・ステータス区分など3〜5種類しか値がない列）は選択性が低いため、ACEのオプティマイザがコスト上インデックスを使わないと判断し、インデックスを付けても速度改善の効果が限定的な場合があります
• 複合インデックスは「よく一緒に絞り込む列」の組み合わせで作る。最大10列まで設定できますが、実際には2〜3列で組むことがほとんどです
• 一意インデックス（No Duplicates）はACEが統計情報をより正確に扱えるため、主キー以外でも値が一意な列は一意インデックスを優先する

なお、ACEはフィールド名がAutoIndex設定に登録された文字列（デフォルトではID・key・code・numなど）で始まるか終わる場合に自動でインデックスを付けます。設計当初に意図せず付いているケースもあるので、テーブルのデザインビューで確認しておくと無駄がありません。

DLookup・DSum置き換えの具体例

ドメイン集計関数（DLookup・DSum・DCountなど）はVBAやフォームの式で使う分には便利ですが、クエリの集計列として並べるのは避けたほうが得策です。Microsoft公式も「DLookupを使うよりも、必要なフィールドを含むクエリを作ってそれをフォームやレポートのレコードソースにする方が効率的」と述べています（DLookup 関数 | Microsoft サポート）。

たとえば受注明細テーブルで各行に顧客名を引いてくるためにDLookupをクエリ列に書いている場合、内部結合で書き換えられます（顧客IDが数値型で顧客マスタで一意、かつ参照整合性が保たれている前提です。テキスト型の場合は条件式の引用符が異なります。未登録の顧客IDが明細に存在する場合は LEFT JOIN を検討してください）。

DSumで「グループ合計」を求めているケースは、GROUP BY集計クエリを先に作ってメインクエリにJOINする設計で置き換えられます。一方、行ごとに増えるランニング累計（例: 日付が進むにつれ積み上がる売上）の置き換えは別の設計が必要です。レポートで使うならAccessのRunningSumプロパティが手軽ですが、クエリで実現する場合は自己結合などを使う設計になります。実案件では、グループ合計のDSumを集計JOINベースに書き直したところ、開くまでの待ち時間が大幅に短縮された例があります（当社実務経験に基づく参考値です）。

複数人で使うと遅い・壊れる問題を別記事で解説しましたが、DLookupの乱用は単一ユーザーでも遅くなる代表例であり、ネットワーク越しではさらに深刻になります。

パフォーマンスアナライザーと診断の始め方

Accessには標準でパフォーマンスアナライザー（Performance Analyzer）が搭載されています。「データベースツール」タブの「分析」グループから起動でき、テーブル・クエリ・フォームなどを選んで分析すると、「推奨」「提案」「アイデア」の3段階で改善案を返してくれます（Use the Performance Analyzer to optimize an Access database | Microsoft Support）。「推奨」カテゴリはAccessが自動で実行できるものです。まずこのツールを全オブジェクトに対して走らせるのが、診断の第一歩として手軽です。

パフォーマンスアナライザーで見つかる代表的な指摘は、インデックスが不足しているテーブルフィールドです。ただし「アイデア」カテゴリは手動で直す必要があり、提案どおりに変更して逆に遅くなるケースも稀にあるため、変更前後でクエリの実行時間を測っておくと安心です。

クエリが本当に遅いのか、それともフォーム側の問題なのかを切り分けるには、クエリをデータシートビューで単体実行してみるのが一番早いです。クエリ単体では速いのにフォームで遅い場合は、コンボボックスやリストボックスが別途クエリを発行している可能性があります。

データが増えてきてクエリ改善で追いつかなくなった場合の選択肢として、テーブルだけSQL Serverへ移す構成があります。Accessの画面はそのままにデータだけSQL Serverへ移す方法でその概要を解説しています。また無料のSQL Server Expressを使う手順も参考にしてください。

自社のAccessが今の状態で改善できるかどうか確かめたい場合は、無料の解析可否チェックでファイルを送らずに仮判定できます。

よくある質問

Q. インデックスを付けるだけで本当に速くなりますか。

結合列や絞り込み列にインデックスがない場合は大きく改善する例が多いです。ただし小規模テーブルではインデックスの改善効果が限定的な場合もあり、原因が別にある可能性があります。まずパフォーマンスアナライザーを走らせてインデックス不足の指摘が出るか確認するのが手順として楽です。

Q. DLookupをクエリ列に使うのが「なぜ」遅いのか技術的に知りたい。

DLookupをはじめとするドメイン集計関数はACEのクエリオプティマイザの最適化対象外です。クエリが返す行数だけ個別に呼び出され、毎回テーブルにアクセスします。1万行のクエリに1列DLookupがあれば理論上1万回のルックアップが走ります。SQLのJOINに書き換えると、ACEがインデックス利用・結合順序・実行計画を一体的に最適化できるようになるため、DLookupとは比較にならない改善が見込めます。

Q. クエリを保存したクエリオブジェクトにしたほうが速いと聞きました。本当ですか。

一般的にはそのとおりです。VBAのコード内に直接SQL文字列を書いてRecordsetを開く場合、実行のたびにACEがコンパイルと最適化を行います。保存済みクエリはコンパイル済みの形式で保持されるため、同じSQLでも繰り返し実行時のオーバーヘッドが小さくなります。ただし、インデックスを追加した後は一度デザインビューで開いて保存し直すと実行計画が更新されます。

Q. Accessのクエリをチューニングしても限界を感じています。移行しか手がないですか。

クエリ設計の改善（インデックス・結合・ドメイン関数の排除）でかなり改善できる場合もありますが、同時利用者の増加・ネットワーク環境・データ量の組み合わせによってはACEがファイル型データベースとして持つ構造的な限界に当たることがあります。その場合はテーブルだけSQL Serverへ移す段階移行が現実的です。破損トラブルも増えてきている場合は移行の優先度を上げるサインです。