Excelファイルデータベースの読込みスピードは？(Excelデータベースその5)

ExcelVBA-Excelファイルをデータベースとして使う-その5です。前回の記事はこちらになります。
hirocom777.hatenadiary.org

本当にスピードが速いのか比べてみる

さて、今回はデータベース接続したときの読込スピードについて確認してみたいと思います。
VBAでファイルを開いて読むよりも、データベース接続のほうが速いって本当なんですかね。
同じデータにを読み込んでスピードを比べてみようと思います。

とはいっても、多少のデータではスピードの差なんて測れませんね。そこで、以下のサイトからデータをいただいて使わせてもらおうと思います。
jusyo.jp

こちらの全国データ(CSVファイル-約15万件分)をxlsx形式に変換して使わせていただきます。ありがとうございます！！
ファイル名は『zenkoku.xlsx』シート名も『zenkoku』としています。

今回は、このデータから都道府県名が『静岡県』のデータのみ抽出して速さを比べてみようと思います。

VBAで検索、コピーした場合

まずは、VBAだけでやってみましょう。『zenkoku.xlsx』と同じファルダに別のxlsmファイルを作成してシートモジュールに以下のプロシージャを置いて実行してみてください。ファイルを開いてからフィールド『都道府県』を検索して『静岡県』とある行をコピーしていきます。

Option Explicit

Sub VBA_Test()
Dim wbk As Workbook
Dim rngArea As Range
Dim rngRange As Range
Dim strAddress As String
Dim i As Long

  Debug.Print Now
  Application.ScreenUpdating = False
  
  Set wbk = Workbooks.Open(Filename:=ThisWorkbook.Path & "\zenkoku.xlsx")
  
  wbk.Sheets("zenkoku").Range("1:1").Copy Me.Range("1:1")
  
  Set rngArea = wbk.Sheets("zenkoku").Range("H:H")
  Set rngRange = rngArea.Find(What:="静岡県", LookAt:=xlWhole)
  strAddress = rngRange.Address
  i = 2
  Do
    rngRange.EntireRow.Copy Me.Cells(i, 1).EntireRow
    Set rngRange = rngArea.FindNext(rngRange)
    i = i + 1
  Loop While strAddress <> rngRange.Address
  wbk.Close
  
  Application.ScreenUpdating = True
  Debug.Print Now
End Sub

シートには、静岡県のデータ(約3700件)だけコピーされたと思います。

かかった時間は僕の環境で……約50秒！！まずまずの時間です。
因みに高速化のために定番の"Application.ScreenUpdating = False"を記述しています。これを記述しないと約55秒となりました。

ADOを使った場合

お次は、いよいよADOを使った場合！！以下でご紹介したSelectRecordを使えばいいですね。
hirocom777.hatenadiary.org
でもその前に、VBAだけのコードではフィールド名の表示もしています。SelectRecordはレコードの取得だけなので、別途フィールド名を取得して表示させましょう。以下が、フィールド一覧を取得するプロシージャです。標準モジュールに追加してください。

Function GetFieldList(strFileName As String, strSheetName As String) As Variant
On Error GoTo HrrorHandler
  Dim strField As String
  Dim adoCn As Object
  Set adoCn = CreateObject("ADODB.Connection")
  
  With adoCn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties") = "Excel 12.0"
    .Open strFileName
  End With
  
  Dim adoRs As Object
  Set adoRs = CreateObject("ADODB.Recordset")
  adoRs.Open "[" & strSheetName & "$]", adoCn
  
  Dim adoField As ADODB.Field
  For Each adoField In adoRs.Fields
    strField = strField & "," & adoField.Name
  Next
  
  GetFieldList = Split(Mid$(strField, 2), ",")
  
  adoRs.Close
  adoCn.Close

HrrorHandler:
  If Err.Number <> 0 Then MsgBox "データベース接続に失敗しました"

End Function

データベースのファイル名と、対象となるテーブルのシート名を指定するとVariantの配列でフィールド名の一覧を返します。
その上で、シートモジュールに以下のプロシージャを置いて実行してみてください。SQLでフィールド『都道府県』が『静岡県』となっているレコードを抽出してコピーします。

Option Explicit

Sub ADO_Test()
Dim strSQL As String
Dim vrnField As Variant
  Debug.Print Now

  vrnField = GetFieldList(ThisWorkbook.Path & "\zenkoku.xlsx", "zenkoku")
  Me.Range(Me.Range("A1"), Me.Range("A1").Offset(0, UBound(vrnField))) = vrnField

  strSQL = "SELECT * FROM [zenkoku$]  WHERE 都道府県 = '静岡県'"
  SelectRecord ThisWorkbook.Path & "\zenkoku.xlsx", strSQL, Me.Range("A2")

  Debug.Print Now
End Sub

すごくシンプルですね。さて、かかった時間は……6秒！！速い！！
あと、このプロシージャ'には"Application.ScreenUpdating = False"を入れていません。入れても入れなくても時間に変わりはありませんでした。