ゴミ箱

15年近く前に外注に出したAccessのシステムの保守を引き継いだ
データのサイズは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は使わないと心に誓った

ゴミ箱

ページ

2014年8月10日日曜日

MarkdownNote0.0.0.2

2014年8月8日金曜日

MarkdownNote0.0.0.1

2014年4月23日水曜日

引き継いだAccessのシステムが激遅な件

2014年3月12日水曜日

忘れていた