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

あるフィールドが最大値のレコードを抽出する

NAME_ID, PRICE というフィールドを持つテーブルがあります。PRICE が最大値のレコードを抽出したいです。
SELECT NAME_ID,MAX(PRICE) FROM TABLE_NAME;
ではエラーになりうまくいきません。

という内容の質問がありました。私のも含めて回答としていくつかの方法が出ていました。どれも同じ結果はでるのですが、一番高速なのはどれか、ちょっと興味がありましたので実験してみました。

難易度:

テーブルデータ

Table1
フィールドデータ型 
NAME ID数値型主キー
PRICE数値型 

上記のようなテーブルに100,000件のレコードをVBAで作成しました。NAME_ID は連番、PRICE は Rnd関数で乱数を生成しました。ちなみにコードは下記のものです。

回答

下記の4つのクエリ、どれでも同じ結果が得られます。

QryMax サブクエリでMaxを使い抽出条件にする

QryDMax DMax関数を抽出条件にする

QryTop1 PRICEで降順に並べ TOP 1 を使う

最大値が複数レコードある場合、TOP 1 だと1件だけしか抽出されないように思うかも知れませんが、ちゃんと複数レコード表示されます。1件だけ表示するには、下記のように ORDER BY に主キーを追加します。

QryNotExists 相関サブクエリで自分より大きいPRICEのレコードが存在しないレコードを抽出

実験方法

実験環境
Win7Home Access2007 MDB(2002-2003ファイル形式)
ハード環境は下記を参照
PCを新調しました。

下記のコードを標準モジュールに作成して、イミディエイトウィンドウで

QrySpeedTest クエリ名 [Enter]
で実行させました。

順に4つのクエリを実行し、処理時間を計測。それを3回繰り返し、3回の平均値を求めました。

時間計測には、APIの timeGetTime を使いました。下記のページを参考に私の環境における分解能を計測したところ、1ミリ秒でした。

QueryPerformanceCounterを使用してコードの時間を計測する方法

結果

まずは、PRICEフィールドの「インデックス」が「いいえ」でテストしてみました。

インデックス無し 単位(ミリ秒)
クエリ名1回目2回目3回目平均
QryDMax182175169175.5
QryMax177174179178
QryTop1240229231235.5
QryNotExists2032203320372034.5

PRICEフィールドの「インデックス」を「はい(重複あり)」に変更してテスト。

インデックス有り 単位(ミリ秒)
クエリ名1回目2回目3回目平均
QryDMax1191010.5
QryMax8898.5
QryTop1218231230224
QryNotExists351356354352.5

考察

インデックス無しでの実験で、QryDMaxが最速なのはちょっと意外でしたが、ほぼ、誤差範囲で、QryMax と同等とみていいでしょう。DMax関数は重い関数と言われてますが、今回は呼び出されるのは1回のみなのでほとんど影響しなかったのでしょう。

QryNotExists は、サブクエリの方から親クエリを参照しているので相関サブクエリになります。相関サブクエリは親クエリのレコードの件数分呼び出されますので、速度的には不利です。Exists述語はTrueになった時点で問い合わせを止めるので高速と言われてますが、サブクエリ内での検索数は減っても、親レコード件数分呼び出されるのはつらいようですね。

インデックス有りにすると全体的に高速になってます。特に、QryDMax, QryMax は劇的に高速化されてます。最大値を取得するという処理がインデックスを最大限有効利用できるからでしょう。

QryNotExistsもインデックス効果でかなり高速化されてますが、いかんせん、元が重いので、高速化されても他のを追い越すところまでは行きませんでした。

意外だったのが、QryTop1 でインデックス効果がほとんど出なかったことです。ORDER BY での並べ替えはインデックスを利用すれば高速化するはずなんですが、TOP 1 なのでそれほど影響がないということでしょうか。TOP 100 とかだったら差が出るかも知れません。

今回の結果は、あくまで、当方の環境、当方が用意したデータでの結果ですので、参考程度にとどめてください。
データ件数や内容、DBエンジンが異なったりすると結果が変わることは十分あり得ることです。


拍手する

Leave a reply






Trackbacks

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