#28
売上伝票テーブルと売上明細テーブル、入出庫伝票テーブルと入出庫明細テーブルなどのように、伝票ごとの見出しにあたる部分のデータと、その中の明細データとを分けてテーブル設計することはよくあると思います。そしてそれらは、通常、リレーションシップやクエリーによって、一対多の関係で結合されて利用されます。例えば、次の「入出庫伝票」テーブルと「入出庫明細」テーブルの例では、入出庫伝票テーブルには、１伝票１レコードで、伝票ごとのIDとその日付だけが保存されています。また、入出庫明細テーブルには、商品別の発注数や入出庫数が伝票１枚に対して複数レコード保存されています。両者は「伝票ID」フィールドで結合され、一対多の関係を持っています。
 |
 |
 |
|
このような関係を持った２つのテーブルを結合し、しかも、明細データテーブル側のデータを集計するようなクエリーを作る場合、一般的には、通常の選択クエリーを構成したあと、グループ化や合計などの集計クエリーの設定をしていくと思います。しかし、このような機能を持ったクエリーの作り方には、他にもいくつかの方法があります。ここでは、どのような方法があるかピックアップしてみるとともに、それらのうち、どの方法で集計を行うのがもっとも処理が高速か、比較検討してみたいと思います。
今回、最終的に作りたいのは、「入出庫伝票」と「入出庫明細」の２つのテーブルから、次のようなデータを出力する集計クエリーです。入出庫伝票テーブルの「伝票ID」ごとに、それぞれの「発注数」・「入庫数」・「出庫数」の合計を入出庫明細テーブルから集計して一覧にします。
テストパターンとして、次の４つの方法を考えてみました。
| 1.単独の集計クエリーとして作成する方法 | ||
 |
||
| 2.複数のクエリーを使った方法 | ||
まず、入出庫明細テーブルだけを「伝票ID」でグループ化・集計するクエリー（「明細データテーブル集計クエリー」）を作り、最終的に"クエリーのクエリー"として入出庫伝票テーブルと結合する方法です。 |
||
| 3.サブクエリーを使った方法 | ||
 ここでは、「集計クエリー」ではなく、各集計フィールドに次のようなSQL文が入力されています。 発注数: (SELECT SUM(発注数) FROM 入出庫明細 WHERE 伝票ID =[入出庫伝票].[伝票ID]) |
||
| 4.DSum関数を使った方法 | ||
 ここでは、「集計クエリー」ではなく、各集計フィールドに次のような関数式が入力されています。 発注数: DSum("発注数","入出庫明細","伝票ID = " & [入出庫伝票].[伝票ID]) |
||
これらはいずれも最終的に得られるデータは同じものですが、これらのテスト用クエリーを作る段階では、２〜４は作成が面倒そうという印象がきっとあるでしょう。しかし、もしパフォーマンスに大きな違いがあるのであれば、アプリケーションとしては、多少の手間は犠牲にしても、実用面でメリットの高いものを選択した方がよい場合もあります。
そこで、続いて、次のようなVBAのテストコードを使って、各々の実行時間を調べてみたいと思います。
実際のテストでは、４つのクエリーを比較するのはもちろんですが、レコードセットのレコード移動において、さらに２つのケースを考えてみました。１つ目は、単純にクエリーをレコードセットとして開くだけの時間を測定するものです。このテストでは、先頭のレコードをいかに速く得られるかというのがポイントになります。２つ目は、クエリーからレコードセットを開いたあと、最後のレコードまで一気に移動させてみます。テーブルに保存されているレコード数やクエリーの結合の複雑さなどが影響してくると考えられるテストです。
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim strQryName As String
Dim iintLoop As Integer
Set dbs = CurrentDb
ts_Watch "テスト開始", True
strQryName = "単独クエリー集計": GoSub QryTest
strQryName = "複数クエリー集計": GoSub QryTest
strQryName = "サブクエリー集計": GoSub QryTest
strQryName = "DSum集計": GoSub QryTest
Exit Sub
QryTest:
For iintLoop = 1 To 10
Set qdf = dbs.QueryDefs(strQryName)
Set rst = qdf.OpenRecordset()
'２つ目のテストでは次の１行を追加してみます
'rst.MoveLast
rst.Close
qdf.Close
Next iintLoop
ts_Watch strQryName
Return
テスト結果は次のようになりました。
この結果から、気づいた点を列挙してみます。
レコードセットを開くだけ 最終レコードへ移動 単独クエリー集計 3.63 3.52 複数クエリー集計 2.36 3.24 サブクエリー集計 0.11 30.15 DSum集計 0.05 0.06
- 単独クエリーの集計やDSum関数を使った集計では、最終レコードに移動しようとしまいと、処理時間はほぼ同じである・・・・・・レコードセットを開くことによってすべてのレコードがアクセスされるのか？
- 複数クエリーの集計やサブクエリーでは、レコード移動を行うことによって、その分の時間が増える
- サブクエリーを使った集計では、レコード移動による処理時間の増加が非常に顕著である・・・・・・内部的にレコード移動ごとにクエリーが実行され、トータルのSQL処理回数が膨大になるためか？
- DSum関数を使った集計は、他のクエリーに比べて処理時間が極端に少なく、特にレコード移動した場合の優位性は歴然・・・・・・定義域集計関数は一般に遅いといわれるがなぜ？、理屈はサブクエリーと同じだと思うのだが
- DSum関数を使ったクエリー「DSum集計」をデータシートビューで開くと、他のクエリーに比べて非常に画面表示に時間がかかる(１行ずつダラダラと表示されていく)のに、VBAのレコードセットとして扱うとかなり速い
今回のテストに使ったテーブルでは、「入出庫伝票」には4400件のレコード、「入出庫明細」には5000件のレコードが保存されています。平均すると、１伝票あたり1.14件の明細データしかないことになります。一口に一対多とはいっても、１伝票に100件の明細データがある場合もあれば、ほとんど１伝票１明細の場合もあるでしょう。また、トータルのレコード数自体も、数百の場合から数万の場合まであるわけで、そのようにいろいろ状況が変わったとき、この結果がどれだけ変わるかは何ともいえません。しかしもし、処理時間でネックとなる一対多の関係を持ったテーブル間の集計クエリーがあれば、クエリーの作成方法を変えることによって何らかの改善が見られるか、比較・確認してみるのもよいでしょう。
個人的には、集計クエリーの新規作成の容易さ、メンテナンス性などを考えると、やはり一般的な１番目の方法を中心に使うことになると思います。しかしもし、非常に大量のレコードを扱うような処理で、時間がかかって仕方ないと思われるような場面に遭遇したら、上記の他の方法を試してみようと思います。
また、これまでは、大量のレコードを持ったテーブルに対してはその使用を控えていた「定義域集計関数」も、なかなか使えそうであることが分かったので、今後は積極的に使ってみようと思います。特に、今回例として作成したクエリーデザインを見ても分かるように、サブクエリーを使わざるを得ない場面になったとき、そのほとんどは定義域集計関数で代替が利きそうです。総まとめとしては、「状況によって１と４の方法を使い分ける」ということになるのではないでしょうか。
