#08 レコードセットのメソッドを使うか、SQLを使うか?

結論
VBAでレコード編集を行う場合、JETデータベースエンジンではRecordsetオブジェクトのAddNew・Edit・Deleteの各メソッドが最も最適化された方法である。(しかしODBCを使った場合には"INSERT INTO"のようなSQLステートメントを使った方がよいらしい?)。

VBAでレコードセットを作成しレコード編集を行う際には、AddNewやEdit、Deleteなどのメソッドが必須です。ところが、SQLServerのようなサーバーベースのデータベースにODBC経由でアクセスする場合には「AddNewメソッド1つでもサーバー側へは複数のSQL文が送出されるため、AddNewなどのメソッドは使わずに"INSERT INTO"のようなSQLステートメントを使った方がよい」ということを聞いたことがあります。そこで今回は、JETデータベースエンジンの場合にはどうなのかを確認してみたいと思います。

テストの内容としては、 あるテーブルに1万件のレコードを1件ずつ追加、続いてそれら全レコードに対して1件ずつ更新を行い、最後にそれらすべて削除するというものです。これらの手順を、RecordsetオブジェクトのAddNew・Edit・Deleteの各メソッドを使った場合と、DatabaseオブジェクトのExecuteメソッドを使ってそれらに相当するSQL文を発行する場合とで処理時間の測定と比較を行います。さらに、そのテーブルがローカルテーブルの場合とリンクテーブルの場合についても比較を行ってみます。テストコードは次のようなものです。

  Dim dbs As Database
  Dim rst As Recordset
  Dim ilngLoop As Long
  Const clngRecMax As Long = 10000
 
  ts_watch "測定開始", True

  'AddNewメソッドを使用してレコードを追加
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblTest")
  For ilngLoop = 1 To clngRecMax
    With rst
      .AddNew
        !Data1 = ilngLoop
        !Data2 = ilngLoop * 2
        !Data3 = "ABC"
        !Data4 = "EFG"
      .Update
    End With
  Next ilngLoop
  rst.Close
  dbs.Close
  ts_watch "AddNewメソッド"
 
  'Editメソッドを使用してレコードを更新
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblTest")
  For ilngLoop = 1 To clngRecMax
    With rst
      .Edit
        !Data2 = ilngLoop * 3
        !Data3 = "HIJK"
        !Data4 = "LMN"
      .Update
    End With
  Next ilngLoop
  rst.Close
  dbs.Close
  ts_watch "Editメソッド"
 
  'Deleteメソッドを使用してレコードを削除
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblTest")
  For ilngLoop = 1 To clngRecMax
    With rst
      .Delete
      .MoveNext
    End With
  Next ilngLoop
  rst.Close
  dbs.Close
  ts_watch "Deleteメソッド"

  'INSERT INTO ステートメントを使用してレコードを追加
  Set dbs = CurrentDb
  For ilngLoop = 1 To clngRecMax
    dbs.Execute "INSERT INTO tblTest ( Data1, Data2, Data3, Data4 ) " & _
                  "VALUES (" & ilngLoop & "," & _
                  ilngLoop * 2 & ", 'ABC', 'EFG' )"
  Next ilngLoop
  dbs.Close
  ts_watch "INSERT INTOステートメント"
 
  'UPDATE ステートメントを使用してレコードを更新
  Set dbs = CurrentDb
  For ilngLoop = 1 To clngRecMax
    dbs.Execute "UPDATE tblTest SET " & _
                  "Data2 = " & ilngLoop * 3 & ", " & _
                  "Data3 = 'HIJK', " & _
                  "Data4 = 'LMN' " & _
                  "WHERE Data1 =" & ilngLoop & ""
  Next ilngLoop
  dbs.Close
  ts_watch "UPDATEステートメント"
 
  'DELETE ステートメントを使用してレコードを削除
  Set dbs = CurrentDb
  For ilngLoop = 1 To clngRecMax
    dbs.Execute "DELETE * FROM tblTest " & _
                  "WHERE Data1 =" & ilngLoop & ""
  Next ilngLoop
  dbs.Close
  ts_watch "DELETEステートメント"


テスト結果は次のようなものになりました。なお時間の単位は"秒"です。

追加 更新 削除
Recodsetオブジェクトの各メソッドを使用した場合 14.0 9.9 4.67
DatabaseオブジェクトのExecuteメソッドでSQL文を発行した場合 213.5 1214.2 699.3

結論を述べるまでもなく、ExecuteメソッドでSQL文を発行するという方法はまったく実用になりません。膨大な時間がかかるのはリンクテーブルでも同様でした。ExecuteメソッドではSQL文の解釈や構文チェックの時間が掛かることも含めて、JETデータベースエンジンではRecordsetオブジェクトのAddNew・Edit・Deleteの各メソッドが最も最適化された方法と言えるのかもしれません。

テストする環境がないためにODBCの場合の比較値を載せられないのが残念ですが、SQLServerの環境があるようでしたら、SQLServerのODBCリンクテーブルに対してAddNewなどのメソッドを実行した場合、実際にどのようなSQLが送られているのかを「SQL Trace」ツールなどを使って調べてみてはいかがでしょうか?

| Index | Prev | Next |

 

Copyright © T'sWare All rights reserved