Accessにおいてインデックスが効かない原因
概要
Accessでハマったことメモ。
Access(というかデータベース全般)を触ったことあるよって人はちょっと見てみて。
環境
今回の目標は、一年間分の取引データを元に、月別の金額合計を算出することです。
これを実現するために、以下のようなSQLを用います。
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に一部追加するだけです。
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
1のJETSHOWPLAN
という名前のデータへON
とセットしてください。
再起動後にAccessで任意のSQL文を実行すると、showplan.outというファイルが作成されるようになります2。
ではこのshowplan.outの中身を覗いてみましょう。するとテーブル情報、インデックス、結合順序なんかが確認できますね。その内X-Prod join
という記述が見られます。おそらくこれはクロス結合でしょうか。インデックスが効いていないことが確認できました。
しかし疑問は残ります。インデックスが効いていないのであれば、(最初に実行した)月別の合計金額を求める際にも時間がかかるはずです。しかし、こちらの実行計画を確認するとインデックスが正しく使われていることが確認できます。
インデックスの操り方は
AccessではHINT句のようなものは存在しなので、実行計画をチューニングすることもできません。詰みました。
解決策としては、不要なインデックスを削除することでした。
実は用意したテーブルには結合の際には用いられていないインデックスが多数存在していました3。
Accessがどうやって実行計画を決めているのかはわかりませんが、余計なインデックスが存在することでクロス結合の方がましと判断してしまったのでしょう。
SQL文 | 実行時間 |
---|---|
sql1 | 約2.0s |
sql2 | 約0.6s |
やったぜ。
おわりに
今回はインデックスを調整することで解決できましたが、Accessでは解決できない場合もありそうですね。Access以外も触りたい。
Author And Source
この問題について(Accessにおいてインデックスが効かない原因), 我々は、より多くの情報をここで見つけました https://qiita.com/thakaz/items/807aa4a3e63015f21d31著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .