SQL入門
データの挿入:バルクインサート

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-03

データの挿入:バルクインサート


エクセルVBAでデータベースを扱うためのSQL入門です。
前回でデータをINSERTすることができるようになりましたが、処理時間に不満がありました。


せっかくデータベースを使うのでしたら、エクセルで扱いに困ってしまうような大量データをストレスなく扱いたいところです。
今回は大量データをINSERTしてもストレスなく使えるようにVBAを改修していきます。

テスト用のテーブル作成

前回のデータの挿入(INSERT)と全削除で作成したワークシートおよびテーブルを使用します。
エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを自由に作れるようになり、ワークシートからの自動作成も出来上がりました。今回は作成したテーブルにデータを追加(INSERT)していきます。
テーブル定義とワークシートは以下になります。

CREATE TABLE t_sales (
 'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,'code' TEXT NOT NULL
,'name' TEXT NOT NULL
,'address' TEXT
,'sales_date' TEXT
,'item_code' TEXT
,'item_name' TEXT
,'item_price' INTEGER
,'item_count' INTEGER
,'item_amount' INTEGER
,'comment' TEXT
);


VBA マクロ SQL ADO

データ件数は必要に応じて行コピーしました。
最終的には100万行まで作成しましたが、さすがにパソコンが悲鳴を上げていました。
ファイルサイズも100MBを超えてしまいます。
それでも、一度作成してしまえば、なんとかエクセルでも扱えます。

VBAの改善点の概要

VBAでワークシートからINSERTしている処理を単純化すると、
・シートの1行分のデータからSQL文字列作成
・SQL発行→SQLiteでSQL実行

前回の実行時間は、10,000件で44秒でした。
そこで、上記処理のそれぞれの時間割合がどうなっているかです。
SQL発行をせずにSQL文字列だけ作成した場合は、1秒程度で終わります。

つまり、INSERTに時間がかかっています。
これを改善する最も有効な方法としてバルクインサートがあります。
複数のINSERT文をまとめて一気に実行します。

バルクインサートについては、DBごとにサポートされる内容が違ってきます。
専用のコマンドや、CSVからロードしたり、いろいろいなものがあります。
SQLiteでも、CSVからIMPORTするコマンドが用意されています。
今回は、割と多くのDBでサポートされている、複数のINSERTをまとめて実行するSQLの書き方になります。

バルクインサートのSQL解説

前回紹介した通常のiNSERTは、
INSERT INTO table_name [ column1 , column2 , ・・・ ] VALUES (value1 , value2 , ・・・ )

このSQLをデータ件数分発行することになります。
ちなみにADOでは、;セミコロンでつなげた複数行のINSERT文を発行することはできません。
仮にできたとしても、実際のSQL実行は1行ずつなので速度はあまり変わりません。
複数のINSERTをまとめるには、専用の構文があります。

INSERT INTO table_name [ column1 , column2 , ・・・ ] VALUES
(value1 , value2 , ・・・ ), (value1 , value2 , ・・・ ), (value1 , value2 , ・・・ ),・・・

VALUESの後の()ごとに1行ずつのデータをいれ、()をカンマでつなげていきます。
このSQL発行で、複数のデータを一気に追加できます。

このSQLについては、DBや環境により制限があります。
・一度にまとめられるデータ件数
・SQL文の文字列長
SQLiteの最新版では、それぞれの制限値について確認が取れませんでした。
WEBを調べてみると、500件だったが解除されたとか、100万バイトまでとか出ていますが、
今回のSQL入門用にダウンロードした最新のSQLiteでは、どちらの制限も超えています。
したがって、極端なことをしないのであれば、制限値はあまり気にする必要がないようです。
ちなみに、VBAのString型は20億文字なので全く気にする必要はありません。

バルクインサートのVBAコードと動作検証

