こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

-広告-

締切り済みの質問

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

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

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

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

宜しくお願いします。

投稿日時 - 2017-06-11 10:43:45

QNo.9339843

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(5)

ANo.5

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

(Select Sum(売上) From T_売上集計 As tmp売上集計 Where T_売上集計.担当者 = tmp売上.担当者 And T_売上集計.営業日数 <= tmp売上.営業日数)
に置き換えます。

投稿日時 - 2017-06-13 21:16:02

ANo.4

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

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

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

投稿日時 - 2017-06-12 02:23:40

ANo.3

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

投稿日時 - 2017-06-11 23:17:28

ANo.2

【補足:レコードアクセスを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

投稿日時 - 2017-06-11 21:25:00

ANo.1

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

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

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

 私の考えでは、

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

のが筋だと思う。

投稿日時 - 2017-06-11 12:48:13

お礼

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

投稿日時 - 2017-06-11 15:49:52

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-