Script事業案内エクセル以外ホームSQL入門データの挿入:バルクインサート最終更新日:2019-12-03データの挿入:バルクインサートエクセルVBAでデータベースを扱うためのSQL入門です。前回でデータをINSERTすることができるようになりましたが、処理時間に不満がありました。データベースを使うのでしたら、エクセルで扱いに困ってしまうような大量データをストレスなく扱いたいところです。今回は大量データをINSERTしてもストレスなく使えるようにVBAを改修していきます。前回のデータの挿入(INSERT)と全削除で作成したワークシートおよびテーブルを使用します。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを自由に作れるようになり、ワークシートからの自動作成も出来上がりました。今回は作成したテーブルにデータを追加(INSERT)していきます。テーブル定義とワークシートは以下になります。TEXT);データ件数は必要に応じて行コピーしました。最終的には100万行まで作成しましたが、さすがにパソコンが悲鳴を上げていました。ファイルサイズも100MBを超えてしまいます。それでも、一度作成してしまえば、なんとかエクセルでも扱えます。前回の実行時間は、10,000件で44秒でした。そこで、上記処理のそれぞれの時間割合がどうなっているかです。SQL発行をせずにSQL文字列だけ作成した場合は、1秒程度で終わります。INSERTに時間がかかっています。これを改善する最も有効な方法としてバルクインサートがあります。複数のINSERT文をまとめて一気に実行します。バルクインサートについては、DBごとにサポートされる内容が違ってきます。専用のコマンドや、CSVからロードしたり、いろいろいなものがあります。SQLiteでも、CSVからIMPORTするコマンドが用意されています。今回は、割と多くのDBでサポートされている、複数のINSERTをまとめて実行するSQLの書き方になります。)このSQLをデータ件数分発行することになります。ちなみにADOでは、;セミコロンでつなげた複数行のINSERT文を発行することはできません。仮にできたとしても、実際のSQL実行は1行ずつなので速度はあまり変わりません。複数のINSERTをまとめるには、専用の構文があります。),・・・VALUESの後の()ごとに1行ずつのデータをいれ、()をカンマでつなげていきます。このSQL発行で、複数のデータを一気に追加できます。このSQLについては、DBや環境により制限があります。・SQL文の文字列長SQLiteの最新版では、それぞれの制限値について確認が取れませんでした。今回のSQL入門用にダウンロードした最新のSQLiteでは、どちらの制限も超えています。したがって、極端なことをしないのであれば、制限値はあまり気にする必要がないようです。ちなみに、VBAのString型は20億文字なので全く気にする必要はありません。バルクインサートのVBAコードと動作検証バルクインサートに対応したVBAになります。クラスについては前回作成した「クラスモジュール:「clsSQLite」の全VBAコード」こちらを使います。SQL入門の「データの挿入(INSERT)と全削除」時点のVBAクラスの全コードです。Function※100件ごとにまとめたSQL文を作成しています。約4.8秒もう感動ものです、劇的に速くなっています。1万件程度で喜んでいてはいけません。100万件くらい処理したいところです。10万件でやってみましょう。件数が多くなったので当然その分の時間がかかってますが、十分に速そうに思われます。100万件やるのは躊躇する程度には時間がかかっています。以下、何通りかの条件で実行した結果の一覧です。52.1※時間は目安です。※テーブルの全データ削除後に実行しています。納得いかないですよね。バルク数を1,000件にしても大して変わらないし、10,000件にしたら・・・おかしい、何かがおかしいです。約51.7秒SQL発行していないので、SQL文字列作成しているだけです。間違いなく、文字列作成にほとんどの時間を費やしているという事です。これは、SQLiteの問題ではなく、VBAの問題だという事です。文字列操作を改善する方法とVBAコードVBAの文字列操作、つまり、Stringデータ型の処理はとても遅いのです。空文字列の扱い方と処理速度について(""とvbNullString)空文字列と書きましたが、空文字列という表現がかなり曖昧な表現になっています。ここでいう空文字列とは、文字列が入るべき場所に、何も入っていない(ように見える)状態を指しています。VBAにおいては、空文字列の状態が2つあります。聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型(可変長文字列)についての基礎知識が必要です。後者のページで紹介している、Midステートメントを使用したStringbuilderクラスを少し改修して使います。シートへのアクセス改善に配列を使用するようにしました。セル範囲⇔配列(マクロVBA高速化必須テクニック)|VBA入門セル範囲をVariant型変数に入れる事で、配列を作成することができます。配列をセル範囲にまとめて出力する事も出来ます。これは、マクロVBAを高速処理したい時の必須テクニックになります、マクロの処理が遅い場合は、このテクニックが使えないか検討してください。PC環境によっては大きなセル範囲を配列に入れる時点でエラーになる可能性があります。以下は、これらを組み込んだVBAの全コードになります。ADOの部分については、クラスモジュール:「clsSQLite」の全VBAコード、こちらをそのまま使っています。SQL入門の「データの挿入(INSERT)と全削除」時点のVBAクラスの全コードです。クラスモジュール:clsSQLiteさらに、汎用的に使えるように引数で情報(DB、セル範囲、バルク数)を渡して使えるようにしています。上で紹介したVBAの標準モジュールでの使い方です。約2.6秒どうでしょう、これなら問題ないでしょう。いうことで100万件に挑戦しました。約25秒これなら、もう十分な速度ではないでしょうか。SQL発行を止めて計測すると15.5秒くらいなので、これ以上無理に速度アップさせても何秒も短縮されず効果は少ないでしょう。100万件×10列で25秒バルクインサートの最後に2回に渡ってVBAからデータ追加するINSERTを解説しました。100万件×10列で25秒という結果も出ましたので、これで十分でしょう。いいますか、エクセルでは行数をこれ以上増やせませんし、これ以上列数増やしたらファイルが大きくなりすぎて、エクセルでは扱うのが厳しくなってしまいます。getValuesこの中をStringbuilderで改修すると速くなるはずです。文字列長が短かったり結合回数が少ない場合、Stringbuilderクラスを使うとかえって時間がかかってしまいますので、今回は列処理は通常の&結合のままにしてあります。次回から、SQLの核心、SELECT文に入っていきます。Next)|VBA入門8.セルに文字を入れるとは(Range,Value)|VBA入門9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説10.ひらがな⇔カタカナの変換|エクセル基本操作ホームSQL入門データの挿入:バルクインサートこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。GASスプレッドシート入門スプレッドシート応用GAS入門GAS応用ホームSQL入門データの挿入:バルクインサートこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。