« 2015年2月 | トップページ | 2015年4月 »

2015年3月

2015年3月31日 (火)

VB MySQLのデータをCSVファイルに出力するツール

MySQLで複数のデータベース,テーブルを作ってデータを自動入力させているのですが,データを入力するソフトを制作,デバッグしている際,データベースサーバーにちゃんとデータが入っているかちょっと調べたいと思っても,ちょうど良いツールがなかなか無かったりします(探し方が悪い?)。
MySQL for Excel」と言うアドインツールをオラクルが提供しており,このツールが目的に合うのですが,都度,Excelを起動,データベース選択など煩わしい面もあります。

仕方無い,と言うことでVisualBasicでツールを自作し活用しています。

MySQL データ出力ツール

せっかくなのでこのツールのソースコードを公開します。公開することで手抜きしているコードを直すとか少しは見やすくするとか私に取ってもメリットがあると考え公開しています。

「SqlDataOut.zip」をダウンロード

ダウンロードし活用して頂く場合
ダウンロードしたらすぐにウイルスチェックして下さい。
実行ファイルは含まれていませんのでコンパイルする必要があります。
必然的に「VisualStudio」がインストールされている必要があります。
予め「MySQL Connector/Net」をインストールして下さい。
参照設定で「Mysql.Data」を選択して下さい。
CSVファイルは
数値以外はダブルコーテーション「"」で囲んでいます。
数値にカンマ「,」が含まれている場合,カンマが削除されます。
バグがあるかも知れません。
ツール使用でのいかなる損害も私は責任を負いません。

ツール制作にあたり,ちょっとした裏話があります。
当初,このツールで開始行を指定せずデータ取得すると,何故か必ず1レコード目のデータが取得出来ませんでした。ネットで散々調べても事例無し。もうお手上げ,原因不明,仕方ない,
昇順,降順で2回検索かけて1行足すか…
それもとネットのQ & Aにすがるか…
と思いつつ,そう言えばSQL文を考えていた際に参考にした書籍があった。ここにヒントは無いか? と見直しました。

【改訂第3版】SQLポケットリファレンス


この書籍のLIMIT旬(指定された行だけ取得)のところに下記記載があります。
「使用例 全体の5行目から3行だけを取得します。」
「SELECT * FROM for LIMIT 3 OFFSET 5  PostgreSQL MySQL」
「SELECT * FROM for 5, 3              MySQL」

この記述に従い,似たよう感じでSQL文を組みました。
「" limit " & startNo & "," & rowNo & ";"」

上記説明からしても,また普通に考えても1行目からの場合「startNo=1」となり,その前提でプログラムを書いていました。
で,試しに「startNo=0」としてみたところ,見事に1レコード目からデータを取得出来ました…
なんともトホホな話です。これだけで数日間散々悩みました。
プログラム的には,開始行の入力は1行目(1レコード目)=1を前提とし,内部で-1してあります。

| | コメント (0) | トラックバック (0)

2015年3月19日 (木)

VB たかがソート,されどSort

No,Dataの2つの配列を連動してDataをキーに降順でソートしたい
のですが,あれこれはまっていました。

下記のようにソートしたい。
ソート前
No Data
1 0.154
2 0.683
3 0.995
4 0.482

ソート後
No Data
3 0.995
2 0.683
4 0.482
1 0.154

VisualBasic6.0やVBAではこういった場合のソート用関数が無いので地味にループして比較でソートするオーソドックスな方法となります。
しかし最新の.NETの時代,ネット検索した感じでは,ソート1つ取ってもなにやら様々な方法があるらしい。主にコレクションのクラスを使ってソートするようですが私の前回のブログ記事(VB ArrayList,LIST(Of T),配列 比較)で掲載した通り私自身コレクションが今1つ分かっていなくてソートとなるとさらに分からない。
でも,出来ることならカッコ良く,しかも高速にソートしたい。
と言うことで無い頭で考えるのは止めて,ひたすらネット検索した結果,いくつかのソート方法を試すことが出来ました。

