結論

レコード追加では「QueryDefやDatabaseオブジェクトを使うよりDocmdを使った方が速い」、またCurrentDb関数によってデータベースを開く場合とDocmdを使った場合それぞれでは「SQL文を直接発行するより保存済みクエリーを使った方が速い」。また、リンクテーブルを対象としたレコード削除では、「Docmdを使うよりをQueryDefやDatabaseオブジェクト使った方が速い」。

追加クエリーや更新クエリー、削除クエリーといったアクションクエリーを実行したい場合、もっとも簡単なのはデータベースウィンドウからそれらのクエリーを選択して[開く(O)]をクリックすることです。複雑な処理を行うのではなく、いくつかのアクションクエリーを実行するだけで完了するような簡単な処理で、かつある程度Accessを使える人であればその方法で充分でしょう。もしAccessそのものを知らない人がそのデータベースをアプリケーションとして使うのであれば、マクロを使ってアクションクエリーの実行を自動化すればよいでしょう。

一方、いくつかのアクションクエリーの実行だけでは完結しないような複雑な処理、例えばある条件をユーザーが任意に指定して、その条件を満たすレコードについてユーザーが指定した任意の値に更新するなどの処理では、アクションクエリーの実行の前後にもいろいろな処理が必要となりますので、データベースウィンドウの[開く(O)]のクリックだけではとても対応できません。そのような場合にはVBAを使ってクエリーを開くことになるでしょう。

ここで、データベースウィンドウやマクロを使ってアクションクエリーを実行する場合にはその方法は単一なので何も迷うことはないのですが、VBAを使ってアクションクエリーを実行するとなるといろいろな選択肢が出てきます。そのいくつかを挙げると、

1. Docmd.OpenQueryで保存済みクエリーを開く
2. Docmd.RunSQLでSQL文を実行する
3. QueryDefオブジェクトのExecuteメソッドで保存済みクエリーを実行する
4. DatabaseオブジェクトのExecuteメソッドでSQL文を実行する

ここで、「保存済みクエリー」とはデータベースウィンドウに表示されているクエリーのことを指しています。VBA上でSQL文を発行する操作も、ある意味では「クエリーを実行」することになるので、違いを明確にするためにこのように表現しました。あまり体感的には差を感じていないのですが、保存済みクエリーを一度実行するとそのSQLがコンパイルされ、以降はSQLの文法チェックが不要となり、その分コンパイルされていない時よりもパフォーマンスが向上するそうです。一方、実行時にSQL文を発行する場合には、その都度SQL文の内容が解釈・文法チェックされると考えていいと思います(ただしMDEについては不明です)。

保存済みクエリーの場合にはデータベースウィンドウの[開く(O)]のクリックだけで実行できるので、その部分だけのテストが簡単かつ確実にできる点がメリットと言えるでしょう。一方、規模の大きいアプリケーションを作るとクエリーの数はすぐに何十ケにも膨らんでしまいます。フォームやレポートのレコードソースなどあちらこちらで使えるような汎用性のあるクエリーならよいのですが、特定の処理で使われるだけの専用的なものや使用頻度の少ないものであれば、保存済みクエリーとしては増やしたくない場合もあると思います。そのような場合には２や４の方法を採ることもできます。

それでは処理時間の観点からはどの方法が最適なのでしょうか？。あるいは時間的な違いがあるのでしょうか？。上記４つの方法について、処理時間の測定・比較を行ってみたいと思います。

テストの内容としては、 tblTestSource テーブルに登録されている５万件のレコードをそれぞれの方法を使って tblTest テーブルに追加します。続いて tblTest テーブルに追加された５万件すべてのレコードをそれぞれの方法を使って削除します。さらに、それらのテーブルがローカルテーブルの場合とリンクテーブルの場合についても比較を行ってみます。テストコードは次のようなものです。なお、それぞれのテスト条件をなるべく同じにするために、実際のアプリケーションで使う場合に比べてかなり冗長なコードになっています。

	ローカルテーブル			リンクテーブル
	追加	削除	追加時の 最大−最小	追加	削除
１．Docmd.OpenQuery	28.2	0.05	1.9	50.8	0.13
２．Docmd.RunSQL	29.0	0.06	1.6	52.8	0.11
３．QueryDef+Execute	29.7	0.06	1.2	52.3	0.09
４．Database+Execute	30.2	0.05	1.8	54.1	0.09

これらの結果から、レコード追加についてはおおむね上記４つの方法の番号順に処理時間が短いことが分かります。結論としては、「QueryDefやDatabaseオブジェクトを使うよりDocmdを使った方が速い」、またCurrentDb関数によってデータベースを開く場合（３、４）とDocmdを使った場合（１、２）それぞれでは「SQL文を直接発行するより保存済みクエリーを使った方が速い」ということが分かります。

後者については前述の保存済みクエリーのコンパイルが要因と考えられます。一方、前者についてはクエリーの実行だけでなくCurrentDb関数によってデータベースを開いたりする時間の影響も考えられますので、それについては別途実験を行ってみたいと思います。

一方、レコード削除の場合にはローカルテーブルとリンクテーブルとで異なる結果が出ています。ローカルテーブルの場合にはそれぞれの方法による時間的違いは微少ですが、リンクテーブルの場合には「Docmdを使うよりをQueryDefやDatabaseオブジェクト使った方が速い」という結果になっています。

また、今回の実験ではおもしろい結果も得られました。それは上記の４つの処理をループで何度か実行すると、その度に実行時間が増えていくということです。上表の「追加」と「削除」の時間はそれらの平均値を示していますが、「追加時の最大−最小」欄に示したのが最後に実行した時間と最初に実行した時間の差です。これらの値から推測されることは、頻繁にデータベースの最適化を実行できれば影響は少ないが、そうでない場合は「QueryDefオブジェクトのExecuteメソッドで保存済みクエリーを実行する」場合が最もパフォーマンスの劣化が少なくて済む、ということです。なお、削除クエリーでは値そのものが非常に小さいのですが、特に差は見られませんでした。

今回の実験を通して、４つの方法には何らかの時間的違いがあることが確認できましたが、実際には状況に応じて使い分けることも必要だと思います。例えば、"DELETE * FROM tblTest" のようなシンプルなクエリーであれば、そのデザインを開いてみないと内容が分からない保存済みクエリーよりも、モジュールの中に "DELETE * FROM tblTest" と記述した方が一連の処理の流れを通しての可読性は高いと思います。また、アクションクエリーがパラメータを持っている場合に"Docmd"を使ってそのクエリーを開くと、パラメータを入力するためのダイアログが表示されてしまい自動的に値をセットして処理を続行することができません。全体の処理時間に対するその部分の比率やメンテナンス性なども含めてどの方法を使うかを判断することが必要でしょう。