クエリのスピードアップ方法

質問

windows2000
access2002

在庫テーブル
　日付、店番号、商品番号、個数
商品マスタ
　商品番号、売価、原価

これらのテーブルを使い以下のクエリ１を作成しました。
　日付、店番号、商品番号、在庫原価:個数*原価
さらにこのクエリを使い以下のクエリ２を作成しました。
　日付、店番号、在庫原価の合計

クエリ１は実行するとサクっと表示されるのですが、クエリ２は実行してから２０秒から３０秒待たないと表示されません。現在のレコード320万件です。この件数は２倍から５倍に増える予定ですので、現時点でこの速度では件数が増えたときが心配です。
しかしテーブルは他のシステムでも使用しているので、手を加えることはできません。
なんとかもう少し早く表示させる方法はないでしょうか。

よろしくお願いします。

回答No.1

O_cyan · ベストアンサー

>さらにこのクエリを使い以下のクエリ２を作成しました
クエリ１を元にクエリ２の集計クエリを作ったという事でしょうか？
クエリ１を解せずテーブルを元に集計クエリを作れば少しは早いと思いますが・・

SELECT 在庫テーブル.日付, 在庫テーブル.店番号, 在庫テーブル!個数*[原価] AS 在庫原価
FROM 商品マスタ INNER JOIN 在庫テーブル ON 商品マスタ.商品番号 = 在庫テーブル.商品番号;
または
SELECT 在庫テーブル.日付, 在庫テーブル.店番号, 在庫テーブル!個数*商品マスタ!原価 AS 在庫原価
FROM 商品マスタ INNER JOIN 在庫テーブル ON 商品マスタ.商品番号 = 在庫テーブル.商品番号;
ではどうでしょうか？
テーブル名等そのまま使えるならクエリのSQLビューにコピーして試して見てください。

回答No.5

Hiroshi101

ヒントだけですがこれは、苦しいですよ。
過去のソフト
基本設計か再検討すべき事案です。
最初のクエリを実行時、320万件すべて一括で処理しているわけではありません。
おそらく数百件程度のレコードぐらいしか処理しません。
必要なところ（表示部前後）のみ処理するのが普通です。

それと異なり、２番目のクエリは、すべてのレコードにアクセス処理が
必要になります。
なんせ累計の計算をしなければなりませんから。

このまま対処をしようとすれば
1.〆の日付（月単位）を決めて月単位の累計テーブルの作成
2.1を利用して、〆以降のみSQLにて作成
3.1と2の数値を合算

もしくは、レコード追加、削除、変更する毎に、
月次累計テーブルへ、数値を計算の後、更新をして
常にこの数値を利用する等

会計ソフトなど、件数が多いソフトでは、月次どころか
日次累計テーブルを作成して速度を上げています。
でも、ＶＢＡの知識がたぶん必要になります。

回答No.4

CHRONOS_0

クエリを重ねると処理量は掛け算で増えますから
どうしても遅くなりますね

避ける方法のひとつは、一つ目のクエリの結果を
一時的ななテーブルとして固定することです
もちろん2つ目のクエリを実行する前には
必ずこの一時テーブルを作成しなおす必要があります

回答No.3

O_cyan

#1です。失礼しました。在庫原価の合計を出すんでしたよね。集計するの忘れてました。
SELECT 在庫テーブル.日付, 在庫テーブル.店番号, Sum(在庫テーブル!個数*商品マスタ!原価) AS 在庫原価
FROM 商品マスタ INNER JOIN 在庫テーブル ON 商品マスタ.商品番号 = 在庫テーブル.商品番号
GROUP BY 在庫テーブル.日付, 在庫テーブル.店番号;
で試してみてください。
Accessなら
SELECT 在庫テーブル.日付, 在庫テーブル.店番号, Sum(在庫テーブル!個数*[原価]) AS 在庫原価
FROM 商品マスタ INNER JOIN 在庫テーブル ON 商品マスタ.商品番号 = 在庫テーブル.商品番号
GROUP BY 在庫テーブル.日付, 在庫テーブル.店番号;
でも行けるはずです。

補足

回答ありがとうございます。
早速試して見ました。
しかし結果は45秒でした。
今までよりも遅かったです。
やはり無理なのでしょうか。

回答No.2

noname#22222

スーツのデザイナーでプログラムに関しては素人ですが...

＜在庫テーブル＞
ID　　　　　日付　　　　　　店番号　　　　　　商品番号 　個数
1　　　　　　2006/06/01　　　　1　　　　　　　　　　　1　　　10
2　　　　　　2006/06/01　　　　1　　　　　　　　　　　1　　　10
3　　　　　　2006/06/01　　　　2　　　　　　　　　　　2　　　20
4　　　　　　2006/06/01　　　　3　　　　　　　　　　　2　　　 1

＜商品マスター＞
商品番号 売価 原価
1 \100 \50
2 \200 \100

＜クエリーの実行結果＞
日付 店番号 在庫原価の合計
2006/06/01 1 1000　　<--- (10+10)*\50=\1,000
2006/06/01 2 2000　　<--- 　(20) *\100=\2,000
2006/06/01 3 100　　<--- 　　　　(1)*\100=\100

SELECT DISTINCTROW 在庫テーブル.日付, 在庫テーブル.店番号, Sum([個数]*[原価]) AS 在庫原価の合計
FROM 商品マスタ RIGHT JOIN 在庫テーブル ON 商品マスタ.商品番号=在庫テーブル.商品番号
GROUP BY 在庫テーブル.日付, 在庫テーブル.店番号;

補足

回答ありがとうございます。
#3の回答と一緒に試して見ました。
しかし結果は50秒でした。
今までよりも遅かったです。
他に方法はないのでしょうか。