結果
※シリアルNoと乱数(小数点型)のデータ1万行をソートした時間

項目 時間[s]  コメント
①単純なループでのソート 0.680 
②ArrayListを介しArray.Sort 0.042   
③SortedListでソート その1 0.055 
④SortedListでソート その2 0.076      降順を別ソート
⑤Dictionaryでソート 0.209   昇順のみのソート
⑥単純なArray.Sort 0.005   降順を別ソート

丸付き数字はネット検索で見つけた順番ですが,その順番で試していきました。
オーソドックスな方法が一番軽いイメージを持っているため①の単純ソートが一番遅いのが意外でした。
⑤のDictionaryでのソートは降順にソートする方法が分からず昇順のみの結果です。そのため若干時間が足されます。
実は⑤までやって終わる予定だったのですが最後のひと押しと言うことでさらに検索したところ⑥の単純なArray.Sortを見つけ,これが非常に高速で当たりでした。

結論
単純なArray.Sortでソートする。

もちろん,それぞれの方法にメリット,デメリットがあります。例えばSortedListはどんどんデータを足していくことが出来る上,その都度ソートされますので,その意味では代替の方法がありません(出来たとしても多分遅くなる)。また,要素が文字列だったりすると結果が変わってくるかも知れません。
それから,OrderByなど理解不足でまだ試していない方法がいくつかあります。


それにしても,この情報の海のなかでピンポイントで望む情報を得るのは難しいですね。何事も最後のひと押し,粘りが大切なことを実感しました。


検証に使用したVisualBasicのソースコード
-----------------------------------------------------------
Public Class Form1

'昇順ソート用
Shared Function CreateReversed(Of TKey, TValue)(ByVal source As SortedList(Of TKey, TValue)) As SortedList(Of TKey, TValue)
Return New SortedList(Of TKey, TValue)(source, New ReverseComparer(Of TKey)(source.Comparer))
End Function

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim sw As New System.Diagnostics.Stopwatch()
Dim loop1 As Integer
Dim loop2 As Integer

'元データを準備,シリアルNoと乱数のデータ1万行
Dim no(10000) As Integer
Dim data(10000) As Double
Dim r As New System.Random(1000)
For loop1 = 0 To 10000
no(loop1) = loop1
data(loop1) = r.NextDouble()
Next loop1

'①単純なループでのソート,降順
sw.Start()
Dim swapData As Double
Dim swapNo As Integer
Dim no1(10000) As Integer
Dim data1(10000) As Double
For loop1 = 0 To 10000
no1(loop1) = no(loop1)
data1(loop1) = data(loop1)
Next loop1
For loop1 = 0 To 10000
For loop2 = 10000 To loop1 Step -1
If data1(loop1) < data1(loop2) Then
swapNo = no1(loop1)
no1(loop1) = no1(loop2)
no1(loop2) = swapNo
swapData = data1(loop1)
data1(loop1) = data1(loop2)
data1(loop2) = swapData
End If
Next loop2
Next loop1
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()


'②ArrayListを介しArray.Sortでソート,降順
sw.Start()
Dim Mycompare = New myReverser
Dim no2 As ArrayList = New ArrayList
Dim data2 As ArrayList = New ArrayList
For loop1 = 0 To 10000
no2.Add(no(loop1))
data2.Add(data(loop1))
Next loop1
Dim ar(1)() As Object
ar(0) = no2.ToArray
ar(1) = data2.ToArray
Array.Sort(ar(1), ar(0), Mycompare)
Array.Sort(ar(1), ar(1), Mycompare)
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()


'③SortedListでソート その1,降順
sw.Start()
Dim data3 = New SortedList(New myReverserClass())
For loop1 = 0 To 10000
data3.Add(data(loop1), no(loop1))
Next loop1
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()


