Access Labo #49 クエリパフォーマンスの定石を試す（３） | T'sWare

Access Labo

#49 クエリパフォーマンスの定石を試す（３）

結論: クエリパフォーマンスの定石『Like演算子では中間一致・後方一致は使わない』はAccessの場合にも当てはまり、処理時間でも大きな違いが現れる。インデックスを有効に使わせパフォーマンスをよくするためには、”前方一致”での検索・抽出がベターである。

クエリ（SQL文）のパフォーマンスを上げるための”定石”がAccess（JETデータベースエンジン）でも通じるかを試す３回目、今回は、『Like演算子では中間一致・後方一致は使わない』という定石について調査をしてみました。

Like演算子の使い方には、「*（半角アスタリスク）」のワイルドカードを使った方法では次の３つの書き方があります。

Like "A*" ・・・・・"A"という文字で始まる（前方一致）
Like "*A*" ・・・・・"A"という文字を含む（中間一致）
Like "*A" ・・・・・"A"という文字で終わる（後方一致）

定石では、このうちの「前方一致」は速いが「中間一致・後方一致」は遅いというものです。

これはインデックスの構造を考えみれば容易に想像できることかもしれません。
インデックスには、テーブルのそのフィールドに納まっているデータがソートされた状態で保存されています。たとえば「”A”で始まる」レコードだけをピックアップしたい場合、上から順番にスキャンし、”B”が出てきたところで処理を終了させることができます。それ以降はもはや「”A”で始まる」データは絶対にないからです。一方、「”A”を含む」レコードを探したい場合、”B”が出てきたからといってスキャンを終了させることはできません。たとえ”Z”で始まっているデータであっても２文字目以降に”A”が含まれている可能性があるからです。

そのような理由から、前方一致はインデックスだけのスキャンで済みますが、中間一致・後方一致はテーブルのフィールド内までをスキャンする必要があるわけです。当然、テーブルスキャンだけでなく、フィールド内の文字列ひとつひとつを照合するための時間も余分にかかるはずですから、トータル的に処理スピードが落ちることになります。

ということで、理論的には当然のことのように考えられるわけですが、今回はそれを実際にプログラムを走らせることで確認してみたいと思います。

今回の実験では、総レコード数１０万件のテーブルを使って、「商品コード」（テキスト型フィールド）に対して「123で始まる」・「123を含む」・「123で終わる」の３種類のWHERE句による抽出処理を行い、その実行結果を比較してみます。

なお、今回のテストに使うテーブルのデータの場合、それぞれ「219件」・「306件」・「139件」のレコードが抽出されることになります。厳密に言えば、レコード数が異なることによってレコードセットをループ処理する時間なども違ってはくるはずですが、トータル１０万件に対する比率としては大差ありませんし、もっとも時間差が表れるのはレコードセットを開くところだと思いますので、その点は無視して考えることとします。

テスト用のコードは次のようなものです。なお、「商品コード」フィールドには重複ありのインデックスが設定されています。またOpenRecordsetメソッドの引数に与えるSQL文内のWHERE句を除いては３つのテストパターンに違いはありません。

Sub QueryPerformance_3()

  Dim dbs As Database
  Dim rst As Recordset
  Dim strSQL As String
  Dim iintLoop As Integer
  Dim strDummy As String

  ts_Watch "処理開始", True

  Set dbs = CurrentDb

  For iintLoop = 1 To 100
    'テストパターン１
    strSQL = "SELECT 商品コード FROM tbl受注明細 " & _
                    "WHERE 商品コード Like '123*'"
    Set rst = dbs.OpenRecordset(strSQL)
    With rst
      Do Until .EOF
        strDummy = !商品コード
        .MoveNext
      Loop
      .Close
    End With
    ts_Watch "Like 123*"

    'テストパターン２
    strSQL = "SELECT 商品コード FROM tbl受注明細 " & _
                    "WHERE 商品コード Like '*123*'"
    Set rst = dbs.OpenRecordset(strSQL)
    With rst
      Do Until .EOF
        strDummy = !商品コード
        .MoveNext
      Loop
      .Close
    End With
    ts_Watch "Like *123*"

    'テストパターン３
    strSQL = "SELECT 商品コード FROM tbl受注明細 " & _
                    "WHERE 商品コード Like '*123'"
    Set rst = dbs.OpenRecordset(strSQL)
    With rst
      Do Until .EOF
        strDummy = !商品コード
        .MoveNext
      Loop
      .Close
    End With
    ts_Watch "Like *123"

  Next iintLoop

End Sub

その結果は次のようになりました（単位は秒）。

テストパターン総実行時間平均実行時間最大実行時間最少実行時間

Like 123*（前方一致） 1.63 0.02 0.03 0.01

Like *123*（中間一致） 45.14 0.45 0.46 0.43

Like *123（後方一致） 44.60 0.45 0.45 0.43

今回の結果については、予想通りとはいえ、その効果は歴然です。前方一致だけがずば抜けており、中間一致や後方一致の２０倍以上もパフォーマンスが優れいてることが明確です。

このことから、定石通り、『Like演算子では中間一致・後方一致は使わない』はAccessでも有効であり、インデックスを有効に使わせパフォーマンスをよくするためには、”前方一致”での検索・抽出がベターであると言えます。

とはいえ、使うなといっても使わざるを得ない場面も多々あるわけで、クエリの目的から中間一致・後方一致を使わざるを得ないケースも当然ありますし、その目的を考えた時には”前方一致”に変更することは簡単にはできないはずです。
そういった場合には、「他のWHERE条件でなるべくレコード数を絞り込んだ上でLike演算子をANDで併用する」や、「抽出対象となる文字範囲のデータだけを保存したフィールドをあらかじめ用意しておきそちらを対象に前方一致で抽出をかける（たとえば商品コードの後ろの３文字だけを保存するためのフィールドを用意し、レコード追加時に自動的にその３文字だけをそこにコピーして保存しておく）」といった工夫が必要となるでしょう。

| Index | Prev | Next |

Copyright © T'sWare All rights reserved