#39
標準モジュールに作成されたFunctionプロシージャは、モジュールからの呼び出しだけでなく、クエリの演算フィールドからも呼び出して利用することができます。そして、一般的に使われる"式"を使ったクエリと"プロシージャ"を使ったクエリ、どちらも得られるデータ結果は同じです。しかし、選択クエリのデータシートビューの表示・描画においては、明らかな違いが表れます。プロシージャを使ったクエリでは、１件１件のレコードが徐々に表示されていくのがはっきりと目で確認できます。これは別の言い方とすると、"ダラダラ"と表示されていくという感じで、１レコード描画するごとにプロシージャが呼び出されているという印象を受けます。これに対して"式"を使った方は、すべてのレコードの演算が内部で完了してから一気に描画されるという感じになります。しかもクエリが複雑になればなるほどこの違いは顕著になります。
そこで今回は、画面上の現象ではなく、VBAを使った処理において両者にどのような違いがあるのか、その処理時間に着目して調査してみたいと思います。
まず、テスト用として、下図のようなテーブルを用意しました。ここには、適当な文字データの入った５万件のレコードがあります。
このテーブルに対して、演算フィールドを持った２種類の選択クエリを用意します。一方は式を使った演算、もう一方は標準モジュール上のFunctionプロシージャを呼び出して演算を行うものです。いずれもWhere句の指定やテーブルの結合等は行っていないシンプルなクエリです。
"式"の方については、次のような演算式を設定します。
式1: IIf(IsNull([Data1]),"",IIf(Left$([Data1],1)="A" Or Left$([Data1],1)="B","●●●●",StrConv(Mid$([Data1],10,5),4)))
式2: IIf(IsNull([Data2]),"",IIf(Left$([Data2],1)="A" Or Left$([Data2],1)="B","●●●●",StrConv(Mid$([Data2],10,5),4)))
一方、クエリから呼び出すプロシージャ DataConvert は次のようなコードとなります。
Public Function DataConvert(varData As Variant) As String
If IsNull(varData) Then
DataConvert = ""
ElseIf Left$(varData, 1) = "A" Or Left$(varData, 1) = "B" Then
DataConvert = "●●●●"
Else
DataConvert = StrConv(Mid$(varData, 10, 5), 4)
End If
End Function
"式"の方はIIf関数を、"プロシージャ"の方はIf〜Else構文を使っていますが、これらのクエリの演算フィールドはいずれも『データの値がNULLなら空の文字列を、１文字目が"A"または"B"なら"●●●●"を、それ以外なら１０文字目からの５文字を全角に変換したものを出力する』というものです。どちらもデータシートビューを表示すると右図のようになります。前述のように、描画中の様子は違いますが、描画の完了結果はまったく同じです。さて、テーブルやクエリの準備ができたところで、いくつかのパフォーマンス比較を行ってみることにします。ここでは次のような２つのテストをVBAで行うことにします。
- それぞれのクエリをRecordsetとして開き、全レコード間の移動を行います。
このときのRecordsetのオープンに要する時間とレコード移動に要する時間をそれぞれ測定します。
- DoCmdのOpenQueryメソッドを実行し、それぞれのクエリのデータシートビューを開くための時間を測定します。
まず１つ目のテストです。ここでは次のような２つのテスト用プロシージャを用意して、それぞれを何度か繰り返し実行、その平均時間を求めます。「式を使った演算フィールド」と「プロシージャを使った演算フィールド」がクエリ名です。
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
ts_Watch "処理開始", True
Set rst = dbs.OpenRecordset("式を使った演算フィールド")
ts_Watch "レコードセットオープン"
Do Until rst.EOF
rst.MoveNext
Loop
ts_Watch "レコードセットループ"
End Sub
Sub QryFieldFuncTest2()
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
ts_Watch "処理開始", True
Set rst = dbs.OpenRecordset("プロシージャを使った演算フィールド")
ts_Watch "レコードセットオープン"
Do Until rst.EOF
rst.MoveNext
Loop
ts_Watch "レコードセットループ"
End Sub
実行結果は次のとおりです。いずれも１回当たりの平均処理時間で、単位は秒です。
ご覧のように、両者にはまったく違いはありません。
レコードセットオープン レコードセットループ 式を使った場合 0.02 0.11 プロシージャを使った場合 0.02 0.11
続いて、２つの目のOpenQueryメソッドを使ったテストです。ここでは次のようなテスト用プロシージャを実行します。
ts_Watch "処理開始", True
DoCmd.OpenQuery "式を使った演算フィールド"
ts_Watch "式のテスト"
DoCmd.OpenQuery "プロシージャを使った演算フィールド"
ts_Watch "プロシージャのテスト"
End Sub
これを何回か実行した結果は次のようなものになりました。いずれも単位は秒です。
この結果から、若干の差異ではありますが、プロシージャを呼び出した方が速いということが分かります。
平均 最大 最小 式を使った場合 0.10 0.11 0.09 プロシージャを使った場合 0.08 0.09 0.06
しかしながら、この結果は、目視によるデータシートの描画の状態とは逆の結果になっています。どうやらこのテストコードの場合、VBA上だけの処理時間、つまりOpenQueryメソッドを呼び出し、そこから制御が次のステートメントに戻ってくるまでの時間を示しているようで、描画そのものの時間はここには反映されないようです。つまり、所定のクエリのウィンドウを開いた時点でメソッドそのものの処理は完了し、画面描画は別の次元、つまりWindowsやAccess側の制御で行われていると考えることができそうです。
画面描画についてはこのような文章ではその状況を定量的に示すことはできませんが、少なくても、画面描画における大きな違いを除けば、２つのクエリの作り方による大きな違いはなさそうです。特にそのクエリがVBA上だけで呼び出されるもので、フォームのレコードソースとして画面表示されないような場合には、まったくその違いを意識する必要はないといえます。しかし、テスト前からの現象として分かっていたことですが、フォームのレコードソースとして使うクエリ、つまりユーザーインタフェースに使われるクエリについては、できるだけ"式"を使って演算フィールドを構成した方がスムースな表示となり、よいインタフェースといえるでしょう。もちろんそれがあまりにも複雑な処理の場合には、いや応なしにプロシージャを使う必要がありますが.....。
DataConvert = IIf(IsNull(varData), "", _
IIf(Left$(varData, 1) = "A" Or Left$(varData, 1) = "B", _
"●●●●", _
StrConv(Mid$(varData, 10, 5), 4)))