クエリの計算時間についてです

質問

access2007で売上を管理するDBを作成しています。
クエリで消費税を表示させるために

(1)税: DMax("税率","T_消費税","施行日付 <=#" & [日付] & "#")の場合と
(2)税: 消費税([日付])

　Function 消費税(日付 As Date)
'渡された日付の時点での消費税率を出します。
Dim Rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT Top 1 T_消費税.税率 " & _
"FROM T_消費税 " & _
"WHERE (((T_消費税.施行日付) <= #" & 日付 & "#)) " & _
"ORDER BY T_消費税.施行日付 DESC;"
Rs.Open strSQL, CurrentProject.Connection
If Rs.EOF Then
消費税 = 0
Else
消費税 = Nz(Rs![税率], 0)
End If
Rs.Close
Set Rs = Nothing
End Function

の場合を作成し、クロス集計で顧客別・月別売上を表示させたところ
(1)の場合は≒6秒
(2)の場合は≒17秒かかってしまいます。
どういう考え方が現実的ではないか指摘願えませんでしょうか？
データは1万件くらいのものです。
使用環境はXPです。

回答No.4

30246kiku

どうも　＃２です

> 必要であれば、VBA 記述になりますが、例は提示できます。
迷惑かと思いますが、小出しにするのではなく、全部出し切ってみます。
（以下は用意していたものになります）

最速の方法ではないことは、言っておきます。
今後、修正等あった場合、修正範囲を極力一箇所に集約することを考えたものです。
（税率を扱う箇所が一箇所であれば、あまり意味のないものになります）

以下の説明に従って、テスト環境を用意してください。

「T消費税」テーブル（内容が正しいかは別にして）

適用日　　　　税率
1989/04/01　　3.00%
1997/04/01　　5.00%

適用日は、日付/時刻型
税率は、通貨型（書式：パーセント）

「T消費税」 の内容をメモリに展開する関数 TaxInit と、
日付を元に税率を求める関数 GetTax を用意します。

以下を標準モジュールに記述します。

Dim dic As Object

Public Function TaxInit() As Boolean
　　Dim rs As New ADODB.Recordset

　　On Error Resume Next
　　If (dic Is Nothing) Then
　　　　Set dic = CreateObject("Scripting.Dictionary")
　　End If
　　dic.RemoveAll

　　rs.Source = "SELECT 適用日, 税率 FROM T消費税 ORDER BY 適用日 DESC;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　While (Not rs.EOF)
　　　　dic.Item(rs(0).Value) = rs(1).Value
　　　　rs.MoveNext
　　Wend
　　rs.Close
　　TaxInit = True
End Function

Public Function GetTax(dt As Variant) As Currency
　　Dim v As Variant

　　On Error Resume Next
　　GetTax = 0
　　If (Not IsDate(dt)) Then Exit Function
　　If (dic Is Nothing) Then Exit Function
'　　If (dic Is Nothing) Then Call TaxInit ' 手順ミス？もう一度読み込んでみる
　　For Each v In dic.Keys
　　　　If (v <= dt) Then
　　　　　　GetTax = dic.Item(v)
　　　　　　Exit For
　　　　End If
　　Next
End Function

適用日、税率の管理に Dictionary を使用します。
（この方法以外にも配列を使って・・・・（上記より速くなる？）等あると思います）
TaxInit が呼ばれた時、Dictionary の内容を作成していきます。
適用日をキーとし、内容は税率とします。
Recordset を使って求めるのは、ここ１回です。
戻り値は、無条件で True を設定しておきます。

GetTax では、作成されていた Dictionary の内容をみて税率を戻すだけです。
> '　　If (dic Is Nothing) Then Call TaxInit ' 手順ミス？もう一度読み込んでみる
現在はコメントですが、考え方だと思います。
（有効にするのなら、前の行はコメントに）

確認用のテーブル「T消費税テスト」を以下内容で作成します。
an ：オートナンバ（主キー）
売上日 ：日付/時刻型　書式：日付（S)

確認のためのデータを入れておきます

an　売上日
1　1989/03/31
2　1989/04/01
3　1997/03/31
4　1997/04/01
5　
6　2012/11/12

上記 5 のところは、
１度日付を入れて確定した後、戻って「DEL」キーで削除します。

クエリデザインから「T消費税テスト」を表示して作成します。
an、売上日 を表示するようにし、その隣のフィールドに以下を記述します。

税率:GetTax([売上日])

そして、書式をパーセントにします。
クエリの表示を SQLビューに変更すると以下のようになっていると思います。

SELECT T消費税テスト.an, T消費税テスト.売上日, GetTax([売上日]) AS 税率
FROM T消費税テスト;

