愚者の経験

「また今度」はほとんどこない

SQL ServerへのCSVファイルinsert高速化

クライアントでテキストファイル(csv)を処理してテーブルに
取り込む操作があったのですがそれが遅いとの事。

普通に「insert into xxxxx values(zzzzzzz,zzzz,zzzz)」系の
文字列にして、ADODB.Commandにセットしていたんですが
良かれと思ってあることをしていたのです…

    Dim InsertSQL As String
    Dim ValuesSQL As String
    Dim Row As String
    Dim rCount As Long
    Dim fNO As Long
    
    fNO = FreeFile
    InsertSQL = "insert into xxx(xxxx,xxx,xx,x)"
    
    Open filePath For Input As #fNO
    Do Until EOF(fNO)
        rCount = rCount + 1
        Line Input #fNO, Row
        
        ValuesSQL = ValuesSQL & ",(" & Row & ")"
        
        If (rCount Mod 1000 = 0) Then
            With New ADODB.Command
                .ActiveConnection = "xxxxxx"
                .CommandText = InsertSQL & " values" & Mid$(ValuesSQL, 2)
                .CommandType = adCmdText
                .CommandTimeout = 0
                .Execute , , adExecuteNoRecords
            End With
            ValuesSQL = ""
        End If
    Loop
    
    Close #fNO

大雑把に書くとこんな感じです。適当に書いてるのであしからず。
SQL Server2008から(確か)可能になった「insert into~~ valuesでの複数行追加
をやっていました。「valus(a1,b1),(a2,b2)」のような書き方で複数行がinsertできる機能ですが
既定で一度に1000行までしかinsertできない制約を除けば一度の実行の手間が省ける!
と思っていたんです。

しかしExecute前後でTimer計測すると1000行入れるのにも4-5秒ほどの時間がかかっていました。
インデックスを除去したりトランザクションで括っても結果は変わらず…「早くならない!これが限界か?」
おもむろに1000行カウントを外して1行ずつExecuteするように変更してみました。
するとなんと5000行を10秒で回りました。めちゃくちゃSpeedup。

さらに

insert into xxx(xxxx,xxx,xx,x) values('a1','b1','c1','d1');
insert into xxx(xxxx,xxx,xx,x) values('a2','b2','c2','d2');
insert into xxx(xxxx,xxx,xx,x) values('a3','b3','c3','d3');
insert into xxx(xxxx,xxx,xx,x) values('a4','b4','c4','d4');
…
insert into xxx(xxxx,xxx,xx,x) values('a1000','b1000','c1000','d1000');

上記のような全体を文字列にして実行回数自体を減らすと更に早くなりました。
これならトランザクションで効果が出そうだと思いましたがやはりあまり変わらず。

いづれにしても複数行insertで遅い時は敢えて1行ずつ実行することも
試してみる価値はあると思います。必ずしも新機能が高速だというわけではなく
当然ケースバイケースだということだと思います。でもちょっと驚き。

ちなみに1行30列ほどだったと思います。
また1行ずつExecuteするなら、「insert into xxx(xxxx,xxx,xx,x) values(?,?,?,?);」
このような形でCommandTextをセットし、PreparedをTrueにして実行すれば
こっちのほうが早いかもしれません。やったことないですが。

    Dim Row As String
    Dim rCount As Long
    Dim fNO As Long
    fNO = FreeFile

    With New ADODB.Command
        .ActiveConnection = "xxxx"
        .CommandText = "insert into xxx(xxxx,xxx,xx,x) values(?,?,?,?);"
        .CommandType = adCmdText
        .CommandTimeout = 0
        .Prepared = True
        ' Parameters.Apprend .CreateParameter*4が必要かも
    
        Open filePath For Input As #fNO
        Do Until EOF(fNO)
            rCount = rCount + 1
            Line Input #fNO, Row
            .Execute , Split(Row, ",", , vbBinaryCompare), adExecuteNoRecords
        Loop
        Close #fNO
    End With

超すっきりしますね(笑)繰り返しますが未検証なのであしからず。
パラメータの配列渡しはSplitじゃダメかも知れません。

そもそもBULK使えば?と言われそうですが、これはこれで共有フォルダの
設定とか考えなくていいので全く見込みがないわけじゃないと思うのです。

