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

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

【VBA】UTF-8 CSV の読み込みを、ADODB.Recordset と ADODB.Stream で比べてみた・・・第2回 ADODB.Streamを使ったコードの処理速度改善

前回のコードでは、完敗だったADODB.Stream ですが、汚名返上なるか?
見つけたサイトに掲載された方法による効果を検証してみます。


前回
z1000s.hatenablog.com


目次

前回の結果概要

処理時間

30列、100万行+αのCSVの読み込み速度を比較。
その結果、

処理方法 処理時間
ADODB.Recordset 7秒弱
ADODB.Stream 130秒強

と圧倒的な差。

不明な点
  1. ADODB.Stream があまりにも遅く、その原因が不明。
  2. 2回以上処理を繰り返すと、1回目に比べ、2回目以降がさらに遅くなる。

ADODB.Stream を使った処理の分析と改善

参考サイトの説明

参考としたサイトは、
www.mussyu1204.com
説明によると、「後ろの行ほどその行を取得するための時間が長くなっていく
ことが原因とのこと。
今回のコードで、どの程度の変化があるのかを確認してみる。

1万行あたりの読み込み時間

1万行処理毎に開始からの経過時間を出力し、その差から1万行あたりの処理時間を求めてグラフにしてみた。
f:id:Z1000S:20191023095428j:plain
リンク先のサイトの説明にあるように
行が進むにつれて、読み込みに要する時間が増加していくのが確認できる。
それ以外にも

  • 2回目以降は、6~7万件を超えるあたりから、1回目よりも遅いことも確認できる。
  • 2回目と3回目は同じような傾向にあり、その差は小さい。

といった事がわかる。

注)

  • 1回目とは、Excel 起動後に最初にプロシージャを実行したもの
  • 2回目とは、1回目の処理終了後、Excel を終了させずに、再度プロシージャを実行したもの
  • 3回目とは、2回目の処理終了後、Excel を終了させずに、再度プロシージャを実行したもの
改善策

リンク先サイトに書かれている方法
指定した文字数を読み込み、その中から行データを切り出すという方法をとっている。
ただし、リンク先のコードは、改行コードがCRLFなので、今回のデータの場合にはLFに変更する必要がある。

実行結果
1回に読み込む文字数を、512~16,382文字の範囲で変えて、それぞれの場合の時間を計測してみた。

結果として、1回で読み込む文字数を増やしていくと、

  • 読み込み時間が短くなっていくことが確認できる。
  • 1回目と2回目以降の差が小さくなっていくことが確認できる。

まとまった文字数の読み込みが、効果があることが確認できた。

全データの読み込みにかかった時間は、以下の通り。
大幅に改善されている。

1回で読み込む文字数時間(秒)
Split 有Split 無
51237.6329.51
102415.538.64
204815.328.60
409610.924.16
819210.553.77
1638410.243.45
1行(1回目)145.61134.88
1行(2回目)353.99340.79

読み込み文字数別
縦軸のスケールが、グラフによって異なるので注意。
f:id:Z1000S:20191023105933j:plain
f:id:Z1000S:20191023105952j:plain
f:id:Z1000S:20191023110006j:plain
f:id:Z1000S:20191023110110j:plain
f:id:Z1000S:20191023110121j:plain
f:id:Z1000S:20191023110133j:plain

読み込み回別
f:id:Z1000S:20191023110334j:plain
f:id:Z1000S:20191023110346j:plain
f:id:Z1000S:20191023110355j:plain

文字数固定読み込みのコード
ADODB.Stream の ReadText メソッドに渡すパラメータに、読み込む文字数を指定するとで、必要な文字数の読み込みができる。
今回のコードでは、定数 READ_CHAR_NUM に読み込む文字数を設定して、1回あたりの読み込み文字数を変えている。

ADODB.Recordset との比較

ADODB.Recordset の読み込み速度
ADODB.Stream と同様に、1万件毎の読み込み時間を計測してみた。
その結果、

  • ADODB.Recordset を使用した場合、読み込み行数に対する依存は確認できない。
  • 1万行あたりの読み込み時間は、カーソルロケーションをClient とした場合が、最速であった。

f:id:Z1000S:20191023113352j:plain
f:id:Z1000S:20191023113406j:plain

ADODB.Recordset と、ADODB.Stream の比較

ADODB.Recordset と ADODB.Stream(1回目)の比較
ADODB.Recordset の方が速い事は変わらないが、ADODB.Stream でも、条件によって、それに迫る結果を出すことが出来ている。
f:id:Z1000S:20191023120639j:plain

感想、考察

ファイル後半の行ほど、読み込みが遅い事に関して

