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

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

テストの内容としては、あるテーブルに１万件のレコードを１件ずつ追加、続いてそれら全レコードに対して１件ずつ更新を行い、最後にそれらすべて削除するというものです。これらの手順を、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ステートメント"

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

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