'④SortedListでソート その2,降順
sw.Start()
Dim data4A As New SortedList(Of Double, Integer)()
For loop1 = 0 To 10000
data4A.Add(data(loop1), no(loop1))
Next loop1
Dim data4B As New List(Of KeyValuePair(Of Double, Integer))(CreateReversed(data4A))
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()


'⑤Dictionaryでソート,昇順(降順の方法分からず)
sw.Start()
Dim data5A As New Dictionary(Of Double, Integer)()
For loop1 = 0 To 10000
data5A.Add(data(loop1), no(loop1))
Next loop1
Dim data5B As New List(Of KeyValuePair(Of Double, Integer))(data5A)
data5B.Sort(Function(x, y) String.Compare(x.Key, y.Key))
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()


'⑥単純なArray.Sort,降順
sw.Start()
Dim no6(10000) As Integer
Dim data6(10000) As Double
For loop1 = 0 To 10000
no6(loop1) = no(loop1)
data6(loop1) = data(loop1)
Next loop1
Array.Sort(data6, no6)
Array.Reverse(no6)
Array.Reverse(data6)
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

Application.Exit()

End Sub

End Class

'昇順ソート用
Class ReverseComparer(Of T)
Implements IComparer(Of T)
Private comparer As IComparer(Of T)
Public Sub New(ByVal comparer As IComparer(Of T))
Me.comparer = comparer
End Sub
Public Function Compare(ByVal x As T, ByVal y As T) As Integer Implements IComparer(Of T).Compare
Return comparer.Compare(y, x)
End Function
End Class

'昇順ソート用
Public Class myReverser
Implements IComparer
Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
Return New CaseInsensitiveComparer().Compare(y, x)
End Function
End Class

'昇順ソート用
Public Class myReverserClass : Implements IComparer
Private Function Compare(ByVal x As Object, ByVal y As Object) _
As Integer Implements IComparer.Compare
Return ((New CaseInsensitiveComparer()).Compare(y, x))
End Function
End Class

| | コメント (0) | トラックバック (0)

2015年3月17日 (火)

VB ArrayList,LIST(Of T),配列 比較

VisualBasic6.0やVBAからVB.NETに移行してきた方はたいていの場合「配列使い」ですよね。しかしVB.NETだと,もはや配列は古い概念であって今は「ArrayList」なんだ,とか,いやいやArrayListも昔の話で今は「ジェネリック(List(Of T))」なんだよ,とかネットで散見されます。
このArrayList,List(Of T)が最初につまずくポイントの1つでなかなか身につきません。

ところで,CSVファイルを読み込むサンプルコードを改めて探していたところ,いつもお世話になっているサイトにいつものように載っていました。
CSV形式のファイルをDataTableや配列等として取得する

で,このサンプルコードの出力がArrayListになっています。
そのコードをあまり気にせず流用させて頂いていたのですが,CSVデータを計算させる自作クラスが元々,2次元配列前提だったためArrayList用に全部コードを直すか,またはデータをArrayListから2次元配列に変換するか,のどちらかの対応が必要になりました。
しかし,そもそもArrayListってもう古いんじゃなかったっけ?
ArrayListはオブジェクトとしてデータを扱うのでキャストがかかり遅くなるって書いてあるし。
となると,直すにしてもList(Of T)だろうし,でも,もしかして配列の方が高速なのでは?
と頭の中が収拾つかなくなったため,実験して方向性を決めることにしました。

まず10万行,5列,カンマ区切りのCSVファイルを用意し,ArrayList,List(Of T),ジャグ配列のそれぞれでファイルを読み込む時間を計測,さらに読み込んだArrayList,List(Of T),ジャグ配列,2次元配列(2次元配列は複製)のデータを変数に代入する時間をそれぞれ計測し比較してみました。

結果  (時間は秒)

