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

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

データを抽出する場合、抽出条件を設定するフィールドにインデックスを設定しておくと高速に検索できます。ただし、クエリなど抽出条件を設定するときに条件式によってはインデックスが無効になる場合があります。

基本的なことなんですが、掲示板などでたまにそのような回答を見かけることがありますので、条件を設定するときの注意点を解説します。

インデックスが無効

難易度:

例えば、下記のような仕様があったとします。

パラメータクエリで、年月(yyyy/mm)を指定して該当データを抽出したい。

下記のようにすれば簡単に実現できます。

フィールド  Format([日付],"yyyy/mm")
抽出条件  [年月(yyyy/mm)を入力してください。]

データ数が少なければこれでも問題ないのです。が、このようにフィールドの方に式を設定すると(演算フィールドにすると)、フィールドに設定したインデックスが利用されなくなります。データ数が多く少しでも高速に抽出させたい場合は、式は冗長になりますが、下記のように設定します。

フィールド  日付
抽出条件  >=CDate([年月(yyyy/mm)を入力してください]) And <DateAdd("m",1,[年月(yyyy/mm)を入力してください])

2009/05 というように日を省略して入力した値を日付/時刻型に変換すると、2009/05/01 と一日に変換されますので、それを利用してます。

インデックスを利用したい場合は、このようにクエリのフィールド欄には生のフィールド名を設定するように工夫します。

もう一例。

フォーム上のコントロールを参照して抽出条件とする。ただし、コントロールが未入力の場合は、Nullのデータを含めてすべて表示させたい。
このような仕様の場合。

フィールド  Nz([フィールド1],"")
抽出条件  Nz(Forms!フォーム名!コントロール名,"")

このようにしてしまうとインデックスが無効になります。

フィールド  [フィールド1]
抽出条件  Forms!フォーム名!コントロール名 OR Forms!フォーム名!コントロール名 Is Null

とすればインデックスが有効になります。ただし、クエリのデザインビューでこのように設定するとAccessが勝手にSQL文を書きかえてしまい複雑な条件になる場合がありますので、その場合、デザインビューで設定した後SQLビューに切り替えて保存します。ちなみにSQLビューでは下記のようなWHERE条件式になります。

フィールド名 = Forms!フォーム名!コントロール名 OR Forms!フォーム名!コントロール名 Is Null

もう一つ、曖昧検索で部分一致や後方一致にすると、やはりインデックスは利用されなくなります。利用したい場合は、前方一致にする必要があります。

フィールド  フィールド1
抽出条件  Like "*ABC*"

フィールド  フィールド1
抽出条件  Like "*ABC"

などとするとインデックスは無効になります。データ数が多い場合はこのような条件を設定しなくてもすむようなテーブル設計を検討する必要があります。

拍手する

Leave a reply






Trackbacks

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