バルクインサートに対応したVBAになります。
クラスについては前回作成した「クラスモジュール:「clsSQLite」の全VBAコード」こちらを使います。
SQL入門の「データの挿入(INSERT)と全削除」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite



Sub BulkInsert()
  Dim sTime As Double: sTime = Timer
  
  Dim clsDB As New clsSQLite
  clsDB.DataBase = "C:\SQLite3\sample.db"
  
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim maxRow As Long
  maxRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
  
  Dim sSql As String
  Dim sSqlH As String, sSqlC As String, sSqlV As String
  Dim i As Long, cnt As Long
  sSqlH = "INSERT INTO t_sales "
  sSqlC = getValues(ws, 1, """") 'ダブルクオートで囲む
  sSqlV = ""
  cnt = 0
  clsDB.DbOpen
  
  For i = 2 To maxRow
    If sSqlV <> "" Then sSqlV = sSqlV & ","
    sSqlV = sSqlV & getValues(ws, i)
    cnt = cnt + 1
    If cnt = 100 Or i >= maxRow Then
      sSql = sSqlH & sSqlC & " VALUES " & sSqlV
      If Not clsDB.ExecuteNonQuery(sSql) Then
        MsgBox clsDB.ErrMsg
        Exit Sub
      End If
      sSqlV = ""
      cnt = 0
    End If
  Next
  
  clsDB.DbClose
  Set clsDB = Nothing
  Debug.Print Timer - sTime
End Sub

'指定シートの指定行のセル値からSQLの(value,…)を作成
Function getValues(ByVal ws As Worksheet, _
          ByVal i As Long, _
          Optional ByVal aQuote As String = "'") As String
  Dim sSql As String, j As Long
  With ws
    For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
      If LenB(sSql) > 0 Then sSql = sSql & ","
      'セルのValueのデータ型を自動判定
      Select Case TypeName(.Cells(i, j).Value)
        Case "Date"
          sSql = sSql & dateFormat(.Cells(i, j).Value)
        Case "Double"
          sSql = sSql & .Cells(i, j).Value
        Case Else
          sSql = sSql & addQuote(.Cells(i, j).Value, aQuote)
      End Select
    Next
  End With
  getValues = "(" & sSql & ")"
End Function

'SQLiteは日付型がないので文字列として格納
Function dateFormat(ByVal var As Variant) As String
  dateFormat = addQuote(Format(var, "yyyy-mm-dd"))
End Function

'シングルクオーテーションをエスケープ処理
Function addQuote(ByVal str As String, _
         Optional ByVal aQuote As String = "'") As String
  addQuote = aQuote & Replace(str, "'", "''") & aQuote
End Function

※100件ごとにまとめたSQL文を作成しています。

10,000件で実行した結果は、
約4.8秒
もう感動ものです、劇的に速くなっています。

しかし、まだまだ1万件程度で喜んでいてはいけません。
100万件くらい処理したいところです。
では、とりあえず10万件でやってみましょう。
100,000件で実行した結果は、
約16.1秒
件数が多くなったので当然その分の時間がかかってますが、十分に速そうに思われます。
でも、いきなり100万件やるのは躊躇する程度には時間がかかっています。

もっと早くならないものか・・・
まとめる件数を100件ではなく、1,000件とか10,000件とかまとめたらどうだろう・・・
以下、何通りかの条件で実行した結果の一覧です。

処理件数 バルク数 秒数
10,000 100 4.8
100,000 100 16.1
100,000 1,000 13.5
100,000 10,000 52.1
※時間は目安です。
※テーブルの全データ削除後に実行しています。

納得いかないですよね。
バルク数を1,000件にしても大して変わらないし、10,000件にしたら・・・
おかしい、何かがおかしいです。
どこに時間がかかっているのでしょうか?
SQL発行する部分(clsDB.ExecuteNonQuery)をコメントアウトして、
10万件でバルク数1万で実行してみると、
約51.7秒
SQL発行していないので、SQL文字列作成しているだけです。
時間差は目安にしかなりませんが、
間違いなく、文字列作成にほとんどの時間を費やしているという事です。

これは、SQLiteの問題ではなく、VBAの問題だという事です。

文字列操作を改善する方法とVBAコード

VBAの文字列操作、つまり、Stringデータ型の処理はとても遅いのです。

空文字列の扱い方と処理速度について(""とvbNullString)
空文字列と書きましたが、空文字列という表現がかなり曖昧な表現になっています。ここでいう空文字列とは、文字列が入るべき場所に、何も入っていない(ように見える)状態を指しています。VBAにおいては、空文字列の状態が2つあります。
遅い文字列結合を最速処理する方法について
VBAは遅い… よく聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型(可変長文字列)についての基礎知識が必要です。

後者のページで紹介している、Midステートメントを使用したStringbuilderクラスを少し改修して使います。
また、さらにシートへのアクセス改善に配列を使用するようにしました。
セル範囲⇔配列(マクロVBA高速化必須テクニック)|VBA入門
セル範囲をVariant型変数に入れる事で、配列を作成することができます。また、配列をセル範囲にまとめて出力する事も出来ます。これは、マクロVBAを高速処理したい時の必須テクニックになります、マクロの処理が遅い場合は、このテクニックが使えないか検討してください。
ただし、PC環境によっては大きなセル範囲を配列に入れる時点でエラーになる可能性があります。

以下は、これらを組み込んだVBAの全コードになります。
ADOの部分については、クラスモジュール:「clsSQLite」の全VBAコード、こちらをそのまま使っています。
SQL入門の「データの挿入(INSERT)と全削除」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite

さらに、汎用的に使えるように引数で情報(DB、セル範囲、バルク数)を渡して使えるようにしています。

クラスモジュール:clsStringBuilder


Option Explicit

Private sBuf As String
Private iBuf As Long

'現在の文字列長
Public Property Get Length() As Long
  Length = iBuf
End Property

'初期化
Private Sub Class_Initialize()
  sBuf = VBA.String$(32768, vbNullChar)
  iBuf = 0
End Sub

'文字列追加
Public Function Append(ByRef sValue As String) As clsStringBuilder
  If iBuf + Len(sValue) > Len(sBuf) Then
    sBuf = sBuf & VBA.String$(VBA.Len(sBuf) * 2 + VBA.Len(sValue), vbNullChar)
  End If
  Mid(sBuf, iBuf + 1) = sValue
  iBuf = iBuf + VBA.Len(sValue)
  Set Append = Me
End Function

'文字列クリア
Public Function Clear() As clsStringBuilder
  Call Class_Initialize
  Set Clear = Me
End Function

'文字列化
Public Function ToString() As String
  ToString = VBA.Left$(sBuf, iBuf)
End Function


Stringbuilderを使ったバルクインサート対応のVBA
Sub BulkInsertSbAry(ByVal sDb As String, _
          ByVal sTable As String, _
          ByVal argRange As Range, _
          Optional ByVal bulkCnt As Long = 10000)
  Dim clsDB As New clsSQLite
  clsDB.DataBase = sDb
  Dim ary
  ary = argRange.Value
  
  Dim sbSql As New clsStringBuilder
  Dim sSqlC As String
  Dim i As Long, cnt As Long
  
  'カラム名取得:(column,…)
  sSqlC = getValues(ary, LBound(ary, 1), """")
  
  'DB接続
  clsDB.DbOpen
  
  For i = LBound(ary, 1) + 1 To UBound(ary, 1)
    If sbSql.Length = 0 Then
      Call sbSql.Append("INSERT INTO t_sales ")
      Call sbSql.Append(sSqlC)
      Call sbSql.Append(" VALUES ")
    Else
      sbSql.Append (",")
    End If
    
    '(value,…)を作成
    Call sbSql.Append(getValues(ary, i))
    
    '指定件数のINSERTをまとめて実行
    If (i - 1) Mod bulkCnt = 0 Or i = UBound(ary, 1) Then
      If Not clsDB.ExecuteNonQuery(sbSql.ToString) Then
        MsgBox clsDB.ErrMsg
        Exit Sub
      End If
      sbSql.Clear
    End If
  Next
  
  'DB切断
  clsDB.DbClose
  
  '解法
  Set clsDB = Nothing
  Set sbSql = Nothing
End Sub

'指定シートの指定行のセル値からSQLの(value,…)を作成
Function getValues(ByRef ary, _
          ByVal i As Long, _
          Optional ByVal aQuote As String = "'") As String
  Dim sSql As String, j As Long
  For j = LBound(ary, 2) To UBound(ary, 2)
    If LenB(sSql) > 0 Then sSql = sSql & ","
    'セルのValueのデータ型を自動判定
    Select Case TypeName(ary(i, j))
      Case "Date"
        sSql = sSql & dateFormat(ary(i, j))
      Case "Double"
        sSql = sSql & ary(i, j)
      Case Else
        sSql = sSql & addQuote(ary(i, j), aQuote)
    End Select
  Next
  getValues = "(" & sSql & ")"
End Function

'SQLiteは日付型がないので文字列として格納
Function dateFormat(ByVal var As Variant) As String
  dateFormat = addQuote(Format(var, "yyyy-mm-dd"))
End Function

'シングルクオーテーションをエスケープ処理
Function addQuote(ByVal str As String, _
         Optional ByVal aQuote As String = "'") As String
  addQuote = aQuote & Replace(str, "'", "''") & aQuote
End Function

標準モジュールでの使い方と、100万行の実行結果

上で紹介したVBAの標準モジュールでの使い方です。
Sub main()
  Dim sTime As Double: sTime = Timer
  
  Dim sDb As String
  sDb = "C:\SQLite3\sample.db"
  Dim rng As Range
  Set rng = ActiveSheet.Range("A1").CurrentRegion
  
  Call BulkInsertSbAry(sDb, "t_sales", rng, 10000)
  
  Debug.Print Timer - sTime
End Sub

このVBAを、10万件で実行した結果は、
約2.6秒
どうでしょう、これなら問題ないでしょう。

ということで100万件に挑戦しました。
約25秒

これなら、もう十分な速度ではないでしょうか。
SQL発行を止めて計測すると15.5秒くらいなので、
これ以上無理に速度アップさせても何秒も短縮されず効果は少ないでしょう。

100万件×10列で25秒

バルクインサートの最後に

2回に渡ってVBAからデータ追加するINSERTを解説しました。
100万件×10列で25秒という結果も出ましたので、これで十分でしょう。
といいますか、エクセルでは行数をこれ以上増やせませんし、
これ以上列数増やしたらファイルが大きくなりすぎて、エクセルでは扱うのが厳しくなってしまいます。
もし、行数は数万件で、列数が数千件というような表の場合は、
Function getValues
この中をStringbuilderで改修すると速くなるはずです。
文字列長が短かったり結合回数が少ない場合、
Stringbuilderクラスを使うとかえって時間がかかってしまいますので、今回は列処理は通常の&結合のままにしてあります。

いよいよ次回から、SQLの核心、SELECT文に入っていきます。



同じテーマ「SQL入門」の記事

テーブルの作成/削除(CREATE TABLE,DROP TABLE)
テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成
データの挿入(INSERT)と全削除
データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)
SQL関数と演算子
データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成
テーブルを結合して取得(INNER JOIN,OUTER JOIN)
複数のSELECT結果を統合(UNION,UNION ALL)


新着記事NEW ・・・新着記事一覧を見る

他のテーブルを基にデータを追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)
SQL関数と演算子|SQL入門(12月1日)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)|SQL入門(11月30日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • データの挿入:バルクインサート

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
    掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