ACCESS：フラグを消し去るVBA #5 スピード対決
- (2010-02-19 05:49:52)
●テスト環境：
・CPU：Pen4
・Memory：1GB
・db：ACCESS/Jetデータベースエンジン
・レコード件数：テーブル1、テーブル2ともに5万件
●予測：
・Do LoopとExecuteは同じDAOからの操作。Do Loopは一見、最初のレコードから順々に最後のレコードまでデータをなめていくので遅い気がした。
しかし、よくよく考えてみれば、Executeもコードの書き方こそ1行ながらコンピュータの動作はDo Loopと同じく1レコードごとに一個一個やっていくしかないはず。と考えると時間差はないかも？
・DoCmd.RunSQLはACCESS内部のネイティブコマンドのはずだから、速いのでは？と予測。
●テスト方法：
開始時刻と終了時刻を記録し、時間差を秒で表示する下記コードをプログラムの前後に挟む。
フラグを消し去るプログラム実行後に経過秒数が表示されるので記録する。
【経過秒数が表示するコード】
Dim 開始日時 As Variant,Dim 終了日時 As Variant,Dim 経過時間 As Single
開始日時 = Now()
(Do LoopやSQLなど実行)
終了日時 = Now()
経過時間 = DateDiff("s", 開始日時, 終了日時)
MsgBox 経過時間
●結果：
理由は不明だが、操作ごとに秒数に大きなバラつきが発生することがあり、必ずしもいつも安定したきれいなデータが採取されるわけでないが、3回連続、比較的安定したときに得られたデータから採取したものを記載。
【3回連続試験の結果＆概ねの平均値】
・DAO+Do Loop方式：5秒,6秒,6秒 平均：6秒
・DAO+Execute方式：2秒,2秒,3秒 平均：2秒
・DoCmd.RunSQL方式：5秒,4秒,4秒 平均：4秒
なお、プログラム実行後、DoCmd.Requeryなどで表示をリフレッシュした方がよいかもしれないが、時間のバラつきが非常(たとえば20秒〜30秒程度、無言になる現象が発生してACCESSの不機嫌具合に手を焼いた。その間CPUは0%、何やってんだろう？)に大きくなるため今回の実験コードには入れていない。
●結論
・Do Loop：予想外いけてなかった。アウト。プログラム実行時にコードを一行読んだらコンパイルして実行して、とかなんとかやっているに違いない。コードの記述も大変だが、実行も文字通り丁寧すぎる作動が遅さの原因か？
・Execute：今回の3候補の中では一番クール。ハードディスクのガリガリ感も少ない。
・DoCmd.RunSQL：「ACCESSネイティブだから、ぶっちぎりでは？」と期待していたが、案外サクッとはいけていない。ハードディスクのガリガリ感も気になる。何をやっているのだろう？Executeとは明らかに挙動が違う。計算結果は同じでも操作ロジックに大きな違いがあるように思える。
ということで私のPCと現在のdb環境ではDAO＋Executeが一番速いことが判明。今後はDAO＋Executeを愛用したい。
ただし、ExecuteがDoCmd.RunSQLより優れているか不明。PCの環境やテスト方法の如何によっては違う可能性もでてくるだろう。普遍的な結果とは考えていない。いずれにしてもDo Loopはやはりイマイチと判断してもよいのでは。
この記事は#5
ACCESS：フラグを消し去るVBA #5 スピード対決
ACCESS：フラグを消し去るVBA #4 DoCmd.RunSQL
ACCESS：フラグを消し去るVBA #3 Execute
ACCESS：フラグを消し去るVBA #2 Do Loop
ACCESS：フラグを消し去るVBA #1 消し忘れ