データのサイズは400MBを超えていて、数万件のデータから1レコードを検索をしようと思ったら1分近くかかるシステムだった。
外注の書いたコードか前任者の追加コードが原因かは今となってはわからない（仕様書もない）が
VBAソースコードを見直してみたらナポリタンの匂いがした
TCP Monitor Plusをダウンロードして、ファイルサーバーとクライアントPCのパケットをモニタリングしてみたところ1レコードの検索で80MBのデータを受信していることが判明(/・ω・)/
見直しをしてみたらテーブルのフィールドに適切なインデックスが設定されておらずRecordsetでFilter使いまくりだった
Indexの有無とFilter使った場合でどんだけ違うのかをテストしてみた
新しいAccessファイルを作りテーブル１にフィールド1～6を追加
フィールド1はインデックスをはい (重複あり)に指定
下のコードで500000件のテストデータを入れ込んだ
Option Compare Database
Option Explicit
Sub TestData()
Dim db As DAO.Database
Set db = CurrentDb
Dim l As Long
For l = 0 To 500000
db.Execute ("INSERT INTO テーブル1 (フィールド1,フィールド2,フィールド3,フィールド4,フィールド5,フィールド6) " & _
"VALUES ('" & GetGUID() & "','" & GetGUID() & "','" & GetGUID() & "','" & GetGUID() & "','" & GetGUID() & "','" & GetGUID() & "')")
Next
End Sub
'下のサイトを参考にさせてもらいました
'http://maeda0414.blog.fc2.com/blog-entry-26.html
Public Function GetGUID() As String
GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").Guid, 2, 36)
End Function
このファイルをファイルサーバに置きクライアント側にも新しいAccessファイルを作りモジュールに下のテストコードを書く
Option Compare Database
Option Explicit
Public Sub Test1()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
'インデックスを設定したフィールドを条件にして直にクエリ文を書いた
Set rs = db.OpenRecordset("SELECT * FROM テーブル1 WHERE フィールド1 = '884EBDD8-0516-480D-9C13-6F2EF60B2202'")
Do Until rs.EOF
Debug.Print ("Test1 " & rs!ID)
rs.MoveNext
Loop
End Sub
Public Sub Test2()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
'インデックスの設定をしていないフィールドを条件にして直にクエリ文を書いた
Set rs = db.OpenRecordset("SELECT * FROM テーブル1 WHERE フィールド2 = '7BFD061C-711E-404F-89D3-0973CADFF5F6'")
Do Until rs.EOF
Debug.Print ("Test2 " & rs!ID)
rs.MoveNext
Loop
End Sub
Public Sub Test3()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
'Filterプロパティにインデックスを設定したフィールドの条件を書いた
Set rs = db.OpenRecordset("テーブル1")
rs.Filter = "フィールド1 = '884EBDD8-0516-480D-9C13-6F2EF60B2202'"
Set rs = rs.OpenRecordset
Do Until rs.EOF
Debug.Print ("Test3 " & rs!ID)
rs.MoveNext
Loop
End Sub
Public Sub Test4()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
'Filterプロパティにインデックスの設定していないフィールドの条件を書いた
Set rs = db.OpenRecordset("SELECT * FROM テーブル1 WHERE フィールド2 = '7BFD061C-711E-404F-89D3-0973CADFF5F6'")
Set rs = db.OpenRecordset("テーブル1")
rs.Filter = "フィールド2 = '7BFD061C-711E-404F-89D3-0973CADFF5F6'"
Set rs = rs.OpenRecordset
Do Until rs.EOF
Debug.Print ("Test4 " & rs!ID)
rs.MoveNext
Loop
End Sub
リンクテーブルを張ってテスト
結果
Test1 送受信量 226KB
Test2 送受信量 153MB
Test3 送受信量 157MB
Test4 送受信量 157MB
まとめ
適切なインデックス設定を行う事とRecordsetのFilterは使わないと心に誓った
