#51
今回は、『WHERE句においてインデックスに対する演算や関数は使わない』という定石について調査をしてみました。
WHERE句においてインデックスの付いたフィールドを使うことは多々あるわけですが（むしろ積極的に使った方がよい）、これは、そのフィールドに演算を行った結果の値、もしくは関数にそのフィールドを引数として与えた返り値に対して何らかの条件式を持って抽出するのは避けるべきというものです。
その理由としては、いくらインデックスの設定されたフィールドであっても、演算式・関数式を指定することによってそのインデックスが使われなくなる、すなわち、インデックスではなくテーブル本体のそのフィールドが走査されて演算等が行われるためにパフォーマンスが落ちるというものです。
たとえば、次のようなSQL文がそれに該当します。
SELECT * FROM TABLE WHERE Price + Tax >= 1000
この場合、もしPriceフィールドにインデックスが設定されていたとしても、インデックスからその値が取得されるのではなく、テーブルがスキャンされてその値が取得され、それに対してTaxフィールドの値が加算されることになります。
とはいえ、当然のことながら、データとしてはPrice+Taxが1000以上のレコードを抽出したいわけですから、何らかの算式を使わざるを得ません。そこで、同じ結果が得られる他の算式を検討します。そして、インデックスの付いたフィールドに対して演算をするのではなく、何とかそのフィールドを独立した状態にして、比較演算子の右側にその演算の代用となるものを持っていくようにします。
そしてそれは具体的には次のような形になります。
SELECT * FROM TABLE WHERE Price >= 1000 - Tax
これなら理論的・数学的には同じ抽出条件になりますし、かつPriceフィールドに対して直接的には演算を行われないことになります。
以上のことを踏まえ、それがAccessのJETデータベースエンジンにおけるクエリにも通用するのかを検証してみます。
テスト用のコードは次のようなものです。 「受注コード」フィールドにはインデックス（主キー）が設定されており、オートナンバー型となっています。前半ではあえてそのフィールドに関数式を使い、６桁のゼロサプレスの値から先頭２桁を取り出し、その値が９以上のものを抽出するというWHERE条件にしています。結果的にその計算の意味としては、受注コードが90000以上のものを抽出するのと同じなので、後半のコードではインデックスには関数を使わず、単に「>=90000」という比較演算を行うようにしています。
Sub QueryPerformance_5()
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim iintLoop As Integer
ts_Watch "処理開始", True
Set dbs = CurrentDb
'インデックスで演算した場合
For iintLoop = 1 To 20
strSQL = "SELECT * FROM tbl受注明細 " & _
"WHERE Left(Format$(受注コード, ""000000""), 2) >= 9"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "演算あり"
Next iintLoop
'インデックスで演算しない場合
For iintLoop = 1 To 20
strSQL = "SELECT * FROM tbl受注明細 " & _
"WHERE 受注コード >= 90000"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "演算なし"
Next iintLoop
End Sub
その結果は次のようになりました（単位は秒）。
テストパターン 総実行時間 平均実行時間 最大実行時間 最少実行時間 インデックスの演算あり 21.70 1.08 1.09 1.07 演算なし 0.28 0.02 0.02 0.02
結果は一目瞭然です。平均実行時間では、演算なしの方が50倍ほどパフォーマンスが優れていることが分かります。
ただ、今回の場合は前半のパターンでは関数式を２つ使い、後半のパターンでは関数式も演算式も一切使わないというもので、本ページの前の方で示した例のように、単純に演算式の左辺を右辺に移動したものとは異なります。よって、その関数式自体の処理に掛かる時間もその時間差に加味されているはずです。テストデータは１０万レコードあるので、単純に２０万回の関数処理が行われていることになります。２０回のループ全体では４００万回ということになります。
したがって、この比較値がそのまま両者のパフォーマンスの違いとは言えない部分もあるかもしれませんが、この大差から見れば、『WHERE句においてインデックスに対する演算や関数は使わない』という一般的な定石はAccessにも当てはまるということは確かでしょう。
