一対多の結合の、明細データテーブルの集計クエリーでは、単独の集計クエリーがその作成の容易さ・メンテナンス性の点ではよいものの、パフォーマンスとしては、DSum関数を使ってフィールド単位で集計値を求める方法が優れている。サブクエリーを使った方法は、レコード数の影響を非常に受け、パフォーマンスは悪い。売上伝票テーブルと売上明細テーブル、入出庫伝票テーブルと入出庫明細テーブルなどのように、伝票ごとの見出しにあたる部分のデータと、その中の明細データとを分けてテーブル設計することはよくあると思います。それらは、通常、リレーションシップやクエリーによって、一対多の関係で結合されて利用されます。次の「入出庫伝票」テーブルと「入出庫明細」テーブルの例では、入出庫伝票テーブルには、1伝票1レコードで、伝票ごとのIDとその日付だけが保存されています。入出庫明細テーブルには、商品別の発注数や入出庫数が伝票1枚に対して複数レコード保存されています。両者は「伝票ID」フィールドで結合され、一対多の関係を持っています。このような関係を持った2つのテーブルを結合し、しかも、明細データテーブル側のデータを集計するようなクエリーを作る場合、一般的には、通常の選択クエリーを構成したあと、グループ化や合計などの集計クエリーの設定をしていくと思います。このような機能を持ったクエリーの作り方には、他にもいくつかの方法があります。ここでは、どのような方法があるかピックアップしてみるとともに、それらのうち、どの方法で集計を行うのがもっとも処理が高速か、比較検討してみたいと思います。今回、最終的に作りたいのは、「入出庫伝票」と「入出庫明細」の2つのテーブルから、次のようなデータを出力する集計クエリーです。合計を入出庫明細テーブルから集計して一覧にします。テストパターンとして、次の4つの方法を考えてみました。1.単独の集計クエリーとして作成する方法2.複数のクエリーを使った方法まず、入出庫明細テーブルだけを「伝票ID」でグループ化・集計するクエリー(「明細データテーブル集計クエリー」)を作り、最終的に"クエリーのクエリー"として入出庫伝票テーブルと結合する方法です。3.サブクエリーを使った方法ここでは、「集計クエリー」ではなく、各集計フィールドに次のようなSQL文が入力されています。=[入出庫伝票].[伝票ID])4.DSum関数を使った方法ここでは、「集計クエリー」ではなく、各集計フィールドに次のような関数式が入力されています。[入出庫伝票].[伝票ID])これらはいずれも最終的に得られるデータは同じものですが、これらのテスト用クエリーを作る段階では、2〜4は作成が面倒そうという印象がきっとあるでしょう。パフォーマンスに大きな違いがあるのであれば、アプリケーションとしては、多少の手間は犠牲にしても、実用面でメリットの高いものを選択した方がよい場合もあります。そこで、続いて、次のようなVBAのテストコードを使って、各々の実行時間を調べてみたいと思います。実際のテストでは、4つのクエリーを比較するのはもちろんですが、レコードセットのレコード移動において、さらに2つのケースを考えてみました。1つ目は、単純にクエリーをレコードセットとして開くだけの時間を測定するものです。このテストでは、先頭のレコードをいかに速く得られるかというのがポイントになります。2つ目は、クエリーからレコードセットを開いたあと、最後のレコードまで一気に移動させてみます。テーブルに保存されているレコード数やクエリーの結合の複雑さなどが影響してくると考えられるテストです。strQryNameReturnテスト結果は次のようになりました。単独クエリー集計3.633.52複数クエリー集計2.363.24サブクエリー集計0.1130.15DSum集計0.050.06この結果から、気づいた点を列挙してみます。一般に遅いといわれるがなぜ?、理屈はサブクエリーと同じだと思うのだがDSum関数を使ったクエリー「DSum集計」をデータシートビューで開くと、他のクエリーに比べて非常に画面表示に時間がかかる(1行ずつダラダラと表示されていく)のに、VBAのレコードセットとして扱うとかなり速い今回のテストに使ったテーブルでは、「入出庫伝票」には4400件のレコード、「入出庫明細」には5000件のレコードが保存されています。平均すると、1伝票あたり1.14件の明細データしかないことになります。一口に一対多とはいっても、1伝票に100件の明細データがある場合もあれば、ほとんど1伝票1明細の場合もあるでしょう。トータルのレコード数自体も、数百の場合から数万の場合まであるわけで、そのようにいろいろ状況が変わったとき、この結果がどれだけ変わるかは何ともいえません。処理時間でネックとなる一対多の関係を持ったテーブル間の集計クエリーがあれば、クエリーの作成方法を変えることによって何らかの改善が見られるか、比較・確認してみるのもよいでしょう。個人的には、集計クエリーの新規作成の容易さ、メンテナンス性などを考えると、やはり一般的な1番目の方法を中心に使うことになると思います。非常に大量のレコードを扱うような処理で、時間がかかって仕方ないと思われるような場面に遭遇したら、上記の他の方法を試してみようと思います。使えそうであることが分かったので、今後は積極的に使ってみようと思います。今回例として作成したクエリーデザインを見ても分かるように、サブクエリーを使わざるを得ない場面になったとき、そのほとんどは定義域集計関数で代替が利きそうです。総まとめとしては、「状況によって1と4の方法を使い分ける」ということになるのではないでしょうか。