項目  CSV読み込み  変数に代入  ArrayList比
ArrayList  1.883  1.4262  
List(Of T)  1.907  0.0066  217倍
ジャグ配列  1.858  0.0037  388倍
2次元配列  -  0.0057  248倍

CSV読み込みはArrayList,List(Of T),ジャグ配列とも同じプログラムコードでかつ3.3MByteもあるファイルを読み込んでいるためか差がないように見えます。対して変数への代入はArrayListだけがすごく遅いことが分かりました。

結論
1)ArrayListは使用しない。
2)検索やソート無しの単純な使用,かつ速度最優先ならジャグ配列。
3)上記2項以外は用途に応じて普通の配列かList(Of T)。

ArrayListは要素がObjectのために遅いのですが,逆に何でもありのObjectだからこそ利点になることもあるようです。
なお,上記計測は変数への代入時間ですが,2次元配列に代入した場合も計測しておりList(Of T),ジャグ配列,2次元配列とも倍程度の時間が掛かっていました。
それからDataTableと言うのもありますが,それも遅いですので用途次第での活用となります(自分でも検証済み)。
意外と遅い DataTable 、なので List  を使うと 5 倍早くなる

List(Of T)の最大の利点は要素数を気にしなくて良いことですが,改めて自分のプログラムコードを眺めると配列宣言で要素数を決め打ちしたり,結構ReDimしていたりで後々のメンテがいま一つ,かつロスっていますのでList(Of T)の活用は非常に有効です。


さて,食わず嫌いを止めて腕まくりしますか…


検証に使用したVisualBasicのソースコード
-----------------------------------------------------------
Imports System.Collections
Imports System.Collections.Generic

Public Class  Form1

Private Sub Button1_Click(sender As Object, e As EventArgs)_
Handles Button1.Click

Dim sw As New System.Diagnostics.Stopwatch()
Dim  csvFileName As String = "D:\Work\test.csv"
'3月16日,54315,3315,131315,10.432  のようなデータを10万行
Dim csvData1 As New ArrayList() 'ArrayList
Dim csvData2 As New List(Of String()) 'List(Of T)
Dim csvData3()() As String 'ジャグ配列
Dim loop1 As Integer
Dim loop2 As Integer
Dim dummyStr As String = ""

'ArrayListでCSV読み込み
sw.Start()
csvData1  = csvLoad_ArrayList(csvFileName)
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

'List(Of T)でCSV読み込み
sw.Start()
csvData2 =  csvLoad_List(csvFileName)
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

'ジャグ配列でCSV読み込み
sw.Start()
csvData3 =  csvLoad_Array(csvFileName)
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

'ArrayListから変数に代入
sw.Start()
For loop1 = 0 To  csvData1.Count - 1
For loop2 = 0 To csvData1(loop1).length - 1
dummyStr =  csvData1(loop1)(loop2)
Next loop2
Next loop1
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

'List(Of T)から変数に代入
sw.Start()
For loop1 = 0 To csvData2.Count - 1
For loop2 = 0 To  csvData2(loop1).Length - 1
dummyStr = csvData2(loop1)(loop2)
Next loop2
Next loop1
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

'ジャグ配列から変数に代入
sw.Start()
For loop1 = 0 To csvData3.GetLength(0) - 1
For  loop2 = 0 To csvData3(loop1).Length - 1
dummyStr = csvData3(loop1)(loop2)
Next loop2
Next loop1
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

'ジャグ配列から2次元配列を複製
Dim row As Integer = csvData3.GetLength(0)  - 1
Dim col As Integer = csvData3(0).Length - 1
Dim csvData4(row, col) As  String
For loop1 = 0 To csvData3.GetLength(0) - 1
For loop2 = 0 To  csvData3(loop1).Length - 1
csvData4(loop1, loop2) = csvData3(loop1)(loop2)
Next loop2
Next loop1
'2次元配列から変数に代入
sw.Start()
For loop1 = 0 To  csvData4.GetLength(0) - 1
For loop2 = 0 To csvData4.GetLength(1) - 1
dummyStr = csvData4(loop1, loop2)
Next loop2
Next loop1
sw.Stop()
Console.WriteLine(sw.Elapsed)
sw.Reset()

