#53
あるフィールドの値がいくつかの値と一致するものだけを抽出したい場合、たとえば「回数=10 OR 回数=20 OR 回数=30」のようにORを使って条件をつなげるのが一般的ですが、「In演算子」を使って「回数 In (10, 20, 30)」と書くのも分かりやすくかつ短くて効果的です。
このIn演算子に関しては、そのパフォーマンスの面から、『カッコ内のリストにはヒット確率の高い順に条件を記述する』という定石があります。
つまり、適当に抽出条件となる値を並べるのではなく、全レコードに対して抽出されるレコード数が多いであろうと推測される条件から順番に並べるとよいというものです。
そこで今回は、その定石について、Accessの場合どうなるかをテストしてみます。
今回のテスト内容としては、全部で１０万レコードあるtbl受注明細テーブルから抽出処理を行います。その抽出条件は次の４つで、それぞれ下表のようなレコード数が抽出されるようなテストデータとなっています。なお（今回の場合はあまり関係はないかもしれませんが）「商品コード」にはインデックスが付けられています。
抽出する商品コードの値 抽出されるレコード数 12300123 139 50000000 70 66000000 35 33570000 10 合計 254
ご覧のように、あまり極端な違いはないかもしれませんが、商品コード＝12300123がもっともヒット率が高く、商品コード＝33570000がもっとも低くなっています。
そこでテストとしては、In演算子のカッコ内に指定する順番を、そのヒット率の高い順に列挙した場合と低い順に列挙した場合の２つについて動作確認を行ってみます。
そのプログラムは次のようなものです。
Sub QueryPerformance_7()
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 100
strSQL = "SELECT * FROM tbl受注明細 " & _
"WHERE 商品コード " & _
"In ('12300123','50000000','66000000','33570000')"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "ヒット率の高い順"
Next iintLoop
'ヒット率の低い順
For iintLoop = 1 To 100
strSQL = "SELECT * FROM tbl受注明細 " & _
"WHERE 商品コード " & _
"In ('33570000','66000000','50000000','12300123')"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "ヒット率の低い順"
Next iintLoop
End Sub
その結果は次のようになりました（単位は秒）。
テストパターン 総実行時間 平均実行時間 最大実行時間 最少実行時間 ヒット率の高い順 0.67 0.02 0.02 0.02 ヒット率の低い順 0.67 0.02 0.02 0.02
ご覧のように、結果にはまったく違いはありません。
もしヒット率が90%と1%というように大きな違いがあるとまた別の結果が出てくる可能性はあるかもしれませんし、インデックスのないフィールドに対して抽出を掛けた場合も異なる結果になるかもしれません。ただ今回の場合でも最大と最小ではヒット率が１０倍以上の違いがありますので、それなりに評価できる条件ではないでしょうか？。
なお、今回のIn演算子に関する定石は、「引数に並べられた条件は左から右へという順番で評価され、いずれかの条件でそのレコードがヒットした場合はそのあとの条件の比較は行われない」という理由から言われているものです。
たとえば「回数 In (10, 20, 30)」という条件の場合、あるレコードの回数フィールドの値が「10」であったならば、”該当”として処理完了し、それが「20」なのか「30」なのかは比較処理せず、次のレコードの評価に移るというものです。
この理由を知ると、Access VBAの比較演算を思い出します。
それは何かというと、たとえば「If A = 10 OR A = 20 Then」というような条件分岐の式です。
”変数Aが10または20ならある特定の処理を実行する”という流れ制御を行うものですが、AccessのVBAの場合、「まずAが10かどうかを比較する」、そしてその結果にかかわらず続いて「Aが20かどうかも比較する」という内部処理が行われているといわれています。つまり、たとえAが10であると判明しても、余分にAが20かどうかの比較を行うというものです。
プログラミング言語によっては、Aが10であると分かったところでもはや以降の比較は無意味なので、その時点で条件が真として、If文に指定されたステートメントに進むという仕様のものがあります。
今回の結果についても、もしかしたらそれと同様の比較手順の違いがSQLあるいはJETデータベースエンジンにも適用されているのかもしれません。
