空腹おやじのログと備忘録

VBA(主にExcel)でいろいろな実験的な事とか、Linuxのコマンドとか設定とかについて忘れないように、あれこれと・・・

【VBA】UTF-8 CSV の読み込みを、ADODB.Recordset と ADODB.Stream で比べてみた・・・第1回 1行ずつ読み込みしてみる

初めに

以前、ADODB.Recordset を使って、CSV の読み込みについてまとめたのだけれど、
世間一般では、UTF-8CSVに限って言えば、ADODB.Stream を使うのがメジャーなようです。
というか、「ADODB.Recordset の記事あるの?」って感じです。

自分が紹介した ADODB.Recordset による方法のマイナー感が拭えなくて、ちょっと悔しかったこともあり、2つを比べてみました。

また、後からまとめていた際に、分かってきたこともあり、追加調査分も含めて2回に分けて書いていこうと思います。

z1000s.hatenablog.com

データ

まずは、使うデータ。
自分で作るのは面倒なので、公開されているものを探したら
以下のサイトが見つかったので、使わせてもらうことにした。
使ったのは、その中の東京のデータ(13_tokyo_all_20190930.csv

  • 文字セット:UTF-8
  • 列数:30
  • レコード数:1,063,583 <=== Excelのシート1枚に入り切らない!!!
  • ヘッダ:無し
  • 改行コード:LF(0x0A)

ADODB.Streamを使う場合には改行コードが、CRLFではないので、指定が必要です。
先頭2行抜粋したのが以下

1,1000011000005,01,1,2018-04-02,2015-10-05,"国立国会図書館",,101,"東京都","千代田区","永田町1丁目10-1",,13,101,1000014,,,,,,,2015-10-05,1,"National Diet Library","Tokyo","1-10-1,Nagatacho, Chiyoda ku",,"コクリツコッカイトショカン",0
2,1000012010003,01,1,2018-04-02,2015-10-05,"内閣法制局",,101,"東京都","千代田区","霞が関3丁目1-1中央合同庁舎第4号館",,13,101,1000013,,,,,,,2015-10-05,1,"Cabinet Legislation Bureau","Tokyo","3-1-1,Kasumigaseki, Chiyoda ku",,"ナイカクホウセイキョク",0

ダウンロード元
t.co

処理概要

ADODB.Recordset

ADODBでデータベースに接続する場合と同様の処理です。
接続文字列を設定して、SQL発行して、Recordset を取得し、EOFまでループします。

ADODB.Stream

一般的に公開されているサンプルと同様の処理です。
Stream を開いて、LoadFromFile でロードして、
ReadText(adReadLine)で1行ずつ読み込み、Splitして、
EOFまでループします。

コード

事前準備

参照設定:Microsoft ActiveX Data Objects 6.X Library
バージョンは、お使いの環境に合わせて・・・

共通部分
Option Explicit

Private Const TARGET_FOLDER As String = "C:\Datas\CSV\法人情報\"
Private Const TARGET_NAME   As String = "13_tokyo_all_20190930.csv"
ADODB.Recordset
Public Sub readByAdoRecordset()

    Dim cn  As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs  As ADODB.Recordset
    Dim lRecords    As Long
    Dim i           As Long

    Dim sgStart     As Single
    Dim sgStop      As Single

    Dim v           As Variant

    sgStart = Timer

    On Error GoTo ERR_EXIT

    Set cn = New ADODB.Connection

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=" & TARGET_FOLDER & ";" _
            & "Extended Properties=""Text;" _
            & "HDR=No;" _
            & "FMT=Delimited"""

    Set cmd = New ADODB.Command

    Set cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "SELECT * FROM [" & TARGET_NAME & "]"

    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseServer
'    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly

    rs.Open cmd

    lRecords = 0

    Do Until rs.EOF
        lRecords = lRecords + 1

        v = rs.GetRows(1)
    Loop

ERR_EXIT:
    If Err.Number <> 0 Then
        Debug.Print "[" & Err.Source & "]" & "[" & CStr(Err.Number) & "] " & Err.Description
    End If

    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then
            rs.Close
        End If

        Set rs = Nothing
    End If

    If Not cmd Is Nothing Then
        Set cmd.ActiveConnection = Nothing

        Set cmd = Nothing
    End If

    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then
            cn.Close
        End If

        Set cn = Nothing
    End If

    sgStop = Timer

    Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]"

End Sub
Scema.ini
[13_tokyo_all_20190930.csv]
Format=CSVDelimited
CharacterSet=65001
ColNameHeader=False
ADODB.Stream(1行ずつ読み込み)
Public Sub readByAdoStream()

    Dim strm        As ADODB.Stream
    Dim sLine       As String
    Dim vItems      As Variant

    Dim lRecords    As Long
    Dim i           As Long

    Dim sgStart     As Single
    Dim sgStop      As Single

    sgStart = Timer

    On Error GoTo ERR_EXIT

    Set strm = New ADODB.Stream

    With strm
        .Type = adTypeText
        .Charset = "UTF-8"