Application.Exit()

End  Sub

Private Function csvLoad_ArrayList(csvFileName As String)

'ArrayListでCSVファイル読み込み
Dim csvRecords As New ArrayList()
Dim tfp As New  FileIO.TextFieldParser(csvFileName, _
System.Text.Encoding.GetEncoding(932))
tfp.TextFieldType = FileIO.FieldType.Delimited
tfp.Delimiters = New String()  {","}
tfp.HasFieldsEnclosedInQuotes = True
tfp.TrimWhiteSpace = True

While Not tfp.EndOfData
Dim fields As String() = tfp.ReadFields()
csvRecords.Add(fields)
End While

tfp.Close()

Return csvRecords

End Function

Private Function csvLoad_List(csvFileName As String)

'List(Of T)でCSVファイル読み込み
Dim csvRecords As New List(Of String())
Dim  tfp As New FileIO.TextFieldParser(csvFileName, _
System.Text.Encoding.GetEncoding(932))
tfp.TextFieldType =  FileIO.FieldType.Delimited
tfp.Delimiters = New String() {","}
tfp.HasFieldsEnclosedInQuotes = True
tfp.TrimWhiteSpace = True

While  Not tfp.EndOfData
Dim fields As String() = tfp.ReadFields()
csvRecords.Add(fields)
End While

tfp.Close()

Return csvRecords

End Function

Private Function csvLoad_Array(csvFileName As String)

'List(Of T)でCSVファイルを読み込みジャグ配列に変換
Dim csvRecords()() As String
Dim  dummyRecords As New List(Of String())
Dim tfp As New  FileIO.TextFieldParser(csvFileName, _
System.Text.Encoding.GetEncoding(932))
tfp.TextFieldType = FileIO.FieldType.Delimited
tfp.Delimiters = New String()  {","}
tfp.HasFieldsEnclosedInQuotes = True
tfp.TrimWhiteSpace = True

While Not tfp.EndOfData
Dim fields As String() = tfp.ReadFields()
dummyRecords.Add(fields)
End While

tfp.Close()

csvRecords =  dummyRecords.ToArray
Return csvRecords

End Function

End Class

| | コメント (0) | トラックバック (0)

2015年3月10日 (火)

VB 文字列連結 高速化

私の前回の記事「MySQL Insertが遅い! ので実験してみた」でマルチプルインサートを使用することでInsert文が高速になるが,代わりにSQL作成時間が長くなるため両者のトレードオフになると結論付けました。

その後,例によって私が知らないだけでVisualBasicでの文字列連結も高速化出来るのでは? と思いネット検索したところ,すぐに見つかりました。
文字列処理を高速に行う: .NET Tips: C#, VB.NET
いつもお世話になっているサイトです。結論として「StringBuilderクラス」を使うと見違えるほど早くなるとのこと。特にループで文字列を連結させる場合は効果が大きい,と。

そこで早速,前回のSQL文作成用プログラムコードを流用して実験してみました。

結果
※10万行のSQL文を文字列連結回数で割った回数分作成した合計時間。

文字列連結回数 String[秒] StringBuilder[秒]
1 0.998 1.149
5 0.526 0.458
10 0.568 0.311
50 1.525 0.244
100 2.679 0.263
500 10.425 0.237
1000 25.605 0.281
10000 271.244 0.221

もはや比較するレベルでは無いですね。StringBuilderの方が圧倒的に早く,10回ループあたりから差がつきだし,ループ回数が増えるほど高速になります。
SQLのInsert実行は「StringBuilderクラス」+「マルチプルインサート」が最強であることが分かりました。この結果から目一杯SQL文を連結して一気に処理するのが一番早いことになります。

