Category Access

SQL文を最速にする11のポイント

　たとえ最終的な結果が同じでも，SQL文は書き方一つでパフォーマンスがずいぶんと変わってきます。ここでは，速いSQL文を記述するためのポイ ントや注意点をいくつか紹介しておきましょう。

●WHEREの左辺で算術演算子や関数を使わない

　WHERE句の左辺に算術演算や関数を指定すると，インデックスが使われません。例えば，
SELECT NAME FROM CUSTOMERS
WHERE SAL – TAX ＞ 1000
とすると，たとえSALフィールドにインデックスが定義されていてもテーブル全体を走査してしまいます。こうした場合は，
SELECT NAME FROM CUSTOMERS
WHERE SAL ＞ TAX + 1000
のように記述すれば良いでしょう。

●「後方一致」検索はなるべく避ける

　インデックスが付加されているフィールドであっても，LIKE ‘％AAA’ のような「後方一致」を指定すると，インデックスを検索せずにデータ部の全表走査が行われます。したがって「後方一致」の使用はなるべく避けるようにしま しょう。どうしても必要であるなら，
・何らかの，少量まで絞り込める条件とAND条件で組み合わせる
・複数のフィールドに分割し，少しでも前方・完全一致できる範囲を広げる
といった方法を検討して下さい。

●IS NULL，IS NOT NULLを単独で使わない

　条件を表すWHERE句にIS NULL/IS NOT NULLを指定したときは，インデックスを定義したフィールドであっても，全表走査が行われます。したがって，これらの条件を指定するときは，単独で指定 するのではなく，何らかのかなり絞り込める条件を合わせて指定してください。例えば，問い合わせの結果を変更せずに「B = 10」の条件を付加できるなら
…WHERE A IS NULL
とする代わりに
…WHERE A IS NULL AND B = 10
とします。

●SELECT文で「＊」を使わない

　レコード長が長いときや，フィールド数が多いときには，すべてのフィールドを表す「＊」を指定するのはできるだけ避けて，使用するフィールドだけ を指定するようにします。「＊」を指定すると，参照系のSQL文では，すべてのフィールドを繰り返してコピーするため，リソースを無駄に使うことになりま す。最低限度必要なフィールドだけを指定するのが基本です。

●ORはある程度絞り込んでから使う

　論理演算子ORを使用した場合，一応インデックスが使用されるものの，個々の条件が抽出する件数が少ない（数％程度）状態でないと，あまり効果が ありません。

●GROUP BY，ORDER BY，HAVINGは注意する

　GROUP BY句，ORDER BY句，HAVING句は，余分なディスク入出力が発生したりディスク領域を使うので，自分もしくはほかのプログラムのパフォーマンスに悪影響を及ぼしま す。このことを念頭において，使わずに済むならなるべく使わないようにしましょう。

●演算子の組み合わせで速度が変わる

　検索条件に，「＞」「＜」「＝」をANDで組み合わせるときは，指定の仕方によってインデックスの使われ方が異なります。等号と不等号の組み合わ せは，等号のみインデックスが使われます。例えば，
SELECT NAME FROM CUSTOMERS
WHERE JOB = ‘MANAGER’
AND SAL ＞ 1000
とすると，「JOB = ‘MANAGER’」にはインデックスが使われますが，「SAL ＞ 1000」には使われません。また，不等号同士の組み合わせでは，先に指定した条件だけにインデックスが使われます。つまり
SELECT NAME FROM CUSTOMERS
WHERE TAX ＞ 100
AND SAL ＞ 1000
のSQL文では，RDBMSは「TAX ＞ 100」だけにインデックスを使い「SAL ＞ 1000」には使いません。

●テーブルの別名を利用する

　テーブルに別名をつけて，フィールド名にはその別名をつけると，SQL文の解析処理を減らすことができます。例えば，
SELECT ID, NAME FROM CUSTOMERS
WHERE SAL ＜ 1000
よりも，
SELECT a.ID, a.NAME FROM CUSTOMERS a
WHERE SAL ＜ 1000
のほうが高速になります。

●SQL文の表現を統一する

　本文中で述べたように，RDBMSは実行計画をキャッシュに保存しておいて再利用します。ところが，SQL文に定数を直接記述してしまう と，RDBMSは定数値だけが異なるSQL文を別のものと解釈するため，再利用されません＊B。 バインド変数を使用して，できる限りSQL文を統一するようにします。また，文字の大小や記述の仕方なども統一しておかないと別のSQL文だと認識されて しまうので，気を付けてください。

●SQL文を簡潔に記述する

　SQL文はなるべく簡潔に記述するようにします。そうすることで，SQL文の処理時間を短縮することができます。

