#24
Accessをはじめとする「データベース」というジャンルに属するソフトの最も得意とするところが、レコードの抽出や並べ替え、テーブル結合といったSQLによるデータ操作です。Accessの場合、これをGUIで簡単に操作できるようにしているのが「クエリー」です。SQLという言語を意識せずマウス操作だけでデータベースエンジンに対して発行するSQLを組み立てて、データベースウィンドウに表示されるデータベースの１つのオブジェクトとして保存することができます。この保存済みのクエリーは単独のクエリーオブジェクトとしてデータシートビューのような方法で動作させるだけでなく、フォームやレポートのレコードソース、あるいはコンボボックスなどのコントロールソースとしても利用することができます。またVBAにおいては、そのクエリーからレコードセットを開くことによってあらかじめ抽出・並べ替えなどの操作がなされたレコードを取得することができます。
しかし、テーブル全体ではなく、条件に合った一部のレコードだけをVBAで扱う方法は、この保存済みクエリーだけに限られているわけではありません。保存済みクエリーを使った方法も含めて次のような方法があります。
- 保存済みクエリーを使った方法(WHERE条件をクエリーに埋め込んでおく)
- 保存済みのパラメータクエリーを使った方法(WHERE条件をVBAのコード上で指定する)
- SQLステートメント自体をVBAから発行する方法
- RecordsetオブジェクトのFilterプロパティを使った方法
いずれの方法もテーブルに対して何らかのSQLを発行するのは同じです(FilterプロパティについてはSQLという表現は適切でないかもしれません)。しかし、実行するタイミングあるいはSQLが解釈されるタイミングが微妙に異なるため、パフォーマンスにも何らかの違いが出てくることが想像されます。そこで今回はこの４つの方法についてその実行時間を測定・比較してみることにしました。
テストに使うコードは次のようなものです。レコードセットの開き方に違いはありますが、その後のレコードを読み込むループの部分はすべて同じものです。総レコードが１万件のテーブルから千件のレコードだけが均一に抽出されるよう、あらかじめデータの内容を調整してあります。
Dim dbs As Database
Dim rst As DAO.Recordset
Dim rstF As DAO.Recordset
Dim qdf As QueryDef
Dim varDummy As Variant
Dim strSQL As String
Set dbs = CurrentDb
ts_watch "テスト開始", True
'【保存済みクエリーを使った方法】
Set rst = dbs.OpenRecordset("テストクエリー")
GoSub ReadLoop
rst.Close
ts_watch "保存済みクエリーで開く"
'【保存済みのパラメータクエリーを使った方法】
Set qdf = dbs.QueryDefs("テストパラメータクエリー")
With qdf
.Parameters("Where Value") = 5
Set rst = .OpenRecordset()
GoSub ReadLoop
rst.Close
.Close
End With
ts_watch "保存済みパラメータクエリーで開く"
'【SQLステートメント自体をVBAから発行する方法】
strSQL = "SELECT * FROM tblTest WHERE Data2 = 5"
Set rst = dbs.OpenRecordset(strSQL)
GoSub ReadLoop
rst.Close
ts_watch "SQLステートメントで開く"
'【Filterプロパティを使った方法】
Set rstF = dbs.OpenRecordset("tblTest", dbOpenDynaset)
rstF.Filter = "Data2 = 5"
Set rst = rstF.OpenRecordset()
GoSub ReadLoop
rst.Close
ts_watch "Filterで開く"
dbs.Close
Exit Sub
ReadLoop:
With rst
Do Until .EOF
varDummy = !Data1
varDummy = !Data2
varDummy = !Data3
varDummy = !Data4
.MoveNext
Loop
End With
Return
テスト結果は次の通りです。
処理時間(Sec) 1.保存済みクエリーを使った方法 1.27 2.保存済みのパラメータクエリーを使った方法 1.31 3.SQLステートメント自体をVBAから発行する方法 1.28 4.Filterプロパティを使った方法 1.40
差異としては1/100秒単位のわずかなものですが、その差を分析してみることにします。まず、最も速かったのが「保存済みクエリーを使った方法」です。クエリーオブジェクトを保存しておくことによってSQLステートメントの解釈にかかる時間が高速化されていることの表れと考えられます。「SQLステートメント自体をVBAから発行する方法」もこれとほぼ同じ時間で処理されています。今回のテストケースは非常に単純なSQLではありますが、少なくともこのようなケースでは保存されたクエリーもVBAからSQLステートメントの文字列を発行するのも同等と考えてよいでしょう。パフォーマンスではなく、使い勝手の面でそれぞれのメリット・デメリットを考えて使い分ければよいようです。
- "SELECT * FROM 〜"や"DELETE * FROM 〜"のような簡単なステートメントは、VBAのコードに記述した方がプログラムの見通しがよい
- 簡単なSQLのクエリーは、モジュール内に置くことによってクエリーオブジェクトを減らせる(やたらクエリーを増やさずに済む)
- 複雑なクエリーはクエリーオブジェクトとして保存しておいた方がクエリー構造や抽出されたレコード内容を確認するのが容易である
- 複数のテーブルを結合するクエリーはGUIで作る方がはるかに楽である(もっとも、こうして作ったクエリーのSQLビューの内容をモジュールに貼り付けることもできますが)
さて、WHERE条件を埋め込んだ保存済みクエリーと同レベルのパフォーマンスを期待していたのに、意外と時間がかかったのが「保存済みのパラメータクエリーを使った方法」です。クエリーオブジェクトの内容を取得し(Set qdf = dbs.QueryDefs〜)、パラメータ値を設定するという処理の分、余分に時間がかかっているのかもしれません。しかしコーディングの面からいえばSQLステートメントのWHERE句を文字列データとして組み立てる必要が要らないという点で、やはり便利な方法ではないでしょうか。
最後に、最も時間がかかったのが「Filterプロパティを使った方法」です。この方法ではテーブルすべてのレコードを持ったRecordsetオブジェクトを生成し、そこからさらにフィルタのかかった別のRecordsetオブジェクトを生成するという二重の処理を行っているわけですから、これはやはりやむを得ない結果でしょう。今回の場合では、１万件のレコードセット＋千件のレコードセットを作っていることになります。しかしこのFilterプロパティのデメリットと思える点も、さまざまなフィルタ(WHERE条件)を順次切り替えていくような処理や、レコードを絞り込んでいくような処理ではむしろメリットとして活かせるのではないでしょうか。
さて、今回はDAOで行ったテストの結果だけを記載しましたが、ADOについても同じテストを試みました。いずれも1.69秒〜1.70秒という結果で、DAOよりはかなり遅いものの、どんな方法でも処理時間が安定しているというのが特徴的です。ADOで特に有用なのが「Filterプロパティを使った方法」です。ADOではDAOのようにRecordsetオブジェクトからフィルタをかけた別のRecordsetオブジェクトを生成する必要がありません。最初に作ったRecordsetオブジェクトのFilterプロパティを設定することによって、そのレコードセット自体がフィルタのかかった状態になるのです。ADOでのこの方法は他の方法に比べてのパフォーマンスの劣化もないので、大いに使える方法ではないでしょうか？。