但し,注意点があります。MySQLサーバーにデータを送る際のサイズには制限があります。
MySQLの設定ファイル「my.ini」に「max_allowed_packet=4M」と記載されておりデフォルトでは最大4MByteです。最大で16MBまで設定出来るようですが,常にこの設定サイズを意識しながら可能な文字列連結回数を見積もる必要があります。


うーむ,それにしても自分の無知が怖い…


検証に使用したVisualBasicのソースコード
-----------------------------------------------------------
Imports System.Text

Public Class Form1

Private Sub Button1_Click(sender As Object, _
e As EventArgs) Handles Button1.Click

Dim swMake As New System.Diagnostics.Stopwatch()
Dim sqlStr As String = Nothing
Dim repeat1 As Integer
Dim repeat2 As Integer
Dim strClass As String = Nothing
Dim mes As String
Dim loop1 As Integer
Dim loop2 As Integer
Dim loop3 As Integer

For loop1 = 1 To 9
If loop1 = 1 Then repeat1 = 1
If loop1 = 2 Then repeat1 = 5
If loop1 = 3 Then repeat1 = 10
If loop1 = 4 Then repeat1 = 50
If loop1 = 5 Then repeat1 = 100
If loop1 = 6 Then repeat1 = 500
If loop1 = 7 Then repeat1 = 1000
If loop1 = 8 Then repeat1 = 5000
If loop1 = 9 Then repeat1 = 10000
repeat2 = 100000 / repeat1
For loop2 = 1 To 2
If loop2 = 1 Then strClass = "String "
If loop2 = 2 Then strClass = "StringBuilder "
For loop3 = 1 To repeat2
swMake.Start()
If loop2 = 1 Then sqlStr = makeSqlStr1(repeat1)
If loop2 = 2 Then sqlStr = makeSqlStr2(repeat1)
swMake.Stop()
Next loop3
mes = strClass & "文字列作成 " & repeat1 & "×" & repeat2 & " "
Console.WriteLine(mes & swMake.Elapsed.ToString)
swMake.Reset()
Next loop2
Next loop1

Application.Exit()

End Sub

'Stringクラスでの文字列結合
Private Function makeSqlStr1(repeat As Integer) As String

Dim workStr As String
Dim dtToday As DateTime = DateTime.Today
Dim loop1 As Integer

workStr = "INSERT INTO test1 "
workStr &= "(date,dataA,dataB,dataC,dataD) "
workStr &= "VALUES "
For loop1 = 1 To repeat
workStr &= "('" & dtToday.ToString & "',"
workStr &= "'テストテストテスト',"
workStr &= loop1.ToString & ","
workStr &= "1234567890,"
workStr &= "1234567.89"
workStr &= "),"
Next
workStr = workStr.TrimEnd(","c)
workStr &= ";"

Return workStr

End Function

'StringBuilderクラスでの文字列結合
Private Function makeSqlStr2(repeat As Integer) As String

Dim workStr As String
Dim dtToday As DateTime = DateTime.Today
Dim sb1 As New StringBuilder()
Dim loop1 As Integer

'StringBuilderのバッファーサイズを決めるために
'ダミーで文字列を作成
sb1.Append("('")
sb1.Append(dtToday.ToString)
sb1.Append("',")
sb1.Append("'テストテストテスト',")
sb1.Append(loop1.ToString)
sb1.Append(",")
sb1.Append("1234567890,")
sb1.Append("1234567.89")
sb1.Append("),")

'バッファーサイズを指定,余裕を見て3倍にしてある
Dim sb2 As New StringBuilder(sb1.Length * repeat * 3)

