Access Labo

#35 定義域集計関数とSQLの比較

結論
定義域集計関数の処理は低速であるが、Databaseオブジェクトを毎回生成するような代替関数を使うよりは高速である。ただし、Databaseオブジェクトの生成にはかなりのオーバーヘッドがかかるので、それさえうまく回避できれば、定義域集計関数を使うよりも代替のSQLを使ってレコードセットから結果を得た方が高速に処理できる。

DLookupやDCount、DSumなどの関数は「定義域集計関数」と呼ばれ、一般的には低速な関数といわれています。一方それらの関数は、すべてまったく同等の処理をSQLで行なうことができます。ここでは、それらを実際に比較することによって、本当に定義域集計関数が遅いのか、またSQLによるものとどれくらい違うのかを確認してみたいと思います。

ここでは、DLookup関数とDCount関数の２つについて比較してみます。テストパターンとして次のような３つを用意しました。

  1. 通常の定義域集計関数を実行した場合

  2. 定義域集計関数と同等の、SQLによる代替のオリジナル関数を呼び出した場合（Databaseオブジェクトの生成も毎回行なう）

  3. 代替のSQL文を関数化せずに同じプロシージャ内で実行した場合（Databaseオブジェクトの生成は一度だけ行なう）

それぞれのテストパターンについて、同じ呼び出しをループで１万回ずつ行ない、その総処理時間を比較するものとします。

テスト用のコードは次のようなものです。


Sub Test()
'テストのメインプロシージャ

  Dim dbs As Database
  Dim rst As Recordset
  Dim strSQL As String
  Dim lngID As Long
  Dim iintLoop As Integer

  Set dbs = CurrentDb

  'DLookup関数のテスト
  For iintLoop = 1 To 10000
    lngID = DLookup("顧客ID", "顧客マスタ", "会社名='日本商事'")
  Next iintLoop

  'DLookup代替関数のテスト
  For iintLoop = 1 To 10000
    lngID = DLookupTest()
  Next iintLoop

  'SQLによるDLookup代替テスト
  strSQL = "SELECT 顧客ID FROM 顧客マスタ WHERE 会社名='日本商事'"
  For iintLoop = 1 To 10000
    Set rst = dbs.OpenRecordset(strSQL)
    lngID = rst!顧客ID
    rst.Close
  Next iintLoop


  'DCount関数のテスト
  For iintLoop = 1 To 10000
    lngID = DCount("顧客ID", "顧客マスタ", "会社名='日本商事'")
  Next iintLoop

  'DCount代替関数のテスト
  For iintLoop = 1 To 10000
    lngID = DCountTest()
  Next iintLoop

  'SQLによるDCount代替テスト
  strSQL = "SELECT Count(*) AS RecCnt FROM 顧客マスタ WHERE 会社名='日本商事'"
  For iintLoop = 1 To 10000
    Set rst = dbs.OpenRecordset(strSQL)
    lngID = rst!RecCnt
    rst.Close
  Next iintLoop

End Sub


Function DLookupTest() As Long
'DLookup代替関数

  Dim dbs As Database
  Dim rst As Recordset
  Dim strSQL As String

  Set dbs = CurrentDb
  strSQL = "SELECT 顧客ID FROM 顧客マスタ WHERE 会社名='日本商事'"
  Set rst = dbs.OpenRecordset(strSQL)
  DLookupTest = rst!顧客ID
  rst.Close

End Function


Function DCountTest() As Long
'DCount代替関数

  Dim dbs As Database
  Dim rst As Recordset
  Dim strSQL As String

  Set dbs = CurrentDb
  strSQL = "SELECT Count(*) AS RecCnt FROM 顧客マスタ WHERE 会社名='日本商事'"
  Set rst = dbs.OpenRecordset(strSQL)
  DCountTest = rst!RecCnt
  rst.Close

End Function


メインプロシージャ「Test」で各テストパターンの呼び出しを行ないます。またこのプロシージャ内では、３番目のテストパターン用に、CurrentDb関数を使ってDatabaseオブジェクトを生成しています。この部分の生成時間はテスト結果には含めません。

一方、「DLookupTest」と「DCountTest」の２つのFunctionプロシージャは、それぞれDLookup関数、DCount関数と同等の処理行なうもので、SQL文によってそのレコードセットを生成し、得られた１レコードのフィールド値を返します。これらのプロシージャでは、呼び出されるたびにCurrentDb関数によってDatabaseオブジェクトが生成され、その処理時間もテスト結果に含まれることになります。なお、ここで用いられているSQL文はあらかじめテーブル名やWhere条件が固定されており、あくまでもテスト用に簡略されたもので、厳密にはDLookup関数などの代替とはいえません。定義域集計関数のように、さまざまなテーブル名やWhere条件に対応させるとそれなりに処理が増えますので、正確には実行結果の時間が増える可能性があります。


テストの実行はメインプロシージャ「Test」を１回実行するだけです。そしてその結果は次のようになりました。
テストパターン 実行時間（秒）
DLookup DLookup関数 28.8
DLookupの代替関数 34.3
SQLによるDLookupの代替 18.1
DCount DCount関数 29.9
DCountの代替関数 34.3
SQLによるDCountの代替 18.3
ご覧のように、明らかな時間の差が出ています。もっとも高速なのはDatabaseオブジェクトの生成を１回だけ実行した場合のSQLによる代替処理です。それに比べると時間はかなりかかっていますが、２番目に速いのは通常の定義域集計関数でした。定義域集計関数よりは速いのではないかと予想していた独立したプロシージャの代替関数は、もっとも遅いという結果になりました。

これらのことから、確かに定義域集計関数の処理は低速であるが、Databaseオブジェクトを毎回生成するような代替関数を使うよりは高速であるということがいえます。また、Databaseオブジェクトの生成にはかなりのオーバーヘッドがかかるようで、それさえうまく回避できれば、定義域集計関数を使うよりも代替のSQLを使ってレコードセットから結果を得た方がかなり高速に処理できるということも分かります。上記の「Test」プロシージャのように、CurrentDb関数を１回使ったあと、さまざまな定義域集計関数を使いたいようなケースでは、SQL文を使った方法がかなり有効です。

また、どうしてもFunctionプロシージャ化された代替関数を使いたい場合には、上記コードでの「dbs」という変数をPublicな変数として宣言し、一連の処理を実行する前にその値を設定しておくことで、対処可能です。もちろん、各Functionプロシージャ内ではCurrentDb関数は実行せず、そのPublicな「dbs」変数を使うようにします。

なお、今回Where条件として使った「会社名」というフィールドには一切キーは設定してありません。よってそれがキー付きのフィールドであった場合には、当然SQLの実行では速くなることが予想されますし、定義域集計関数も速くなる（もしかしたら遅くなるかも）可能性もありますので、場合によっては差異がなくなるかもしれません。それらはケースバイケースとして別途テストが必要かもしれません。
| Index | Prev | Next |

 

Copyright © T'sWare All rights reserved