#54
SQL文のWhere句に対する指定では、通常はある条件に”一致する”レコードだけを取り出すことが多いと思いますが、当然ある条件に”一致しない”レコードを取り出したい場合もあります。しかしもし、”一致する”と”一致しない”のどちらの指定によっても結果的に同じレコードが得られるなら、『Notや<>の否定形は使わない方がよい』という定石があります。
たとえばテーブル上のインデックスを持ったあるフィールドに保存されている値が「１」もしくは「２」しかない場合、「１であるレコード」を抽出するのと「２でないレコード」を抽出するのとではどちらも同じ意味合いであり抽出されるレコードも基本的には同じになります。しかし一般的には「NOTや<>の否定形ではインデックスは使われない」ということから、前者の方がよいということです。
そこで今回は、このWhere句の違いによるパフォーマンスの違いをAccessにおいてチェックしてみたいと思います。
ここでは、３種類のコードを用意して、その実行時間を比較してみます。
ここで使う「tbl受注明細」テーブルは、「受注コード」が主キー、「商品コード」が重複ありのインデックスとなっており、100000件のレコードが保存されています。
一つ目のプログラムでは、主キーである受注コードに対して一つだけWhere条件を指定します。結果的に取り出されるレコードは同じなのですが、一方は「受注コードが50000より大きい」という肯定形でWhere句を指定し、もう一方は「受注コードが50000以下でない」という否定形で指定しています。
Sub QueryPerformance_8_1()
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 (受注コード > 50000)"
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 NOT (受注コード <= 50000)"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "否定形"
Next iintLoop
End Sub
二つ目のプログラムでは、主キーである受注コードとインデックスを持った商品コードの二つに対してANDで絞り込んだWhere条件を指定しています。一方は「受注コードが50000より大きくかつ商品コードが65800000である」という肯定形、もう一方は「受注コードが50000以下でなくかつ商品コードが65800000でなくはない（NOTと<>の併用）」という否定形で指定しています。
Sub QueryPerformance_8_2()
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 (受注コード > 50000) AND " & _
"(商品コード = '65800000')"
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 NOT (受注コード <= 50000) AND " & _
"NOT (商品コード <> '65800000')"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "否定形"
Next iintLoop
End Sub
三つ目のプログラムでは３種類の方法を実行しています。「受注コードが50000である」、「受注コードが50000でなくはない」、「受注コードが”50000未満または50000より大きい”のではない」というように異なる表現をしていますが、実質的にはすべて「受注コードが50000である」レコードを抽出していることになります。
ふつうはこのような場合「受注コード = 50000」とずばり書くはずで、他の２つの方法を使うことはまずありませんが、ついでにテストしてみたいと思います。
Sub QueryPerformance_8_3()
〜〜〜省略〜〜〜
strSQL = "SELECT * FROM tbl受注明細 " & _
"WHERE (受注コード = 50000)"
ts_Watch "肯定形"
strSQL = "SELECT * FROM tbl受注明細 " & _
"WHERE NOT (受注コード <> 50000)"
ts_Watch "否定形1"
strSQL = "SELECT * FROM tbl受注明細 " & _
"WHERE NOT (受注コード < 50000 OR 受注コード > 50000)"
ts_Watch "否定形2"
〜〜〜省略〜〜〜
End Sub
上記３つの処理結果は次のようになりました（単位は秒）。
一つ目の結果 二つ目の結果 三つ目の結果 肯定形 4.66 0.97 0.89 否定形 4.68 1.00 0.91 否定形2 32.84
いずれも100000件のレコードのテーブルに対して100回のループ処理を繰り返している割にはわずかな時間差しかありませんが、あえて言えばですが、定石に近く「若干否定形の方が時間が掛かる」と言えるかもしれません（三つ目の結果の否定形2はもはや別格ですね、まずこういった書き方はする人はいないと思いますし...）。
ただしテストとしてかなり意図的にSQL文を組み立てている部分もあるので、結果としては「余分なあるいは回りくどい演算子は使わず、素直に書けばよい」ということも言えると思います。
なお、二つ目と三つ目は一つ目よりかなり短時間で処理が完了しています。これは「商品コード='65800000'」あるいは「受注コード=50000」というようにレコードを絞り込んでいるせいだと思われます。
このことからも、当然どうしても否定形で書かざるを得ない場面は多々ありますが、もし結果が同じであれば『出来る限りレコード数を絞り込んでかつ素直に肯定形で書く』のが一番の得策と言えるのではないでしょうか？。
※ちなみに、VBAでレコードをループ処理する場合、たくさんのレコードをレコードセットに読み込んだうえでループ内のIfステートメントで「受注コード > 50000だったら●●●する」といった書き方もできますが、そうではなく、レコードセットを開く時点のSQL（あるいはクエリ）の段階でできるだけレコードを絞り込んでおいた方がよいと言えます（これも常識的なことかもしれません）。
