スモールビジネスのIT日誌

• (2010-02-19 05:49:52)

フラグを消し去るVBA、スピード対決(Do Loop、Execute、DoCmd.RunSQL)

●テスト環境：

・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　消し忘れ

• (2010-02-19 05:42:18)

3)フラグを消し去る(DoCmd.RunSQL方式)

「DoCmd.RunSQL」を使用。

SQL文は同じ構文そのまま。SQL実行方法をExecuteからDoCmd.RunSQLに変更。

DoCmd.RunSQLもオブジェクトだろうに、なぜかDoCmdは宣言もSetもCloseも解放も必要ない。ACCESS内部のコマンド体系でACCESSが起動されると常時ともにロードされているためか。

DoCmdは宣言もSetも必要ないのでコードの書き方としては一番シンプルで美しくわかりやすい。

SQLの記述->DoCmdでDoCmd.RunSQLメソッドの実行。

Private Sub フラグクリア_Click()

Dim SQL1 As String, SQL2 As String

'データ更新のためのSQL文(update テーブル名 set フィールド名 = 内容)とその実行(DoCmd.RunSQL)

SQL1 = "UPDATE [テーブル1] SET [フィールド1] = False, [フィールド2] = False"

SQL2 = "UPDATE [テーブル2] SET [フィールド3] = False, [フィールド4] = False"

DoCmd.SetWarnings False

DoCmd.RunSQL SQL1

DoCmd.RunSQL SQL2

End Sub

この記事は#4

ACCESS：フラグを消し去るVBA #5　スピード対決

ACCESS：フラグを消し去るVBA #4　DoCmd.RunSQL

ACCESS：フラグを消し去るVBA #3　Execute

ACCESS：フラグを消し去るVBA #2　Do Loop

ACCESS：フラグを消し去るVBA #1　消し忘れ

• (2010-02-18 05:12:23)

一発でフラグを消し去る書き方はないか？

上記の「Do Loop方式」はコードの記述が多いので、なんかこう一発でコードを書けるやり方はないかと探すと「Execute」と「DoComd.RunSQl」が使えそう。

「Execute」と「DoComd.RunSQl」の意味の違いは不明。勝手に推測すると

・「Execute」：Jetデータベースエンジンを外部プログラムから操作するためのオブジェクト体系であるDAO(*1)の中のコマンドの一つ。DAOはJetデータベースエンジン専用のオブジェクト体系で、JetデータベースエンジンとACCESSネイティブなコマンド体系？

・「DoComd.RunSQl」：「DoComd」シリーズはACCESS内に組み込まれているACCESSの操作を自動化するコマンド体系。ACCESSのマクロパートからマクロを作成しコード変換すると「DoComd○○」という形式になるものが多いことから、もともとACCESSの開発過程でマクロパートの部分用に独自に開発されたコード体系ではなかろうか。このコードをベースに逆にビジュアル化した部分がACCESSマクロではなかろうかと推測。もしそうならACCESS内のコードで、ACCESSネイティブだろうから処理スピードが速いのでは？(と予想したが実験ではそれほどでもなかった->後述)

(*1)DAO：Data Access Objects(DAO)。同じような操作ができるコマンド体系として他にActiveX Data Objects(ADO)が提供されている。ADOはODBC準拠とのこと。そのため他のデータベース製品操作も可能な点で汎用性が非常に高い。今後DAOに代わってACCESSの操作オブジェクトではADOが標準的なコマンド体系になる模様。

2)フラグを消し去る(DAO+Execute方式)

「Execute」を使用。

オブジェクトの宣言->オブジェクトのset->SQLの記述->DAOでExecuteメソッドの実行->オブジェクトのClose＆解放。

Private Sub フラグクリア_Click()

Dim DB As Database

Dim SQL1 As String, SQL2 As String

Set DB = CurrentDb

'データ更新のためのSQL文(update テーブル名 set フィールド名 = 内容)とその実行(Execute)

