クライアントでテキストファイル(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使えば?と言われそうですが、これはこれで共有フォルダの
設定とか考えなくていいので全く見込みがないわけじゃないと思うのです。