sb2.Append("INSERT INTO test1 ")
sb2.Append("(date,dataA,dataB,dataC,dataD) ")
sb2.Append("VALUES ")
For loop1 = 1 To repeat
sb2.Append("('")
sb2.Append(dtToday.ToString)
sb2.Append("',")
sb2.Append("'テストテストテスト',")
sb2.Append(loop1.ToString)
sb2.Append(",")
sb2.Append("1234567890,")
sb2.Append("1234567.89")
sb2.Append("),")
Next
workStr = sb2.ToString
workStr = workStr.TrimEnd(","c)
workStr &= ";"

Return workStr

End Function

End Class

| | コメント (0) | トラックバック (0)

2015年3月 6日 (金)

MySQL Insertが遅い! ので実験してみた

独学でMySQLを使い始めて2年くらいになりますが,最近,CSVファイルから大量のデータを読んでデータベースに書き込みさせる機会があり,その際にとてつもなく時間が掛かることが判明しました。丸3日間くらい掛かってようやく書き込みが終わる事態に…
いくらなんでも遅すぎると言うことでネット検索してみたら同様の案件が多数あり。結論として「マルチプルインサート」を使うと見違えるほど早くなるとのこと。

通常のSQLインサート文(シングルインサート)
INSERT INTO test1 (date,dataA,dataB,dataC,dataD) VALUES
('2015/3/1','テスト',100,1234567890,1234567.89);

マルチプルインサートのSQL文
INSERT INTO test1 (date,dataA,dataB,dataC,dataD) VALUES
('2015/3/1','テスト1',101,1234567891,1234567.00),
('2015/3/2','テスト2',102,1234567892,2234567.11),
('2015/3/3','テスト3',103,1234567893,3234567.22),
('2015/3/4','テスト4',104,1234567894,4234567.33),
('2015/3/5','テスト5',105,1234567895,5234567.44),
('2015/3/6','テスト6',106,1234567896,6234567.55);
複数行のデータをつなげて記述。

実際に実験した結果報告もあり効果のほどは疑いようが無いのですが疑問が1つ湧きました。

マルチプルインサートで早くなるのは分かったが,逆にSQL文の文字列作成で時間が掛かって相殺されるのでは?

と言うことで早速実験してみました。

VisualBasic2013
MySQL Server 5.6
MySQL Connector Net 6.9.5
を使って10万行のデータを書き込むプログラムコードを作成。

使用テーブル ※2つ目のテーブルはインデックス付き
CREATE TABLE test1 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
date DATETIME,
dataA VARCHAR(30),
dataB INT(8),
dataC BIGINT(12),
dataD DECIMAL(10,3)
);
CREATE TABLE test2 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
date DATETIME,
dataA VARCHAR(30),
dataB INT(8),
dataC BIGINT(12),
dataD DECIMAL(10,3),
index(date),
index(dataB)
);

1行のSQL文を10万回実行する方法から最大で1万行分のデータを一まとめにして10回のSQL文で実行する方法まで9段階を試し,それぞれでSQL文作成に掛かる時間,SQL実行に掛かる時間を計測し集計しました。
1行×10万回
5行×2万回
10行×1万回
50行×2000回
100行×1000回
500行×200回
1000行×100回
5000行×20回
1万行×10回

結果
10万行のSQL実行時間(単位:秒) ※インデックス無しテーブルの場合

SQL行結合 実行回数 SQL文作成 SQL実行 トータル
1 100000 1.0 51.4 52.4
5 20000 0.5 12.9 13.4
10 10000 0.6 8.2 8.8
50 2000 1.6 5.8 7.4
100 1000 3.0 5.2 8.2
500 200 11.4 6.9 18.3
1000 100 27.7 6.9 34.6
5000 20 151.5 3.0 154.5
10000 10 282.6 3.3 285.9

SQL実行時間(10万行)

結論として
・SQL実行時間差は最大で17倍。マルチプルインサートの効果あり。
・但し1000回実行あたりから頭打ち。
・SQL文作成時間は行数が増えるほど悪化。
・よって両者のトレードオフになる。
 今回の場合は100行をまとめて1000回実行するラインがベストだった。
 その場合,シングルインサート比の時間差で約6倍早い。