SQL1 = "UPDATE [テーブル1] SET [フィールド1] = False, [フィールド2] = False"

SQL2 = "UPDATE [テーブル2] SET [フィールド3] = False, [フィールド4] = False"

DB.Execute (SQL1)

DB.Execute (SQL2)

DB.Close

Set DB = Nothing

End Sub

この記事は#3

ACCESS：フラグを消し去るVBA #5　スピード対決

ACCESS：フラグを消し去るVBA #4　DoCmd.RunSQL

ACCESS：フラグを消し去るVBA #3　Execute

ACCESS：フラグを消し去るVBA #2　Do Loop

ACCESS：フラグを消し去るVBA #1　消し忘れ

• (2010-02-17 06:04:04)

1)フラグを消し去る(DAO+Do Loop方式)

昔書いたコードを引っ張ると「Do Until〜Loop」を使用していた。

Do Loopでサンプルコードを書く。テーブルは2種類、それぞれに消したいフィールドが2個あると想定。DatabaseオブジェクトをSetしてレコードセットのインスタンスを作成。最初のレコードから最後のレコードまで順々にフラグにFalseを入れていく操作。

Private Sub フラグクリア_Click()

Dim DB As Database

Dim RS1 As Recordset

Dim RS2 As Recordset

Set DB = CurrentDb

'テーブル1のフラグを消し去る

'Do Loopでレコードを最後までなめ回す

Set RS1 = DB.OpenRecordset("テーブル1", dbOpenTable)

Do Until RS1.EOF

RS1.Edit

RS1![フィールド1] = "False"

RS1![フィールド2] = "False"

RS1.Update

RS1.MoveNext

Loop

RS1.Close

Set RS1 = Nothing

'テーブル2のフラグを消し去る

'Do Loopでレコードを最後までなめ回す

Set RS2 = DB.OpenRecordset("テーブル2", dbOpenTable)

Do Until RS2.EOF

RS2.Edit

RS2![フィールド3] = "False"

RS2![フィールド4] = "False"

RS2.Update

RS2.MoveNext

Loop

RS2.Close

Set RS2 = Nothing

Set DB = Nothing

End Sub

この記事は#2

ACCESS：フラグを消し去るVBA #5　スピード対決

ACCESS：フラグを消し去るVBA #4　DoCmd.RunSQL

ACCESS：フラグを消し去るVBA #3　Execute

ACCESS：フラグを消し去るVBA #2　Do Loop

ACCESS：フラグを消し去るVBA #1　消し忘れ

• (2010-02-17 06:03:14)

奥が深いACCESSに今回もはまる

-------------------------------

時として発送伝票(メール便、宅急便)や請求書などの書類を個別に打ち出す必要がある。コンビニ用紙の再発行など。その際は氏名などでdbを検索し、印刷すべき伝票や書類の「フラグ」を立てて印刷ボタンを押す。

フラグのクリア

それで操作は終了するが、立てられたフラグのクリアは今までロジックに入れていなかったので、次回違うレコードを操作の際、以前のレコードまで引いてくる。

いつものように「大した作業でないから」と毎回手作業でクリアしていた。やりっ放しで終了手続きを疎かにする。

これも自動化がよかろうと検討。今回は下記2種類の対策を考えたが、後者を選ぶ。

・印刷ボタンを押した時点でフラグクリア(プリンターが調子が悪い場合、再印刷に不便なので不採用)

・この種のテンポラリーフラグはdb内の全レコードですべてまとめてクリアする

この記事は#1

ACCESS：フラグを消し去るVBA #5　スピード対決

ACCESS：フラグを消し去るVBA #4　DoCmd.RunSQL

ACCESS：フラグを消し去るVBA #3　Execute

ACCESS：フラグを消し去るVBA #2　Do Loop

ACCESS：フラグを消し去るVBA #1　消し忘れ