URLMON（URLMoniker）ライブラリのAPI関数、「URLDownloadToFile」を使用して、インターネット上の指定URLのファイルをダウンロードする方法を紹介します。ここでの”ファイル”とは、インターネットからダウンロード入手できるフリーソフトのようなものだけでなく、そのURLで表示されるHTMLファイルやそこで使われている画像ファイルなどもすべて含みます。

まず、標準モジュールに次のようなAPIの宣言を記述します。

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                          (ByVal pCaller As Long, ByVal szURL As String, _
                           ByVal szFileName As String, ByVal dwReserved As Long, _
                           ByVal lpfnCB As Long) As Long

次のサンプルプロシージャでは、当サイトより、LZHファイル、HTMLファイル、JPEGファイルをダウンロードしています。ダウンロード元URLを変更することで、さまざまなファイルをダウンロードできることが分かると思います。

Sub test()

  Dim strURL As String
  Dim strFileName As String
  Dim lngRet As Long

  '*************************************
  ' LZHファイルのダウンロード
  '*************************************
  'ダウンロード元のURLを設定
  strURL = "http://www.tsware.jp/download/cabinet/jinz200.lzh"
  'ダウンロード先の保存ファイル名を設定
  strFileName = "c:\jinz200.lzh"
  'ダウンロードの実行
  GoSub ExecDownload

  '*************************************
  ' HTMLファイルのダウンロード
  '*************************************
  strURL = "http://www.tsware.jp/index.htm"
  strFileName = "c:\index.htm"
  GoSub ExecDownload

  '*************************************
  ' JPEGファイルのダウンロード
  '*************************************
  strURL = "http://www.tsware.jp/images/topmainphoto.jpg"
  strFileName = "c:\topmainphoto.jpg"
  GoSub ExecDownload

  Exit Sub

ExecDownload:
'ダウンロードの実行
  lngRet = URLDownloadToFile(0, strURL, strFileName, 0, 0)
  If lngRet = 0 Then
    MsgBox "ダウンロードに成功しました！", vbOKOnly + vbInformation
  Else
    MsgBox "ダウンロードに失敗しました！", vbOKOnly + vbExclamation
  End If
  Return

End Sub

CSVファイルをテーブルにインポートするソース

DoCmd.TransferText acImportDelim, , "amajpcsv", msg, True

以下のコードに置いて数万件のレコードがはじめの数十件しかインポートできない。
テーブル定義は、先頭フィールドがテキスト。
インポートできるデータの数十件は数字で入っているようだ。

どうやら、一つのフィールドに数字と文字が混ざっているとダメらしい。
データを並べ替えて文字データを先頭に持ってきたら全部読みこまれた。
並べ替えるのは大変なので、これでイケルようなのでやってみたらすんなり成功。

まず、メニューの「外部データの取込」「インポート」でテキストファイルを指定して、
インポートウィザードで起動します。そこで「設定」をクリックして
「インポート定義」を開きます。そこで、各フィールドのデータ型を指定します。
「保存」をクリックして定義名を付けて保存します。

TransferTextメソッドの第２引数に上で付けた定義名を指定します。

DoCmd.TransferText acImportDelim, "Jpデータ_インポート定義", "amajpcsv", msg, True

Accessで送料一覧テーブルと、重量フィールドのある商品一覧テーブルを結合して送料情報を得たい

プライマリの結合はいらない。

1.クエリを使わずにSQL文で制御する方法
とりあえずDAOを使う
こんな感じに宣言

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim MQ As QueryDef
Set DB = CurrentDb

strSQL = strSQL & “SELECT ・・・
とりあえずVB6系では 結合代入 .= += とかが使えないので回りくどく書くしかない。 → strSQL = strSQL &

クエリ内では、ROUNDが使えないらしい。ビルドの一覧には出るのに結果は全く反映しないので注意!!
しかたなく、INT で対応。+0.99は少数第2で切り上げるための処置。

WHEREの()がくどいので削除したら、条件が適用されなくなったのでクエリで自動で作成されたカッコは消さない方がいい。

計算式がはいったカラムで整列やWHERE句を書くとパラメータクエリになってしまう。
開くときにいちいちパラメータが足りません、1を入力してください」とか聞いてくるのでそれを回避する方法

2.SQL文の実行で聞いてくる場合

Set MyQuery = DB.CreateQueryDef(“”) ‘クエリをセット
strSQL = “PARAMETERS [カラム名] TEXT;”‘SQL文の先頭に書く

コメントアウトする → ‘Set RS = DB.OpenRecordset(strSQL) これでは開けないので。
代わりにこの文を
With MyQuery ‘SQL文にパラメータを渡してから実行
.SQL = strSQL
.Parameters(0) = 1 ‘0はパラメータのインデックス、値1を入れる
Set RS = .OpenRecordset() ‘クエリ実行
End With