SQL ServerへのCSVファイルinsert高速化」への6件のフィードバック

  1. k17 3月 9, 2014 7:08 am

    これ、私もはまったことがあります。
    文字列連結に時間がかかっているのが原因ですね。
    以下、試してみてください。

    1.1回の挿入行数を10~100行位に抑える

    2.1行分のデータを先に作ってから連結する(括弧をつける)
    ×:sql = sql & “,(” & a & “,” & b & “,” & c & “)”
    ○:sql = sql & (“,(” & a & “,” & b & “,” & c & “)”)

    • rsskkr 3月 9, 2014 10:42 pm

      k17様
      コメントありがとうございます。個別に返信させて頂きます。
      この件はあまり記憶が定かで無いのですが、この件はVBAでタイムを計測した時に
      ADODB.CommandオブジェクトのExecuteメソッドに時間がかかっていたような気がします。

      もちろんVBAの文字列連結もタイムを短くできる箇所ではあったのですが
      それ以前にSQLの実行そのものがかなり遅かったという感じだったとおもいます。

      • k17 3月 11, 2014 12:30 am

        こんばんは。改めて調べてみたところ、おっしゃる通りExecuteで時間がかかってますね。
        申し訳ありません。
        どうも Execute に渡すSQLの文字数によって実行時間に差ができるようです。
        私のマシンでは以下のような感じでした。
        1行毎…2845行/秒
        20行毎…11429行/秒
        1000行毎…850行/秒
        環境:AMD A10-6800K、Win8.1-64、Access2013、SQL Server 2012 LocalDB

        検証用コード
        Dim cn As New ADODB.Connection
        Dim t As Single
        Dim i As Long, j As Long, k As Long, id As Long
        Dim sql As String, sql2 As String
        Dim result As String
        Dim batchSize As Long
        cn.Open “Provider=SQLNCLI11;Server=(localdb)\Projects;Database=Database1;Integrated Security=SSPI;DataTypeCompatibility=80”
        For Each batchSizeStr In Split(“1,2,5,10,20,25,50,100,500,1000”, “,”)
        cn.Execute “TRUNCATE TABLE Table1”
        batchSize = CLng(batchSizeStr)
        t = Timer
        For i = 1 To (10000 \ batchSize)
        sql = “”
        For j = 1 To batchSize
        id = id + 1
        sql2 = “”
        For k = 1 To 30
        sql2 = sql2 & “,’1234567890′”
        Next
        sql = sql & (“,(” & id & sql2 & “)”)
        Next
        cn.Execute “INSERT INTO Table1 (ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29,F30) VALUES ” & Mid$(sql, 2)
        Next
        t = Timer – t
        result = result & “BatchSize=” & batchSize & ” Speed=” & Format(10000# / t, “0”) & vbCrLf
        Next
        cn.Close
        MsgBox result

        テーブル:IDはint、F1~F30はvarchar(10)。

      • rsskkr 3月 13, 2014 10:12 am

        k17様、検証ありがとうございます。ご返事が遅れてすみません。
        私も頂いたコードを変更して実行してみました。結果は以下のようになりました。
        BatchSize=1 Speed=106667
        BatchSize=2 Speed=128000
        BatchSize=5 Speed=128000
        BatchSize=10 Speed=128000
        BatchSize=20 Speed=106667
        BatchSize=25 Speed=128000
        BatchSize=50 Speed=91429
        BatchSize=100 Speed=82581
        BatchSize=500 Speed=32000
        BatchSize=1000 Speed=3838

        環境:Core(TM)2 Duo CPU E8400 Win7SP1 Access2010SP2 SQLServer2008R2
        k17さんと比べると化石のような環境ですね笑

        insert into ~~~ values ~~~;
        insert into ~~~ values ~~~;
        insert into ~~~ values ~~~;
        のような形で実行するとまた違った結果かもしれません。時間があったら確認します…
        連続で実行した方が解析が早くなって後のinsertが早くなるんでしょうかね?
        いずれにしても一気に入れるのはよくなさそうですね。
        一応テストコードも載せます。

        Public Function InsertTest()
        On Error Resume Next
        Dim cn As New ADODB.Connection
        Dim t As Single
        Dim i As Long, j As Long, k As Long, id As Long
        Dim sql As String, sql2 As String
        Dim result As String
        Dim batchSize As Long

        Const table As String = “create table #table1 (ID int null,F1@,F2@,F3@,F4@,F5@,F6@,F7@,F8@,F9@,F10@,F11@,F12@,F13@,F14@,F15@,F16@,F17@,F18@,F19@,F20@,F21@,F22@,F23@,F24@,F25@,F26@,F27@,F28@,F29@,F30@)”

        cn.Open “PROVIDER=SQLOLEDB.1;DATA SOURCE=(local);INITIAL CATALOG=TESTDB;Integrated Security=SSPI;”

        For Each batchSizeStr In Split(“1,2,5,10,20,25,50,100,500,1000”, “,”)

        cn.Execute “drop table #table1;”
        cn.Execute Replace(table, “@”, ” varchar(10) null”)

        batchSize = CLng(batchSizeStr)

        t = Timer
        For i = 1 To (10000 \ batchSize)
        sql = “”
        For j = 1 To batchSize
        id = id + 1
        sql2 = “”
        For k = 1 To 30
        sql2 = sql2 & “,’1234567890′”
        Next
        sql = sql & (“,(” & id & sql2 & “)”)
        Next
        cn.Execute “INSERT INTO #table1 (ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29,F30) VALUES ” & Mid$(sql, 2)
        Next

        t = Timer – t

        result = result & “BatchSize=” & batchSize & ” Speed=” & Format(10000# / t, “0”) & vbCrLf

        Next
        cn.Close
        MsgBox result
        End Function

      • k17 3月 16, 2014 12:29 am

        こんばんは。
        INSERTを複数並べるのでも実験してみました。
        あと、通常テーブルと一時テーブル(名前が#で始まるテーブル)の違いも調べました。

        ①通常テーブル、31列、複数VALUES
        ②通常テーブル、31列、複数INSERT
        ③一時テーブル、31列、複数VALUES
        ④一時テーブル、31列、複数INSERT
        ⑤通常テーブル、31列、複数INSERT×複数VALUES
        ⑥通常テーブル、2列、複数INSERT×複数VALUES
        ⑦通常テーブル、2列、複数INSERT

        ①…25個付近が最速で11532行/秒。それを超えると急激に遅くなり、1000個では877行/秒まで落ちる。
        ②③④…2~500個の範囲で、3000~4000行/秒位。個数の影響をあまり受けない。
        ⑤…INSERT10個×VALUES25個付近が最速で12190行/秒。
        ⑥…INSERT4個×VALUES250個付近が最速で104918行/秒。
        ⑦…INSERT1000個付近が最速で6278行/秒。

        結論:
        ・一時テーブルでは複数INSERTも複数VALUESも違いはなくそこそこの速度が出る。
        ・複数INSERTは個数の影響をあまり受けず、そこそこの速度が出る。
        ・複数VALUESは個数によって速度に大きな違いが出る。ある一定数を超えると急激に遅くなる。CPUのL1/L2キャッシュかページサイズの影響を受けているような印象。
        ・列数が少ないとき、複数VALUESは効果的。複数INSERTの10倍以上の速度が出ることもある。
        ・複数INSERTと複数VALUESを組み合わせることで少し速度を向上させられる。

        SQL Server の複数 VALUES は独特の癖があるようですね…。

      • rsskkr 3月 16, 2014 9:01 pm

        k17様、こんばんは。素晴らしい検証ありがとうございます。
        私もループ部分を改変して複数INSERTだけ調べました。

        For i = 1 To (10000 \ batchSize)
        sql = “”
        For j = 1 To batchSize
        id = id + 1
        sql2 = “”
        For k = 1 To 30
        sql2 = sql2 & “,’1234567890′”
        Next
        sql = sql & vbCrLf & (“INSERT INTO #table1 (ID,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29,F30) VALUES (” & id & sql2 & “);”)
        Next
        cn.Execute sql
        Next

        結果は以下のようになりました。
        BatchSize=1 Speed=106667
        BatchSize=2 Speed=128000
        BatchSize=5 Speed=128000
        BatchSize=10 Speed=106667
        BatchSize=20 Speed=106667
        BatchSize=25 Speed=91429
        BatchSize=50 Speed=64000
        BatchSize=100 Speed=49231
        BatchSize=500 Speed=5141
        BatchSize=1000 Speed=1723

        CPUによって最適なINSERTの仕方があるみたいですが難しいですね。
        とりあえず20行ぐらいが大体いい感じになるんですかね。
        VALUESの代わりにSELECTでやってみましたがこれはやはり変化ありませんでした。

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

w

%s と連携中

%d人のブロガーが「いいね」をつけました。