Microsoft Accessを使ってデータ抽出をされている人も多いかと思います。
AccessならSQLがわからなくてもGUI上の操作でデータの抽出が出来てとっても便利です。操作も分かりやすく、データベースを扱う敷居を大きく下げてくれる、大変素晴らしいソフトだと思います。
ただ、Accessのネガティブな部分としてよく挙げられるのは、「クエリが遅い」と言うことです。
今回はなぜAccessのクエリが遅いのかを掘り下げ、どのように改善出来るのかを紹介していきます。
原因1 リンクテーブルの仕様
Accessが便利なのは、OracleやSQL ServerなどのデータベースとODBCを介して接続し、リンクテーブルとして設定することで、あたかもローカルテーブルと同じように参照したり更新することができるところです。
さらに、リンクテーブルを使えば、接続先データベースの製品の違いをユーザーは意識することなく、クエリ機能を使って、シームレスにテーブルを結合したり一つの表としてまとめたり出来ます。
よって、Accessでデータ抽出をする場合は、大抵自社のデータベースサーバに格納されている主要なテーブル群をリンクテーブルで接続して、そのリンクテーブルに対してクエリを作成します。
例えばSQLServerの例で言えば、データベースの操作については、Microsoft純正の「SQLServer Management Studio(SSMS)」で接続するのが一般的です。
データを参照する際にSQLを書いて実行した場合、書いたSQL文はデータベースサーバに渡されて、サーバ上で構文解析(SQLの文法に誤りが無いかなどのチェック)を行い、その後実行可能な状態にコンパイルし(サーバ側のオプティマイザが渡されたSQLを最適化し実行可能な形式に変換)、実テーブルにアクセスします。
サーバ側で実テーブルから対象のデータを探して、見つかったデータだけをクライアントのManagement Studioに返します。
- クライアント→サーバ：SQLを実行(SQL文を渡す)
- サーバ：SQL文の構文解析、チェック
- サーバ：最適化、コンパイル
- サーバ：実データへの検索実施
- サーバ→クライアント：取得対象の行データをクライアントに返す
よって、クライアント→サーバ間では、SQL文を渡し、検索結果の対象行データを返す通信だけが発生します。
クライアントがSQLをサーバに投げた以降はサーバ側のCPUだけを使って処理し、クライアントは結果を戻してもらえるまで待つだけです。
まず、Accessのリンクテーブルを参照先としてクエリを作成し、そのクエリを実行します。そうすると、Accessではリンクテーブルと設定しているリンク先の実テーブルから全行をまるっと取得します。
クライアント側のAccessの全行をまるっと移動させて、そのローカルに持ってきた全行を対象にクエリで設定した条件を元にデータを絞り込み、見つかったデータをクエリの実行結果として表示させます。
その為、リンク先の実テーブルで大量のデータを管理している場合は、そのデータ転送だけで長時間待たされることになります。
また、クエリの条件を元にデータを絞り込むのはクライアント側のCPUを使って実行します。
Management Studioなどを用いてサーバ側で処理を実行する場合は高性能なサーバ側のCPUで処理されますが、リンクテーブル＆クエリではクライアント側で絞り込み処理がされる以上、クライアントPCのスペックが低ければ、その分さらに処理も遅くなります。
- Access：クエリの実行
- Access：クエリ内容をSQLに変換し構文解析及びコンパイル
- Access→サーバ：リンク先サーバからテーブルの実データを要求
- サーバ→Access：リンク先実テーブルの全行データを返す
- Access：行単位でデータを順次受け取り、クエリの内容を元にデータを絞り込んだり演算する
- Access：抽出結果を表示
よって、「原因1 リンクテーブルの仕様」を要約すると以下になります。
- Access のリンクテーブル＆クエリでは、クエリを実行すると実テーブルから大量のデータ転送が発生する。
- Access のリンクテーブル＆クエリでは、データの絞り込みなどの抽出処理に非力なクライアントのCPUを使用する。
リンクテーブルでクエリを実行した場合の内部処理では上記の様なことが発生しているのです。そりゃあ遅くなりますよね。
ただし、また後述しますが、リンクテーブルを用いたクエリを実行する場合に、常に全行データを転送してくる訳ではなく、適切にインデックスを条件に指定したクエリを作っていれば、全行転送は起こりません。
全行転送有無の詳細な制御内容は私も正直よくわからないのですが、おそらくインデックスが適切に指定出来ていない、全行走査が行われるようなクエリの場合にこういった挙動になるのではないかと考えています。要はクエリの作り方次第な部分もあります。
あと、フォームを作る際、連結フォーム形式で作成し、その連結先をリンクテーブルだったり、リンクテーブルを参照したクエリだったりした場合は、やはり上記の理由によりフォームの動作が重くなることになります。
原因2 専門知識が無くても容易に利用出来ることによる利用者のスキル不足
Access のクエリはSQLがわからなくてもGUIの操作でデータ抽出ができます。
テーブル間のカラム(Accessだとフィールドと呼びます。)同士をドラッグして内部結合や外部結合が作れます。
SQLにおけるWHERE条件の指定も初心者が直感的に行えるような設計になっており、データベースやプログラミング未経験や初心者の人でも容易に扱えます。
リレーショナルデータベースの適切な知識がある技術者であれば、WHERE句で文字列データに対して部分一致で指定をすると、インデックスが効かなくなり、全行走査になるのを知っています。
また、WHERE句で関数などを用いて変換した値を条件に指定した場合や、NOTや<>などの否定演算子を使った条件でもインデックスは使われなくなり、レスポンスは大きく落ちます。
そういったデータベースにおける基本的な知識が欠如した状態でデータベースを扱うのであれば、当然出来上がるクエリは負荷の高い非効率な処理になってしまい、遅いクエリが出来上がってしまうという訳です。
やはりAccessであれば初心者でも習得しやすく、簡単に使えると言っても、扱うのは本来専門的な知識が必要となるデータベースである以上、効率的に利用する為にはある程度のデータベースに対する知識が必要ということですね。
今回の「原因2 専門的な知識が無くても利用出来ることによる利用者のスキル不足」を要約すると以下です。
- Accessユーザーは一般的にデータベースの知識の少ない層が多く、その結果効率的なクエリを作れていない。
一般的に言われるAccess が遅い理由として、主だったものは上記の2点だと考えています。
他にもAccessではサブクエリが作れないので、サブクエリが必要な抽出では、サブクエリ用のクエリを単体で複数作って、それらをまとめて参照するような無理なクエリを作らざるを得ないケースもあり、そういった製品仕様上の制約もあったりしますが、そういった細かい原因を挙げていくときりが無いので、原因の列挙はこの辺にします。
では、遅いAccessをどうすれば早く出来るのかについて、また次回紹介していきます。
↓続きはこちら↓
↓インデックスの活用に関して少し深掘りした記事はこちら
ではでは。
※Access 関連記事はこちら↓
興味があればこちらもどうぞ↓
