access vba 累計計算について教えてくださ

質問

クエリで以下の関数をフュールドに入力しているのですが、
レコードが10万ほどあり非常に遅く5時間以上計算にかかります。
一度VBAでも試したいのですが、記述がわかりません。
どなたか教えていただけないでしょうか？

dsum("売上","T_売上集計","担当者='" & 担当者 & "' And 営業日数<=" & 営業日数)

売上　数値
担当者　テキスト
営業日数　数値
です。

宜しくお願いします。

回答No.5

magical_bit

他の方が回答済みですが念のため、そもそもdsumのような定義域集計関数というのは遅いです。
こういうのはサブクエリを使うと速くできます。
たとえば、この質問の場合、
dsum("売上","T_売上集計","担当者='" & 担当者 & "' And 営業日数<=" & 営業日数)
を
(Select Sum(売上) From T_売上集計 As tmp売上集計 Where T_売上集計.担当者 = tmp売上.担当者 And T_売上集計.営業日数 <= tmp売上.営業日数)
に置き換えます。

回答No.4

chayamati

>レコードが10万ほどあり非常に遅く5時間以上計算にかかります。
どうしてでしょうか、
10万レコードものテーブルはないので下記より取込み(45,586レコード）
3回重ねて136,758レコードでクロス集計クエリー(集計と平均）で試してみましたが1秒以内で結果が出ました。
http://www.mhlw.go.jp/topics/bukyoku/syakai/soren/50onjun/h03/index.html
クエリは以下です。
SELECT 死亡者名×３.出身地ID, Sum(死亡者名×３.通番) AS 通番の合計, Avg(死亡者名×３.通番) AS 通番の平均
FROM 死亡者名×３
GROUP BY 死亡者名×３.出身地ID
ORDER BY 死亡者名×３.出身地ID;

こちら東芝のデスクトップ（REGZA D731/T7DB)メインメモリー４GB WINDOSE10 ACCESS2010

体験上ですが、何度も修正していると修正履歴が残るのかファイルが肥大化するようです。
空のACCESSファイルを作りこれにインポートすると早くなるかも

回答No.3

山田 太郎（@f_a_007）

画像を添付するのを忘れていました。

回答No.2

山田 太郎（@f_a_007）

【補足：レコードアクセスを10万に留める以外になし】

・001件目のデータを検索。
・001件目のDSum()が走る。対象は10万レコード。
・001件目のデータを表示。
　↓
・002件目のデータを検索。
・002件目のDSum()が走る。対象は10万レコード。
・002件目のデータを表示。
　↓
・999件目のデータを検索。
・999件目のDSum()が走る。対象は10万レコード。
・999件目のデータを表示。

ここまででDSum()が集計対象としたレコード数は99,900,000レコードになります。こういうやり方だと処理時間は途方もないものになるのは必定です。そこで、レコードアクセス回数を最低限に留めてスピードアップを図ります。その場合、仮に、検索し表示するレコード数が1,000件だとすると。その処理時間はXX分の世界になります。

さて、レコードアクセス回数を最低限に留める唯一の方法は、テーブル全体を構造体変数（ユーザ定義変数）に落とすことです。その後は、完全にメモリ上での処理。ですから一件当たりの集計は0.001秒以下で行われます。また、集計後に一気に表示するので、これも時間短縮に貢献します。フォームには、進行ゲージを表示しておけばユーザも待てると思いますよ。

ちなみに、DAOやADOで集計関数の高速化を試みても、《99,900,000レコードが集計対象》であることは一緒。ですから、まったく効果はないと思いますよ。ちなみに、次のADO関数を試されてみて下さい。50歩100歩だと思います。DSum()とDlookup()の違いは、後者はテーブルをダイレクトに集計することです。その場合、DLookup()にSUM()を書いた方が3倍速という結果を得ます。でも、そんな小手先の改善は焼け石に水。レコードアクセス回数を最低限に留めるという対策に踏み出さないと実用性は担保されないと思いますよ。

まあ、70歳になる爺の戯言ですが・・・。

Public Function sumSeek(ByVal strstrSQL As String, ByVal strSumField As String) As Double
On Error GoTo Err_sumSeek
　　Dim stopNow As Boolean
　　Dim db　　　As DAO.Database
　　Dim rs　　　As DAO.Recordset
　　Dim dblSum　As Double

　　Set db = CurrentDb
　　Set rs = db.OpenRecordset(strstrSQL, 2)
　　rs.MoveFirst
　　If Not rs.NoMatch Then
　　　　Do
　　　　　　dblSum = dblSum + Nz(rs.Fields(strSumField))
　　　　　　rs.MoveNext
　　　　Loop Until rs.EOF
　　End If
Exit_sumSeek:
On Error Resume Next
　　rs.Close
　　Set rs = Nothing
　　sumSeek = dblSum
　　Exit Function
Err_sumSeek:
　　MsgBox "実行時エラーが発生しました。(sumSeek)" & Chr(13) & Chr(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr(13), vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_sumSeek
End Function

Public Function DBLookup(ByVal strQuerySQL As String, _
　　　　　　　　　　　　　Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim rst As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

回答No.1

山田 太郎（@f_a_007）

Q、どなたか教えていただけないでしょうか？
A、無理。

　答えは、一番低レベルなSEEKを利用してDSum()の代替関数を作成することだろうが・・・。でも、それで劇的に改善されることはないのでは・・・。

　次善の策は、ADOを利用したDSum()の代替関数。これはSEEKほどの改善は期待できないが、少ないレコードでは３倍速が確認されている。が、そもそも５時間の原因が不明では、これも怪しいもの。

　私の考えでは、

１、クエリの利用をすべて中止する。
２、テーブル設計を最適化する。
３、処理1分以内を目指した改善策を立案・実行する。

のが筋だと思う。

お礼

ご回答ありがとうございます。
ただ、今の私の質問レベルの回答では無いため、正直Fa_007さんの
言っている事が全く分かりません。