MS Access Tips/Sample and VBA and Blog customize etc...

結合条件でインデックスが無効になる場合

以前の 抽出条件でインデックスが無効になる場合 という記事とほとんど同内容ですが、とある掲示板での質問に回答したので、注意喚起という意味もこめて、もう一度、今回の質問のケースを題材にして、解説します。

結合条件でインデックスが無効になる場合

難易度:

インデックスが利用できない間違った結合条件

質問は下記

ACCESS SQLビューで条件分岐の処理速度 OKWave

希望の仕様は、

TblAの伝票NoとTblBの伝票Noが一部一致(伝票Noが10ケタならば左5ケタ、伝票Noが8ケタならば左4ケタが一致)したら、TblBの伝票Noに該当する伝票名を表示させたい。
データ件数は、TblAは10000件、TblBは150件です。

ということで下記のSQLを記述しています。

これで表示速度がかなり遅いということです。
このSQLの間違いは、フィールドを関数の引数に入れてしまっていることです。
フィールドを関数の引数に入れてしまうとインデックスが使用できません。

インデックスが利用できる結合条件

TblA.伝票No は切り取る必要があるので仕方ないですが、TblB.伝票No は関数の外に出せます。

これで、TblB.伝票No のインデックスが利用できます。

サブクエリを使う方法

今回の要件だと、下記のようにサブクエリを使う方法もあります。

これもインデックスは利用できます。

計測結果

サンプルを作成して、速度を計測してみました。

クエリ計測結果

クエリ2はクエリ1より2桁早いです。
クエリ3はクエリ2より若干遅いですね。

どうでしょうか。インデックスの効果は絶大ですね。

計測結果はあくまで当方の環境、サンプルでの結果ですので、あくまで参考程度に。

サンプルファイルを用意しましたのでご自身の環境で結果を確認してください。

Summary

  • フィールドを関数の引数にしたり、演算式に入れるとインデックスを利用できない。
  • インデックスが利用できないと、抽出、結合、並べ替えの速度が極端に遅くなる場合がある。

サンプルファイルが下記からダウンロードできます。
QryUseIndex_07.zip (Access 2007-2010 形式 - 159kb)
QryUseIndex.zip (Access 2002-2003 形式 - 161kb)
QryUseIndex_2k.zip (Access 2000 形式 - 161kb)

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/440-04a91c70
該当の記事は見つかりませんでした。