Accessにおいてインデックスが効かない原因

概要

Accessでハマったことメモ。
Access(というかデータベース全般)を触ったことあるよって人はちょっと見てみて。

環境

• Windows7

• Office 2016

• テーブルとしては__単価(3種類)__、ユーザ名、__取引データ__があるとします。
  

やりたいこと

今回の目標は、__一年間分の取引データを元に、月別の金額合計__を算出することです。
これを実現するために、以下のようなSQLを用います。

sql1

SELECT
    Format(日時, "yyyy年mm月") as 月,
    Sum(金額1) as 金額1合計,
    Sum(金額2) as 金額2合計,
    Sum(金額3) as 金額3合計,
FROM
    (
        SELECT
            取引データテーブル.日時 AS 日時,
　　　　　　　金額1テーブル.金額 as 金額1,
　　　　　　　金額2テーブル.金額 as 金額2,
　　　　　　　金額3テーブル.金額 as 金額3
        FROM
            (
                (
                    (
                        ユーザテーブル
                        RIGHT JOIN
                            取引データテーブル
                        ON  ユーザテーブル.ユーザコード = 取引データテーブル.ユーザコード
                    )
                    LEFT JOIN
                        金額1テーブル
                    ON  ユーザテーブル.金額1コード =  金額1テーブル.金額1コード
                )
                LEFT JOIN
                    金額2テーブル
                ON  ユーザテーブル.金額2コード =  金額2テーブル.金額2コード
            )
            LEFT JOIN
                金額3テーブル
            ON  ユーザテーブル.金額3コード =  金額3テーブル.金額3コード
        WHERE
            日時 >= #年開始日# AND 日時 < #年終了日#
    )
GROUP BY
    Format(日時, "yyyy年mm月")
ORDER BY
    Format(日時, "yyyy年mm月")

はい。Accessの場合は3つ以上のテーブルを一度に結合できませんが、入れ子構造にすればむりやり結合できます。
これで取引データを月ごとにグループ化し、各金額の合計を取得できます。

問題はここから

それではこの合計を特定のユーザのみに絞って出力したいとします。これも簡単で上記のSQLに一部追加するだけです。

sql2(抜粋)

        WHERE
            日時 >= #年開始日# AND 日時 < #年終了日#
         AND  取引データテーブル.ユーザコード = "対象のユーザコード"

確かに結果には問題ありません。が、処理時間がかなり延びてしまいました。

SQL文	実行時間
sql1	約2.0s
sql2	約13.0s
本来ならWhere句で対象を絞っているので早くなるはずなのですが・・・。

原因を追究する必要がありそうです。

何が原因なのさ

遅くなる原因はおそらくインデックスが効いていない為と考えられますが、実際どうなのでしょうか。確認してみましょう。
多くのDBMSでは実行計画が確認できますが、Accessも簡易ですが用意されています。

レジストリエディタを開き、HKLM\SOFTWARE\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\engines\Debug¹のJETSHOWPLANという名前のデータへONとセットしてください。
再起動後にAccessで任意のSQL文を実行すると、showplan.outというファイルが作成されるようになります²。

ではこのshowplan.outの中身を覗いてみましょう。するとテーブル情報、インデックス、結合順序なんかが確認できますね。その内X-Prod joinという記述が見られます。おそらくこれはクロス結合でしょうか。インデックスが効いていないことが確認できました。

しかし疑問は残ります。インデックスが効いていないのであれば、(最初に実行した)月別の合計金額を求める際にも時間がかかるはずです。しかし、こちらの実行計画を確認するとインデックスが正しく使われていることが確認できます。

インデックスの操り方は

AccessではHINT句のようなものは存在しなので、実行計画をチューニングすることもできません。詰みました。

解決策としては、不要なインデックスを削除することでした。
実は用意したテーブルには結合の際には用いられていないインデックスが多数存在していました³。
Accessがどうやって実行計画を決めているのかはわかりませんが、余計なインデックスが存在することでクロス結合の方がましと判断してしまったのでしょう。

SQL文	実行時間
sql1	約2.0s
sql2	約0.6s

やったぜ。

おわりに

今回はインデックスを調整することで解決できましたが、Accessでは解決できない場合もありそうですね。Access以外も触りたい。

1. ただしこのパスはOfficeのバージョンやビット数で異なりますので注意してください。 ↩

2. このファイルはカレントディレクトリに生成されるようになります。そのため通常はマイドキュメントへ生成されますが、カレントディレクトリを移動させるとそのパスへ生成されるようになります。 ↩

3. インデックスが多すぎてもたいした問題はないと考えていました。 ↩