なお,テーブルにインデックスがある場合と無い場合の時間差も調べましたが大差が無かったため掲載を割愛しました(インデックスありの方が遅いはず?)。インデックスの付け方が悪かったかも知れないので後日再度検証予定です。


うーむ,それにしても,これが会社業務なら「知らなかった」では済まされませんね。他に似たようなロスが沢山転がっていそうです。

さて,どうしたものか…


検証に使用したVisualBasicのソースコード
-----------------------------------------------------------
Imports MySql.Data.MySqlClient

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) _
Handles Button1.Click

Dim swMake As New System.Diagnostics.Stopwatch()
Dim swRun As New System.Diagnostics.Stopwatch()
Dim Connection As New MySqlConnection
Dim Command As MySqlCommand
Dim ConnectStr As String
Dim sqlStr As String
Dim repeat1 As Integer
Dim repeat2 As Integer
Dim index As String = Nothing
Dim mes As String
Dim loop1 As Integer
Dim loop2 As Integer
Dim loop3 As Integer

ConnectStr = "Database=test;"
ConnectStr &= "Data Source=localhost;"
ConnectStr &= "User Id=XXXXXX;"
ConnectStr &= "Password=YYYYYY;"
Connection.ConnectionString = ConnectStr
Connection.Open()
Command = Connection.CreateCommand

For loop1 = 1 To 9
If loop1 = 1 Then repeat1 = 1
If loop1 = 2 Then repeat1 = 5
If loop1 = 3 Then repeat1 = 10
If loop1 = 4 Then repeat1 = 50
If loop1 = 5 Then repeat1 = 100
If loop1 = 6 Then repeat1 = 500
If loop1 = 7 Then repeat1 = 1000
If loop1 = 8 Then repeat1 = 5000
If loop1 = 9 Then repeat1 = 10000
repeat2 = 100000 / repeat1
For loop2 = 1 To 2
If loop2 = 1 Then index = "インデックス無し "
If loop2 = 2 Then index = "インデックスあり "
For loop3 = 1 To repeat2
swMake.Start()
sqlStr = makeSqlStr(loop3, repeat1)
swMake.Stop()
swRun.Start()
Command.CommandText = sqlStr
Command.ExecuteNonQuery()
swRun.Stop()
Next loop3
mes = index & "文字列作成 " & repeat1 & "×" & repeat2 & " :"
Console.WriteLine(mes & swMake.Elapsed.ToString)
mes = index & "実行 " & repeat1 & "×" & repeat2 & " :"
Console.WriteLine(mes & swRun.Elapsed.ToString)
swMake.Reset()
swRun.Reset()
Next loop2
Next loop1

Command.Dispose()
Connection.Close()
Connection.Dispose()

Application.Exit()

End Sub

Private Function makeSqlStr(selectTabele As Integer, _
                             repeat As Integer) As String

Dim workStr As String
Dim dtToday As DateTime = DateTime.Today
Dim loop1 As Integer

If selectTabele = 1 Then
workStr = "INSERT INTO test1 "
Else
workStr = "INSERT INTO test2 "
End If
workStr &= "(date,dataA,dataB,dataC,dataD) "
workStr &= "VALUES "
For loop1 = 1 To repeat
workStr &= "('" & dtToday.ToString & "',"
workStr &= "'テストテストテスト',"
workStr &= loop1.ToString & ","
workStr &= "1234567890,"
workStr &= "1234567.89"
workStr &= "),"
Next
workStr = workStr.TrimEnd(","c)
workStr &= ";"

Return workStr

End Function

End Class

続きを読む "MySQL Insertが遅い! ので実験してみた"

| | コメント (0) | トラックバック (0)

« 2015年2月 | トップページ | 2015年4月 »