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

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

前回に引き続き、SQLチューニングネタ。

ID, GroupID, Fld1, ・・・ というフィールドを持つテーブルがあります。GroupID毎にFld1が最大のレコードを抽出したいです。

前回の記事と同様に最大値を持つレコードの抽出ですが、今回は、グループ内での最大値を持つレコードの抽出になります。

例えば、販売履歴から顧客毎の直近の購入データを取得とか、支社毎の販売実績No1の社員の販売データを取得とか、、、いろいろ使える場面は多そうです。

4つの方法を紹介して、それぞれの速度も測定してみました。興味深くて、ちょっと意外な結果になりました。

リンク無し画像

難易度:

テーブル

Table2
フィールドデータ型 
ID数値型主キー
GroupID数値型 
Fld1数値 

上記のようなテーブルに10,000件のレコード(1Group当たり100件、全100Group)をVBAで作成しました。ID は連番、Fld1 は Rnd関数で乱数を生成しました。ちなみにコードは下記のものです。

クエリ(SQL)

4種類のクエリを考えてみました。どれでも結果は同じです。

QryGMax 相関サブクエリでグループ内最大値を求め抽出条件にする

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

QryGNotExists 相関サブクエリで自分より大きいFld1の値がグループ内に存在しないレコードを抽出

QryGMaxJoin 最大値を取得する集計クエリ(サブクエリ)とJOIN

実験方法

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

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

QrySpeedTest2 クエリ名[Enter]

で実行させました。

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

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

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

結果

まずは、ID(主キーフィールド)以外すべてインデックス無し

インデックス無し 単位(ミリ秒)
クエリ名1回目2回目3回目平均
QryGMax123829125276125700124764.5
QryGDMax114270112624113830114050
QryGNotExists85194853448467984936.5
QryGMaxJoin41354342

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

GroupIDインデックス有り 単位(ミリ秒)
クエリ名1回目2回目3回目平均
QryGMax747734734740.5
QryGDMax4718471747244721
QryGNotExists122113114118
QryGMaxJoin32373835

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

GroupID, Fld1インデックス有り 単位(ミリ秒)
クエリ名1回目2回目3回目平均
QryGMax744723739741.5
QryGDMax4734473747544744
QryGNotExists1431145414421436.5
QryGMaxJoin37424440.5

考察

インデックス無しでの実験では、QryGMax は相関サブクエリになるので激遅です。QryGDMaxも若干速いですが誤差範囲内でしょう。QryGNotExistsも相関サブクエリになるので遅いですが、条件に一致したレコードが見つかった時点で読み込み中止するので若干改善されてます。
しかし、QryGMaxJoin は桁違いに速いですね。サブクエリの集計クエリが呼び出されるのは1回のみで、その結果セットと元テーブルとを突き合わせるだけなので一番高速に処理できるだろうと予想してましたが、これほど差がつくとは思いませんでした。

GroupIDインデックス有りにすると、すべてのクエリで高速化されます。適切なインデックスが設定されたテーブルの場合、QryGDMaxが一番遅くなります。D系関数は遅いという定説の裏付けになります。

GroupID, Fld1 両方をインデックス有りにすると、すべてのクエリで若干ですが遅くなります。不必要なインデックスは逆に処理を重くすることもあるということですね。なんでもインデックスを設定すればいいとは限らないことは意識しておく必用があります。

今回は、100件×100グループ の 10,000レコードでの実験でしたが、同じ10,000件でも、例えば、10件×1000グループ や 1000件×10グループ というようにデータ内容が変われば、また異なった傾向の結果が出るかも知れません。時間があればその辺りも実験してみると面白そうですね。

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


拍手する

Leave a reply






Trackbacks

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