#50
クエリ（SQL文）のパフォーマンスを上げるための”定石”がAccess（JETデータベースエンジン）でも通じるかを試す４回目、今回は、『Is Nullは使わない』という定石について調査をしてみました。
一般的に、Null値は空の値でありインデックス内にはそのデータが保持されないと言われます。そのため、Where句にIs Nullを指定した場合、たとえそのフィールドにインデックスが定義されていたとしても、インデックスは使われずテーブルスキャン（全表走査）が行われるというわけです。インデックスが使われないので、パフォーマンスとしては落ちるということになります。
今回はまず、Where句にIs Nullを指定することによってどの程度レコードの読み込みスピードに違いが出るのかを調べてみました。
ひとつは、Where句なしで全レコードを読み込み、単純にそのレコードセットの最後までループ処理するものです。このテーブルには総レコード数１０万件データが保存されているので、その全レコードをそのまま読み込みループ処理することになります。
二つめは、Where句にIs Nullを指定しますが、あえてNull値のレコードがひとつもないフィールドに対してWhere句を指定してみます。Where句を指定したといっても該当レコードがないことは分かっているので、処理対象レコード数は１０万件ということになります。ただし、Where句を指定することでそのフィールドの値がNullかどうかをチェックする処理が余分に加わりますので、最初のケースよりは時間がかかることが予想されます。
三つめは、同じくWhere句にIs Nullを指定しますが、今度はあらかじめNull値が含まれていることが分かっているフィールドを対象とします。テストデータとしてはおおむね1/4つまり２万５千件程度のレコードがその条件にヒットするようなものになっています。抽出処理に要する時間は二つめと同じであることが予想されますが、レコードセットを開いたあとにそこに保持されるレコード数が少ないので、その分ループ処理にかかる時間が少なくて済むと思われます。
なお、二つめ・三つめでWhere句を指定するフィールドには、いずれもインデックス（重複あり）が設定されています。
上記３パターンのテスト用のコードは次のようなものです。３つの違いはstrSQL変数に代入するSQL文のみです。
Sub QueryPerformance_4()
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 10
'テストパターン１
strSQL = "SELECT * FROM tblテスト"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "Where句なし"
'テストパターン２
strSQL = "SELECT * FROM tblテスト WHERE フィールド01 Is Null"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "NullなしフィールドにWhere句指定"
'テストパターン３
strSQL = "SELECT * FROM tblテスト WHERE フィールド02 Is Null"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
ts_Watch "NullありフィールドにWhere句指定"
Next iintLoop
End Sub
その結果は次のようになりました。時間はループ１回分当りの平均時間（単位は秒）です。
Where句なし 0.09 NullなしフィールドにWhere句指定 0.01 NullありフィールドにWhere句指定 0.04
まず、ひとつめと二つめの比較です。これは予想外の結果となっています。２つめではWhere句が指定されていますので、フィールド値がNullかどうかをチェックする処理が増えることによって時間が余分にかかると思われましたが、結果は逆になっています。
これについては、次のようなことがその要因として考えられるかもしれません。
- Accessでは、Null値もインデックスに格納される。そのため、Where句を指定しない場合はテーブルスキャンされるが、Where句を指定したためにインデックスが効果的に利用され、結果的に取りだされるレコード数は同じでも読み込みスピードがアップした？。
- 一般的に、たとえ結果的に取り出されるレコード内容がまったく同じでも、インデックスを持ったフィールドに対してWhere句を指定すると処理が速くなると言われる。たとえばオートナンバー型の「ID」という主キーフィールドがあった場合、Where句に「ID > 0」という条件を付ける。オートナンバー型なので「ID <= 0」というレコードは絶対に存在しないのでまったく無意味そうなWhere句だが、それでもこのWhere条件を付けるだけでスピードアップが期待できるというもの。今回もそのケースで、今回のテストデータの場合はIs Nullという条件式は意味をなしていないが、Where条件を付けたというだけで速くなった？。
次に、二つめと三つめを比較してみます。これについてもはじめは「レコードセットのレコード数が少ないのでその分ループ処理にかかる時間が少なくて済む」と考えていましたが、逆の結果になっています。
考えられる要因としては、やはりNull値はインデックス上に保持されておらず、毎回毎回そのチェックがテーブル上で行われた、そして二つめではWhere条件にヒットするレコードが存在しているため、抽出されたあとのレコードの選別のような処理が行われた、といったことが想像されます。
要因については正確なところは分かりませんが、「Where条件にヒットするレコードが多いと処理に時間がかかる」ということは言えそうです。また、「Is Nullは使わない」という定石に反して、「Is Nullを使ったからといってパフォーマンスは落ちない、むしろ上がる」とも言えそうです。
ところで、「Is Nullは使わない」というのが定石とはいっても、Null値のフィールドを取り出したいからこそそのような条件式を指定するわけで、遅いからといって使わないわけにはいかないと思います。ではどうすればよいかですが、やはり定石としては「結果的には同じレコードが取り出せるような別の条件を追加して絞り込みを掛けた上でIs Nullでの抽出を行う」というのがあります。
そこで、Where句にもうひとつ条件式を追加して、よりレコードを絞り込んだ状態にして、そこからさらにIs Nullで絞り込むというコードでテストを行ってみました。そのコードは次のようなものです。
strSQL = "SELECT * FROM tblテスト WHERE ID <= 1000 AND フィールド02 Is Null"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do Until .EOF
.MoveNext
Loop
.Close
End With
このプログラムでは、主キーフィールドであるID（オートナンバー型）が1000以下のものだけを抽出、されにその中からNull値のレコードだけを取り出すというものです。テーブルには全部で１０万レコードがあるので、IDが1000以下のものはその100分の1だけしかなく、その中からNullのデータを取り出すので、全体的にはかなりパフォーマンスが上がりそうです。
ところが、このプログラムを実行してみると、これまでと桁違いに時間がかかってしまいました。この結果は「18.39秒」という非常に大きなものになりました。結論としては、「主キーフィールドを使って絞り込みを行うと大幅にパフォーマンスは落ちる」ということになると思います。
おそらく、このようなWhere条件の場合、コードの見た目からは「まずIDが1000以下のものだけが抽出され、その範囲の中でフィールド02がNull値であるレコードだけがさらに絞り込んで取り出される」ように思われますが、実際にはそのような２段階の抽出処理ではなく、「全１０万レコードひとつひとつについて、２つの条件式がチェックされた」と考えた方がよいのかもしれません。
条件式がひとつであればチェックは１０万回、さらにインデックスが使われればもっと効率的ですが、インデックスも使われずに２つのフィールドでテーブルスキャンが行われれば、最低でも２０万回のチェックが行われることになります。定石と正反対の結果になってしまいその要因は図れませんが、全体的な結論としては、「Accessの場合は定石にとらわれず、パフォーマンスをあまり意識しないでIs Nullを使っても大丈夫」と言えるのではないでしょうか？。
ちなみにこの結果は、Where句を適用するフィールドのインデックスの有無によってもまた違いで出てくる可能性が大いに考えられます。”Is Nullはこだわらなくてもよい”わけですが、もしもIs Nullを使ったことによって以前よりパフォーマンスが落ちた場合には、インデックスの見直しや一見無意味な主キーへのWhere条件の追加などを試してみる価値はあると思います。
