タイトルそのままですが。
今日試して撃沈したネタをご紹介します。
セル範囲を配列へ取り込み高速化
VBAでの処理高速化ネタの一つにセルへのアクセスを極力減らすというものがあります。
そのためにセル範囲を一旦配列へ格納してから諸々の処理を行うと処理が劇的に高速化されます。
セルの配列への格納方法はVariant型変数を宣言してセル範囲の値を代入するだけです。
Dim myVar as Variant myVar = Range("A1:B10").Value
今回２つの例を試してみました。
データ量：2,458×58
| 処理方法 | 処理時間 |
|---|---|
| ①Excel→SQLiteへInsert | 0.71秒 |
| ②Excel→配列→SQLiteへInsert | 0.69秒 |
①はInsert文を発行するたびにセルへのアクセスが発生します。
②は先にセル範囲を全て配列に格納して、Insert文を発行する時は配列にアクセスしています。
この例の場合は誤差範囲でしか差が出ませんでしたが、一応配列を使用したほうが速くなっています。
差を検知するにはデータ量が少なかったようです。
次は今回の本命のデータで試してみました
メモリ容量という物理的限界
データ量：566,401×70のCSVファイル。213MB。
| 処理方法 | 処理時間 |
|---|---|
| ①CSVインポート→ExcelからSQLiteへInsert | 255.29秒 |
| ②CSVから配列へ転記→配列からSQLiteへInsert | 計測不可 |
①は計測して初めてわかりましたが4分以上もかかっている処理です。(実は元ネタのCSVを出力するのに一番時間がかかってるけど)
これを配列を使って高速化処理したらどうなるのでしょうか。
既にネタバレしていますが)いざ実測！
… … … メモリ不足で処理が止まった … … … 無念 …。
会社のPCは４GBでマクロ実行前の空き容量も２GB弱あったので200MB強のファイルなら行けるかと思ったら、メモリ不足で配列に取り込む途中で止まってしまいました。
ファイル容量分だけメモリを消費するのではないんですね。
まとめ
配列に取り込むと高速化は鉄板処理ですが、何でもかんでもというわけには行かないようです。 データ容量によって使い分けが必要なようです。
データ容量が大きくなりそうだったら分割して配列への取り込みとかもありなんだろうか？
今回の例だったら56万件一度にはは無理でも、10万件ずつならいけるとか？
また折を見て試してみたいと思います。