あくまでも推測なのだが、ADODB.Stream の読み込みは、
読み込みするポインタ位置を、毎回ファイル先頭から探しているような印象がある。
そのため、
1行毎に読み込むから遅い
のではなく、
「(行単位であろうと、文字数単位であろうと)読み込みを行った回数が多くなるほど遅くなる
となっているというのが実情なのではないかと思っている。

2回目が1回目より遅い事に関して

原因は不明。
Microsoft 公式によるClose メソッドの説明より抜粋
同サイトの日本語翻訳がおかしいので、英語のままで。

Use the Close method to close a Connection, a Record, a Recordset, or a Stream object to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and open it again later. To completely eliminate an object from memory, close the object and then set the object variable to Nothing (in Visual Basic).

https://docs.microsoft.com/ja-jp/sql/ado/reference/ado-api/close-method-ado?view=sql-server-ver15

Googleさんに翻訳してもらうと、こんな感じ。

Closeメソッドを使用して、Connection、Record、Recordset、またはStreamオブジェクトを閉じ、関連するシステムリソースを解放します。オブジェクトを閉じても、メモリからは削除されません。プロパティ設定を変更して、後で再度開くことができます。オブジェクトをメモリから完全に削除するには、オブジェクトを閉じてから、オブジェクト変数を(Visual Basicで)Nothingに設定します。

こう書かれてはいるが、Colse 後に、Nothing を設定しても、メモリ上に残っているような感じを受けた。
1回目の処理でゴミが残っていて、そのまま2回目を行うと、1回目のような結果を出せていないように思える。

実際に、1回目終了後、そのまま30分放置し、再度同じ処理を走らせても、結果は1回目直後に実施した場合との違いは認められなかった。
その直後に、Excel を終了させ、再度 Excel を立ち上げて、同じプロシージャを走らせると1回目のパフォーマンスが出ている。
ADODB.Stream 変数に対し、Nothing を設定しても、開放処理がきちんと行われていないような気がする。
もし本当にそうなのであれば、VBAのコードでは対処のしようがない。

ADODB.Recordset のカーソルロケーションが Client の場合、1万行あたりの読み込み速度と総読み込み時間がアンマッチな理由

カーソルロケーションを Client に設定した時が、1万行あたりの読み込み速度が1番速かったが、最終的な読み込み完了までの時間は、Server に設定した場合よりも遅くなっている。
これは、カーソルロケーションによって内部の処理の内容が変わることが変わることが原因で、間違っているわけではない。

サーバ側データのスナップショットコピーがクライアント側の Recordset オブジェクト内に一括して取り出されます。

https://blogs.msdn.microsoft.com/nakama/2008/10/16/ado/

上の記事内には書かなかったが、処理を開始してから先頭のレコードの読み込みを開始するまでに要する時間の差が非常に大きいため、このような現象が起こっている。

カーソルロケーション先頭のレコード
読み込み開始までの所要時間(秒)
Server0.47
Client23.00
これは読み込むレコード数が多いほど顕著に現れるようである。
このため、読み込むレコード数によってカーソルロケーションを選ばないと、よいレスポンスが得られない。
通常は、Client 側で良いと思うが、ある程度以上のレコード数があり、速度が出ない場合には、Server 側で試してみると改善する場合があるかもしれない。

まとめ

不明な点として上げた現象の根本的な原因は不明。
ただし、改善策として、今回のコードの有効性は確認できた

  • ADODB.Stream を使用する場合
    • 読み込むデータ量が多く、速度が出ない場合には、面倒でも1行ずつではなく、ある程度まとまった文字数を読み込み、都度改行コードで分割して処理したほうがよい。
    • その場合、1回に読み込む文字数が少ないと速度が出ない。
    • 1行ずつ読み込みを行う場合は、データ量が少ない場合に限定したほうがよい。
    • 改行コードが、CRLF以外の場合は、コード内で指定が必要
    • 自分で区切り文字に応じた分解処理が必要
  • ADODB.Recordset を使用する場合
    • レコード数が少なければ、カーソルロケーションを Client、多ければ、Server に設定。
    • Schema.ini が原則必要(文字セット、ヘッダ有無、区切り文字等。必要であれば各フィールド情報)
    • 区切り文字に応じた分解処理は不要であるが、自分の意図した分割がされない場合には、Schema.ini の内容見直しが必要になる場合があるかもしれない。
    • Recordset からのデータ取り出し時、Null 対応が必要な場合がある。(( ゚д゚) そんなの何処に出てきた?)

今回は、CSVの読み込みとしたが、実際には、CSVに限らず、プレーンテキストの場合でも同様のことが起こる事が考えられる。
その場合にもおそらく有効に使えそうな気がする。

おまけ

改行コードが、CRLFの場合、多分こんな感じでできると思います。(動作未確認なので、使う場合には確認してから使って下さい)