'今回使用したファイルの改行コードは、LF
'        .LineSeparator = adCRLF
        .LineSeparator = adLF
        .Open

        .LoadFromFile TARGET_FOLDER & TARGET_NAME

        Do Until .EOS
            sLine = .ReadText(adReadLine)

            vItems = Split(sLine, ",")

            lRecords = lRecords + 1
        Loop

        .Close
    End With

ERR_EXIT:
    If Err.Number <> 0 Then
        Debug.Print "[" & Err.Source & "]" & "[" & CStr(Err.Number) & "] " & Err.Description
    End If

    If Not strm Is Nothing Then
        If strm.State = adStateOpen Then
            strm.Close
        End If

        Set strm = Nothing
    End If

    sgStop = Timer

    Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]"

End Sub

結果

今回の方法では、結果は以下の通り、
ADODB.Recordset を使った方が圧倒的に速い
Split 無しの場合で、約20倍
Split 有りの場合で、約6倍
の速度となりました。
あくまで、今回の方法ではです。(重要!)

種類カーソル
ロケーション
Split有無時間(秒)備考
RecordsetServer6.76
24.11
Client24.10
34.97
Stream-134.881回目
340.792回目
-145.611回目
353.992回目

但し、上記の結果は全てをそのまま鵜呑みには出来ない部分もあります。

  1. ADODB.Recordset の各フィールドデータと、Splitによる分割をした結果が明らかに異なっている。
    これは処理したコードの問題。(こちらにサンプル)
  2. ADODB.Stream の読み込みが、2回目以降が極端に遅くなること。

1項は、1行のデータを要素に分割する手段として、単純に Split しただけのため、

  • 「"」で囲まれた要素は、「"」が残る。
  • 「"」で囲まれた要素の中に、「,」がある場合、本来1つの要素が複数に分割されてしまった。

ことが原因です。
こちらにもう少し詳しくまとめてあります。

Split 有りのデータについては、参考程度に御覧ください。

2項は根本原因はわかりません。
(予想される要因はありますが、あくまで予想であり、コードで対応できる問題ではなさそうなので・・・)
ただ、改善策はあるので、それは次回とします。

要素分割が正しく行われていなかった・・・
元になるCSVの1行に、こんなデータがありました。

1,1000011000005,01,1,2018-04-02,2015-10-05,"国立国会図書館",,101,"東京都","千代田区","永田町1丁目10-1",,13,101,1000014,,,,,,,2015-10-05,1,"National Diet Library","Tokyo","1-10-1,Nagatacho, Chiyoda ku",,"コクリツコッカイトショカン",0

この中に、

"1-10-1,Nagatacho, Chiyoda ku"

"」で括られた中に、「,」があるので、以下の様になってしまいました。

ADODB.Recodeset 1-10-1,Nagatacho, Chiyoda ku
ADODB.Stream "1-10-1 Nagatacho Chiyoda ku"

ADODB.Recorset は、望んだ値が得られています。
一方、ADODB.Stream の方は、何も考えず、Split しただけなので、当然残念な結果になっています。
分割もおかしいし、要素の前後の「"」も残っています。
今回は、読み込み速度の比較がメインなので、このままとします。
上記の対応をしたところで、現時点での速度差が圧倒的なので、無視できる範囲内と想定できるので。
それに、対応すると、差が広がる一方なので・・・

まとめ

それぞれの方法の比較
項目 Recordset Stream 備考
速度 速い 遅い
2回目以降は特に遅い
要素を分割する処理コード 不要 必要
改行コード指定 不要 必要(CRLF以外の場合)
Schema.ini 必要 不要
接続文字列 必要
少し複雑
不要

上にも書いたのですが、要素の分割の手間を考えると、
個人的には、ADODB.Recordset の使用がオススメです。
処理速度の点もありますが

  • Schema.ini を書く手間
  • Split処理を書く手間

「どちらを選択するか?」と言われれば、私は、Schema.ini 書きます。
必要なら、Schema.ini のテンプレート作るツール作ります。(たぶん・・・)

使うデータにも依存するので、どちらを選ぶかは、使う方の判断で・・・

次回予告

今回の結果だけを見ると「ADODB.Recrdset の圧勝」となってしまったのだけれど、色々調べていると
1行読み込みが遅い時の対処法
というのを見つけたので、これについて検証してみます。
また、この対応をしたコードで同じファイルを使って速度を比較してみます。
mussyu1204.myhome.cx


2019/10/23 追記
続編はこちら
z1000s.hatenablog.com