これに手を加えます。

SELECT T消費税テスト.an, T消費税テスト.売上日, GetTax([売上日]) AS 税率
FROM T消費税テスト WHERE TaxInit();

この状態で表示を「データシートビュー」に変更し、表示を確認します。

an　売上日　　　　税率
1　1989/03/31　　0.00%
2　1989/04/01　　3.00%
3　1997/03/31　　3.00%
4　1997/04/01　　5.00%
5　　　　　　　　　　　0.00%
6　2012/11/12　　5.00%

上記のような表示になると思います。

WHERE に引数を持たない関数を記述すると処理する初めに呼ばれるようです。
TaxInit は WHERE で記述するので、True を無条件に戻すようにした理由はここからです。
なので、クエリが実行された時、メモリに展開しておいて、後の GetTax では参照するだけ。

ここで応用ですが、「T消費税」の内容は頻繁に変わるものではないと思います。
であれば、TaxInit の実行は Access を立ち上げた時に１回

Call TaxInit

としておくことで、税率を管理する Dictionary は完成するので、後のクエリでは
WHERE TaxInit() を記述する必要はなくなります。

また、GetTax 内の
> '　　If (dic Is Nothing) Then Call TaxInit ' 手順ミス？もう一度読み込んでみる
を有効としていた場合、上記手順は要らない、と言えば要らないものです。

例としては以上です。

※ 関数名等、適宜変更してください

回答No.3

nicotinism

少し私も混ぜてもらえますか？
30246kikuさんが参戦されているので安心してポカできます (^^ゞ

Dmax の件は私も同じ考えです。
Function 消費税・・では
税を求めたい各レコードごとに
Adodb.recordset を開いて －１
値を得て
Adodb.recordset を閉じて －２
の繰り返しになるので、１と２の部分が足を引っ張っているのかと。
結果的にDmax関数よりも遅くなっている。。

今後の消費税率変更を考えなくても良いなら
(変更があればクエリも修正しなくてはなりません）
税:ccur(Nz(Switch([日付] >= #2012/1/1#,0.05,[日付] >= #2008/1/1#,0.03),0))
のようにすれば最速かも？（施行開始日は適当です）

T_消費税 を元に考えると
税:CCur(Nz((SELECT TOP 1 T_消費税.税率 FROM T_消費税 WHERE T_消費税.施行日付 <= テーブル名.日付 ORDER BY T_消費税.施行日付 DESC),0))
とか？
かえって遅くなったらゴメン。

回答No.2

30246kiku

処理時間については、どのあたりが適当・・・という事はわかりませんが、
条件を同じにして、再測定されてみてはいかがでしょうか。

現状(1)では、税率が下がった場合には対応できていないと思われます。
DMax で　施行日付 を求め、
その求まった 施行日付 を使って、DLookup で税率を求めるのだと思われます。

税率は上がるもんだ・・・を前提にすれば
(2)のSQL部分を DMax と同じにしてみてはどうでしょう。

SELECT Max(T_消費税.税率) AS 税率 FROM T_消費税
WHERE 施行日付 <= #" & 日付 & "#;"

※ 速くする手法は、いろいろあるんだろうと思いますが
今回の場合、レコード数は２（近い将来４）程度と思われることから、
毎回 Recordset で求めるのではなく、初期時にメモリに読み込んでおいて
後は、その読み込んでいた内容と比較する様にすれば良さそうな気がします。
必要であれば、VBA 記述になりますが、例は提示できます。

なお、速さを追求するものでないのなら、(2)で良いと思います。
私もその方法を提示した事があります。

取引先の消費税の計算方法を四捨五入と切り捨てに区別
http://oshiete1.goo.ne.jp/qa5811666.html

の以下の書き出しで始まる箇所にて

＃１４です（長くなったので２つに分けます）

※ 提示したからと言って、それが正しいものとは言っていないので
　　全ての判断は質問者さんが行ってください。

回答No.1

nag0720

クエリがどのように実行されるのか内部まで知っているわけではないので、単なる予想です。

SELECT Top 1 T_消費税.税率
・・・・・
ORDER BY T_消費税.施行日付 DESC

これは結果は最大値を求めるDMaxと同じですが、内部の処理としては、
Top 1であろうとTop 100であろうとアルゴリズムに大きな違いはないと思いますから、
「ORDER BY T_消費税.施行日付 DESC」によるソートが必要となります。

一方、DMaxは最大値だけを求める関数ですから、ソートは必要なく単に全データの税率を見るだけで済みます。

このソートするかしないかの違いではないでしょうか。