プログラミング

2015年4月15日 (水)

Oracle VBA/VB oo4o,ODBC,ADO 速度比較

会社で大きなシステム変更がありシステム屋さん達が多忙のため何故だか別業務の私に簡単なデータベース読み出しソフトの制作依頼が来ました。

会社で採用されているデータベースは「オラクル」で,その読み出し用プログラムのコードは主にExcel VBAかVisualBasic6.0,データベース接続クライアントに「oo4o」を使用した10年以上前から変わらぬ構造のものです。

今回依頼されたものは,読み出すデータが異なるだけでコードの基本構造は同じで良かったため,当初,以前からあるVBAのコードをちょっと修正してCSVファイルを出力するプログラムを作りました。

しかし,Excelを自動実行するとオブジェクトが残ってメモリーのゴミになるとかの弊害が自身の経験であったため,仕方無いと思いつつVisualBasic2013+ODBCでコードを丸々作り直しました。

これで完成と思って,何気に読み出し時間を比較してみたところVBAよりもVBの方が倍くらい早い感じ。元々VBAが遅いのか,それともoo4oが遅いのかと思いつつVBAのコードに手を付ける気力は無くて取り敢えず任務完了となりました。

しかし,そういえば自宅で使用しているMySQLはVBAはODBC,VBは「ADO」を使用していることを思い出し,ネット検索してみたところ,オラクルも.NET対応のADOがあるらしいと判明。
Oracle Universal Installerを起動しインストールされているソフトを確認したところ「Oracle Data Provider for .NET」がインストールされていました。
オラクルのホームページの解説では「Oracle Data Provider for .NET(ODP.NET)では、Oracleデータベースに対するADO.NETデータ・アクセスの最適化を実現します。」と記載されており期待が持てます。

で,さっそくADOでもプログラムを記述,ついでにVB+oo4oでも記述し,それぞれ掛かった時間を比較してみました。

結果
ネットワーク上のオラクルデータベースから8000行,19列のデータを読みだした平均時間
VBA oo4o 4.8秒
VB  oo4o 9.7秒
VB  ODBC 2.4秒 VBA比2.0倍高速
VB  ADO  1.4秒 VBA比3.4倍高速

なんとVB+ODBCはVBA比で2倍高速であることが分かりました。ADOであればさらに高速です。但し,何故だかVB+oo4oは超遅いです(多分oo4o関連のパラメータ選択が今いち)。
今回は数秒と短いですが,システム部門から提供されているVBAやソフトではデータ量によっては結果が出るまでたまに30分くらい延々と待たされることがあり業務ロスとなっています(しかも10年来この調子)。
これが,やりようによっては1/3の時間で済むかも知れません。

理想はVBまたはC#でコードを全面書き直しなんですが,ユーザーとしてデータベースの読み出しが遅くて困ることは時間比率では大したことがないので,多分これからもシステム屋の工数は割けないですね。他の優先事項が多すぎます。

なお,ADOが最善との結論になったのですが,社内の複数のPCを確認したところ,なんとインストールされているオラクルのクライアントバージョンが9iと11gの2種類があり,9iの方ではADOがインストールされいないらしく動作しませんでした。
そのため,私自身が制作する小物のツールはどのPCでも動きそうなVB+ODBCとなります。

それにしても,自宅パソコンなら管理者,使用者とも自分一人なので融通が効きますが,社内での歴史あるシステムを作り替えるとなると相当なエネルギーが必要なようですね。
データベースを利用するソフトに関しては改善が進まずこれから10年間もまた同じような感じでしょう。もやもやが取れません。

さて,どうしたものか…


VisualBasicのプログラムコードの一部
(検証したデータベースのクライアント ORACLE 11g)

Oracle oo4o 対応のコード
参照設定 com Oracle Inproc Server 5.0 Type Liberty
(.NET Framework 4対応)
----------------------------------------------------------
Dim oraSess As OracleInProcServer.OraSessionClass
Dim oraDb As OracleInProcServer.OraDatabase
Dim Rs As OracleInProcServer.OraDynaset
Dim cnt As String = userId & "/" & password
Dim objectList As New List(Of Object)

oraSess = New OracleInProcServer.OraSessionClass
oraDb = oraSess.OpenDatabase(database, cnt, OracleInProcServer.dbOption.ORADB_DEFAULT)
----------------------------------------------------------


Oracle ODBC Driver 対応のコード
----------------------------------------------------------
Imports System.Data.Odbc

Dim myCon As New OdbcConnection()
Dim myCommand As New OdbcCommand()
Dim myReader As OdbcDataReader

myCon.ConnectionString = _
"DSN=" & dsn & ";" & _
"DATABASE=" & database & ";" & _
"UID=" & userId & ";" & _
"PWD=" & password & ";"

myCon.Open()
----------------------------------------------------------

Oracle Data Provider for .NET 対応のコード
(.NET Framework 2.0対応)
----------------------------------------------------------
Imports System.Data.Common

Dim factory As DbProviderFactory = _
DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
Dim csbuilder As DbConnectionStringBuilder = _
factory.CreateConnectionStringBuilder
csbuilder("Data Source") = database
csbuilder("User ID") = userId
csbuilder("Password") = password

Dim conn As DbConnection = factory.CreateConnection()
conn.ConnectionString = csbuilder.ConnectionString
conn.Open()
----------------------------------------------------------

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

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月20日 (金)

VB WebBrowser HTML取得時間 IEバージョン差比較

半年以上前にVisualBasicでWebBrowserクラスを用いてWebページから情報を取得するプログラムをいくつか制作していました。同じページをInternetExplorer(以降IE)とVBフォーム内のWebBrowser両方で表示させ見比べながら,どこのボタンを押すのかなどを探りつつ作業を進めていくのですが,何故だが複数の特定ページでWebBrowser側の表示や挙動がおかしい。結局,原因が分からないままそのプログラムは後で検証しようと棚上げにしていました。

その後,偶然ネット徘徊の情報でWebBrowserは内部的にはIEが動作しており,しかもバージョンがかなり古い「IE7」であるとのことを知り,合点がいくと納得しました。該当のWebサイトは昨年4月のWindowsXPサポート終了に合わせてIEのサポートがIE8以降に変更されていました。WebBrowserのIEバージョンを変更して近々棚上げプログラムの制作再開を予定しています。

で,1つ疑問が湧きました。「IEのバージョンが新しい方が高速なはずだ!(根拠不明)」。ならばバージョンの違いでHTMLを読み込む速度がどの程度異なるのか是非知りたい,と。

と言うことで早速,前回の「VB HTML取得時間比較 WebClient,WebBrowser,IE」のプログラムコードをほぼそのまま使用し検証してみました。

前回は文字羅列のHTMLで100KByteと10MBteyのファイルを使用しました。半角の英文字(A~Z)を300桁×容量に合う行数としていました。今回は更に桁数が100桁,200桁のものも準備しました。
また,例えば10MBのファイルだと300桁×34997行とかになりますが現実的にそんなHTMLファイルは存在しないでしょうから,ごく普通の,HTMLが10KB程度に写真3枚100KB程度のファイルも準備し,計7種類のファイルを4種の方法でHTML取得し時間を比較してみました。

WebBrowseのIEバージョンは最新の「IE11」とサポート外となりつつある「IE7」を比較しています。

  VisualBasic HTMLファイル取得時間比較
項目 取得回数 平均時間[ms] WB IE7比
①HTML 100KB 半角英文字100桁×1004行
 IE11 22  789   
 WB IE11 22  221  1.1倍 
 WB IE7 28  202   
 WC 22  22   
②HTML 100KB 半角英文字200桁×506行
 IE11 22  717   
 WB IE11 22  199  1.1倍 
 WB IE7 28  184   
 WC 22  22   
③HTML 100KB 半角英文字300桁×338行
 IE11 20  727   
 WB IE11 20  191  1.1倍 
 WB IE7 24  181   
 WC 20  21   
④HTML 10MB 半角英文字100桁×102804行
 IE11 23  6058   
 WB IE11 21  11450  2.0倍 
 WB IE7 28  5644   
 WC 24  70   
⑤HTML 10MB 半角英文字200桁×47393行
 IE11 22  8505   
 WB IE11 21  8082  2.2倍 
 WB IE7 25  3630   
 WC 22  67   
⑥HTML 10MB 半角英文字300桁×34997行
 IE11 21  7504   
 WB IE11 21  7365  2.2倍 
 WB IE7 20  3317   
 WC 21  69   
⑦HTML 14.4KB + 画像3枚計141.3KB
 IE11 24  639   
 WB IE11 24  181  1.0倍 
 WB IE7 21  186   
 WC 24  22   
 WB = WebBrowser, WC = WebClient

結果として,
自分の見当とは逆にWebBrowserでは最新のIE11よりもIE7の方が高速でした。但し,一般的なHTML容量と言える100KB以下ではほとんど差がありませんでした。また,JavaScriptが付加されている場合などは結果が変わるかも知れません。

なお,WebBrowse(IE11)とIE11オブジェクトではHTMLのファイル容量や桁数,行数で結果が逆転しており優劣は判断出来ませんでした。

WebBrowseのIEバージョンの切り替えには下記サイトを参考にさせて頂きました。
C# WebBrowserのレンダリングモード
上記のC#のコードをVBに変換し活用しました。

WebBrowseのIEバージョン切り替え VisualBasicソースコード
※レジストリを操作しますので使用は自己責任でお願いします。
-----------------------------------------------------------
Imports System.Runtime.Serialization

Public Class Form1

Dim FEATURE_BROWSER_EMULATION As String = _
"Software\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION"
Dim FEATURE_DOCUMENT_COMPATIBLE_MODE As String = _
"Software\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_DOCUMENT_COMPATIBLE_MODE"

Dim userAgent As Integer = 11001 'IE11
Dim renderingMode As Integer = 110000 'IE11

Dim exeName As String = "WindowsApplication1.exe" 'デバッグ時は.vshost.exeにしておく
'Dim exeName As String = "WindowsApplication1.vshost.exe"


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

Dim regkey1 As Microsoft.Win32.RegistryKey = _
Microsoft.Win32.Registry.CurrentUser.CreateSubKey(FEATURE_BROWSER_EMULATION)
Dim regkey2 As Microsoft.Win32.RegistryKey = _
Microsoft.Win32.Registry.CurrentUser.CreateSubKey(FEATURE_DOCUMENT_COMPATIBLE_MODE)

'userAgent = 7000 'IE7
'renderingMode = 70000 'IE7

regkey1.SetValue(exeName, userAgent, Microsoft.Win32.RegistryValueKind.DWord)
regkey2.SetValue(exeName, renderingMode, Microsoft.Win32.RegistryValueKind.DWord)

regkey1.Close()
regkey2.Close()

End Sub

End Class

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

2015年2月15日 (日)

VB HTML取得時間比較 WebClient,WebBrowser,IE

いまや企業のみならず
個人においてもビックデータな時代
ではないでしょうか?

その気さえあれば個人でもネットにあふれる情報を収集,分析することで実りあるアウトプットを期待出来ると思います。

例えば,「Yahoo!検索(リアルタイム)」ではTwitterなどの投稿をワード検索出来,時間ごとの件数を表示してくれますが特定ワードの検索結果を一定間隔で収集しデータベースに溜め込み時間帯などの独自の切り口で分析するなどアイディアは沢山あると思います。

その意味で「WebページからHTMLデータを取得する」ことが入り口の作業として重要になってきます。

HTMLのデータ収集はプログラミングで実現出来ますが,例えばVisualBasic(VB)の場合だと,いくつかの方法(プログラムコード)があります。

主に
1)WebClientクラス を使用
2)WebBrowserクラス を使用
3)InternetExplorer(IE)オブジェクト を使用

それぞれ長所短所があります。
WebClient → 簡単で高速,但しページ上のボタンが押せないなど
WebBrowser → ブラウザーの機能を有しほとんどのことが出来る
IEオブジェクト → 面倒で低速,但しVBAなどでポピュラー

VBの場合,WebClientかWebBrowserかどちらかの選択で,ボタンを押す必要がある,セッションを維持する必要などの場合はWebBrowserになります。

その前提で使い分けてプログラミングしている訳ですが,ふと「WebClientが高速」って思ってたけど本当に高速なの?高速だとしてどのくらい高速なの? と疑問が湧いてきます。そう思い始めると気になってしょうがありません。

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

実験前にWebBrowserで注意点があります。
WebBrowserも内部的にIEが動作しているそうですが,標準で「IE7」のバージョンに設定されます。IE7はWindowsXP以前のバージョンですのでWebサイトによっては対応していません。また,バージョンが上がるほど高速になるなどの利点もありますので予め適切なバージョンを強制設定しておく必要があります。今回は下記のサイトを参考に最新の「IE11」に設定してあります。
※多分,IE11そのものがインストールされている必要があります。
WebBrowser コントロールで使われている Internet Explorerを最新のバージョンに変更する
C# WebBrowserのレンダリングモード
WebBrowserコントロールのIEバージョン

実験方法
1)中身が単なる文字羅列の100KByteと10MByteの
 HTMLファイルをローカルフォルダー(例えばDドライブ)
 に用意します。
2)WebClient,WebBrowser,IEオブジェクトのそれぞれで
 HTMLを読み込みその時間を計測します。
 プログラムコードは下記参照。
3)計測値の10回の平均を算出します。

当初,連続してページを読み込みましたが100KByteファイルで確認中に段々時間が早くなっていく現象がありました。計測前にIEの一時ファイルは削除していますがどこかの経路でキャッシュが働いているかも知れません。そのため面倒でしたが1回1回プログラムを終了,起動させながらデータを取得しました。

なお,VisualStudioのデバックモードでプログラムを実行しました。
パソコンはCeleron B840+256GB SSDのノートパソコンです。

計測結果
クラス/obj    サイズ   結果   WC比  WB比
WebClient(WC)  100KB   25ms
WebClient(WC)   10MB   67ms
WebBrowser(WB) 100KB  156ms  6.4倍
WebBrowser(WB)  10MB 1169ms 17.4倍
IE             100KB  423ms 16.9倍 2.7倍
IE             100KB 6895ms  102倍 5.8倍

結果として,WebClientがダントツに高速でした。
ただ,ローカルドライブのファイルをアクセスしており,Webからの場合,TCP/IP通信時間などのゲタが足されて幾分相殺されると思います。
(実際の時間差は少ないかも知れない)

逆にローカルファイルだったことでそれぞれの方法の時間差を正確に把握出来たとも言えますのでWebClientとWebBrowserをきっちり使い分けてプログラミングしていく必要性を改めて認識出来ました。

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

'参照
'Microsoft.msHtml
'Microsoft Internet Controls

Public Class Form1
Private myUrl As String
'ストップウオッチ
Private sw As New System.Diagnostics.Stopwatch()
'IE
Private WithEvents IE As SHDocVw.InternetExplorer

Private Sub Form1_Load(sender As Object, e As EventArgs) _
                                        Handles Me.Load
myUrl = "D:\Work\check_100KB.html"
'myUrl = "D:\Work\check_10MB.html"
End Sub

'IEオブジェクトでページを開く
Private Sub Button1_Click(sender As Object, e As EventArgs) _
                                     Handles Button1.Click
delIeCache()
If Not (IE Is Nothing) Then
IE.Quit()
IE = Nothing
End If
sw.Start()
IE = New SHDocVw.InternetExplorer
IE.Visible = False
sw.Start()
IE.Navigate2(myUrl)
End Sub

'IEオブジェクトのドキュメントコンパレート
Private Sub IE_DocComplete(pDisp As Object, ByRef URL As Object) _
              Handles IE.DocumentComplete
If Not TypeName(pDisp) = "IWebBrowser2" Or _
        URL.ToString myUrl Then Return
Invoke(New SHDocVw.DWebBrowserEvents2_DocumentCompleteEventHandler(AddressOf myDocComplete), pDisp, URL)
End Sub

'IEオブジェクトのドキュメントコンパレート
Private Sub myDocComplete(ByVal pDisp As Object, ByRef URL As Object)
Dim ieDoc As mshtml.HTMLDocument = _
CType(IE.Document, mshtml.HTMLDocument)
Dim html As String = ieDoc.body.innerHTML
MRComObject(ieDoc)
sw.Stop()
System.Diagnostics.Debug.WriteLine("IE " & sw.ElapsedMilliseconds)
sw.Reset()
End Sub

'COMオブジェクトの開放
Public Shared Sub MRComObject(Of T As Class) _
(ByRef objCom As T, Optional ByVal force As Boolean = False)
If objCom Is Nothing Then
Return
End If
Try
If System.Runtime.InteropServices.Marshal.IsComObject(objCom) Then
If force Then
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom)
Else
Dim count As Integer = _
System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom)
If count 0 Then
Debug.Print(count.ToString())
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom)
End If
End If
End If
Finally
objCom = Nothing
End Try
End Sub

'WebBrowserでページを開く
Private Sub Button2_Click(sender As Object, e As EventArgs) _
                                     Handles Button2.Click
delIeCache()
sw.Start()
WebBrowser1.ScriptErrorsSuppressed = True
WebBrowser1.Visible = False
WebBrowser1.Navigate(myUrl)
End Sub

'WebBrowserのドキュメントコンパレート
Private Sub WebBrowser1_DocumentCompleted(ByVal sender As System.Object,
ByVal e As System.Windows.Forms.WebBrowserDocumentCompletedEventArgs) _
                         Handles WebBrowser1.DocumentCompleted
If Not TypeName(sender) = "WebBrowser" Then Exit Sub
If e.Url DirectCast(sender, WebBrowser).Url Then Exit Sub
Dim html As String = WebBrowser1.Document.Body.InnerHtml
sw.Stop()
System.Diagnostics.Debug.WriteLine("WB " & sw.ElapsedMilliseconds)
sw.Reset()
End Sub

'WebClient.DownloadStringでHTML取得
Private Sub Button3_Click(sender As Object, e As EventArgs) _
                                        Handles Button3.Click
delIeCache()
sw.Start()
Dim wc As New System.Net.WebClient()
wc.Encoding = System.Text.Encoding.UTF8
Dim HTML As String = wc.DownloadString(myUrl)
wc.Dispose()
sw.Stop()
System.Diagnostics.Debug.WriteLine("WC " & sw.ElapsedMilliseconds)
sw.Reset()
End Sub

'IEの一時ファイル削除 本当に削除されているかは未確認…
Sub delIeCache()
Dim D As String
D = System.Environment.GetFolderPath(Environment.SpecialFolder.InternetCache)
Dim F() As String
Try
F = Directory.GetFiles(D, "*", SearchOption.AllDirectories)
For I As Integer = 0 To (F.Length - 1)
File.Delete(F(I))
Next
Catch ex As Exception
End Try
End Sub

'フォームを閉じる時の処理
Private Sub Form1_FormClosed(sender As Object, _
                           e As FormClosedEventArgs) _
                                Handles Me.FormClosed
If Not (IE Is Nothing) Then
IE.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(IE)
IE = Nothing
End If
End Sub

Private Sub Button4_Click(sender As Object, e As EventArgs) _
                                          Handles Button4.Click
Application.Exit()
End Sub
End Class

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

2015年1月31日 (土)

VisualBasic VBでExcelのシート取り込み,読み込み

VisualBasicでExcelシートのデータを取り込みたいのですがネットを探しても自分が欲しいピンポイントのクラス,コードが見つからなかったため自作しました。自分用のマニアックな機能になっていますが参考になればと思いアップしました。

自作のクラスは下記のような機能があります。
シート番号またはシート名を指定して1つのシートから下記の3種の方法でデータを取り込み出来ます。
・全列,全行を取り込み
・列番号を指定して全行を取り込み
・列名を指定して全行を取り込み ※列名は1行目であること

バグがあるかも知れませんし思い通りの取り込みが出来ないかも知れません。自己責任でお願いします。

自作にあたり
VB.NETからExcelファイルのデータを読み込むには
の記事を参考にさせて頂きました。開いたExcelオブジェクトをチリも残さず「開放」させる手順を詳しく解説して頂いており大変参考になりました。

下手の横好きでプログラミングしており我流が染み付いています。バグ,見苦しい,助長,意味不明なコードとかあると思いますので遠慮せず指摘して頂ければ勉強になります。

参照設定のCOMから「Microsoft Excel XX.X Object Library」を選択。
-------------------------------------------------------------------
Imports System.IO
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop

Public Class ExcelImport

''' <summary>
''' 読み込むExcelファイルのフォルダー名,末尾「\」,例 D:\Data\
''' </summary>
''' <value>フォルダー名</value>
''' <returns>フォルダー名</returns>
''' <remarks></remarks>
Public Property excelFolderName() As String
''' <summary>
''' 読み込むExcelファイル名,例 ABCDEF.xls
''' </summary>
''' <value>Excelファイル名</value>
''' <returns>Excelファイル名</returns>
''' <remarks></remarks>
Public Property excelFileName() As String
''' <summary>
''' エラーNo,0=OK,1=NG
''' </summary>
''' <value>数値</value>
''' <returns>数値</returns>
''' <remarks></remarks>
Public Property errNo() As Integer
''' <summary>
''' エラー内容
''' </summary>
''' <value>文字列</value>
''' <returns>文字列</returns>
''' <remarks></remarks>
Public Property errMes() As String

Public Sub New()
_excelFolderName = "D:\Data\"
_excelFileName = "ABCDEF.xls"
_errNo = 0
_errMes = ""
End Sub

''' <summary>
''' Excelファイルから取り込み
''' </summary>
''' <param name="sheetSelect">
''' 0=シート名指定,1以上の数値はシート番号</param>
''' <param name="sheetName">読み込むシート名</param>
''' <param name="colSelect">
''' 0=全列読み込み,1=列番号指定,2=列名指定</param>
''' <param name="colName">1次元のString配列,列番号or列名</param>
''' <returns>2次元のオブジェクト配列</returns>
''' <remarks>列名はシートの1行目に記載されていることが条件</remarks>
Public Function excelImport(sheetSelect As Integer, _
                sheetName As String, _
                colSelect As Integer, _
                colName() As String) _
                      As Object(,)

Dim excelApp As Excel.Application = Nothing
Dim wkbk As Excel.Workbook = Nothing
Dim wkbks As Excel.Workbooks = Nothing
Dim sheets As Excel.Sheets = Nothing
Dim wksheet As Excel.Worksheet = Nothing
Dim crRange As Excel.Range = Nothing
Dim crMax As Excel.Range = Nothing
Dim colMax As Integer
Dim rowMax As Integer
Dim colNo As Integer
Dim rowNo As Integer
Dim dummyStr As String

excelImport = Nothing

Try
dummyStr = excelFolderName.Substring(excelFolderName.Length - 1, 1)
If dummyStr <> "\" Then
excelFolderName = excelFolderName & "\"
End If

If sheetSelect = 0 Then
If sheetName = Nothing OrElse sheetName = "" Then
Throw New Exception("シート名が指定されていない")
End If
End If

If colSelect > 2 Then
Throw New Exception("列選択番号が不適切" & vbCrLf & _
                    "2以下のところ" & colSelect)
End If

If colSelect = 1 Then
If colName Is Nothing OrElse Not IsNumeric(colName(0)) Then
Throw New Exception("指定列番号が不適切" & vbCrLf & _
                    "配列の中身無し or 最初が数値では無い")
End If
End If

If colSelect = 2 Then
If colName Is Nothing OrElse colName(0) = "" Then
Throw New Exception("指定列名が不適切" & vbCrLf & _
                    "配列の中身無し or 最初が空")
End If
End If

If Not File.Exists(excelFolderName & excelFileName) Then
Throw New Exception("Excelファイルが存在しない " & vbCrLf & _
excelFolderName & excelFileName)
End If

'Excelアプリケーションの開始
excelApp = New Excel.Application
wkbks = excelApp.Workbooks' ← 暗黙的変換を回避
'ファイルオープン
wkbk = excelApp.Workbooks.Open(excelFolderName & excelFileName)
sheets = wkbk.Worksheets

If sheets.Count < sheetSelect Then
Throw New Exception("指定したシート番号がシート数以上" & vbCrLf & _
sheets.Count & " 以下に対し " & sheetSelect)
End If

If sheetSelect = 0 Then
'指定シート名を検索し設定
For Each oSheet In sheets
If sheetName = oSheet.Name Then
wksheet = oSheet
End If
Next
If wksheet Is Nothing Then
Throw New Exception("指定したシート名が見つからない " & sheetName)
End If
Else
'シート番号で設定
wksheet = sheets(sheetSelect)
End If

'列,行の最大値を抽出
crMax=wksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
colMax = crMax.Column
rowMax = crMax.Row

If colSelect = 1 Or colSelect = 2 Then
If colName.Length > colMax Then
Throw New Exception("指定列番号数が最大列数以上" & vbCrLf & _
           colMax & " 以下に対し " & colName.Length)
End If
End If

Dim cells = wksheet.Cells
Dim tl = cells
Dim br = cells

'全桁のデータを抽出
If colSelect = 0 Then
tl = cells(1, 1)
br = cells(rowMax, colMax)
crRange = wksheet.Range(tl, br)
excelImport = crRange.Value
End If

'列番号を指定してデータを抽出
If colSelect = 1 Then
Dim dummyData(rowMax, colName.Length - 1) As Object
Dim colNo2 As Integer = -1
For colNo = 0 To colName.Length - 1
If Not IsNumeric(colName(colNo)) Then Continue For
If Integer.Parse(colName(colNo)) = 0 Then Continue For
If Integer.Parse(colName(colNo)) > colMax Then Continue For
colNo2 += 1
For rowNo = 1 To rowMax
tl = cells(rowNo, Integer.Parse(colName(colNo2)))
br = cells(rowNo, Integer.Parse(colName(colNo2)))
dummyData(rowNo - 1, colNo) = wksheet.Range(tl, br).Value
Next
Next
If colNo2 > -1 Then
excelImport = dummyData
Else
Throw New Exception("指定列番号無し")
End If
End If

'列名を指定してデータを抽出
If colSelect = 2 Then
Dim dummyData(rowMax, colName.Length - 1) As Object
Dim colNo2 As Integer = -1
Dim loop1 As Integer
For colNo = 0 To colName.Length - 1
If colName(colNo) = "" Then Continue For
For loop1 = 1 To colMax
tl = cells(1, loop1)
br = cells(1, loop1)
If colName(colNo) = wksheet.Range(tl, br).Value Then
colNo2 += 1
For rowNo = 1 To rowMax
tl = cells(rowNo, loop1)
br = cells(rowNo, loop1)
dummyData(rowNo - 1, colNo2) = wksheet.Range(tl, br).Value
Next
Exit For
End If
Next
Next
If colNo2 > -1 Then
excelImport = dummyData
Else
Throw New Exception("指定列名無し")
End If
End If

Catch ex As Exception
errNo = 1
errMes = ex.Message
Finally
'解放(全ての解放を行うため、FinalReleaseComObjectを利用します)
If Not crRange Is Nothing Then
Marshal.FinalReleaseComObject(crRange)
crRange = Nothing
End If
If Not crMax Is Nothing Then
Marshal.FinalReleaseComObject(crMax)
crMax = Nothing
End If
If Not wksheet Is Nothing Then
Marshal.FinalReleaseComObject(wksheet)
wksheet = Nothing
End If
If Not sheets Is Nothing Then
Marshal.FinalReleaseComObject(sheets)
sheets = Nothing
End If
If Not wkbk Is Nothing Then
'元のExcelファイルは保存せず終了。
wkbk.Close(SaveChanges:=False)
Marshal.FinalReleaseComObject(wkbk)
wkbk = Nothing
End If
If Not wkbks Is Nothing Then
Marshal.FinalReleaseComObject(wkbks)
wkbks = Nothing
End If
If Not excelApp Is Nothing Then
excelApp.Quit()
Marshal.FinalReleaseComObject(excelApp)
excelApp = Nothing
End If
End Try

End Function
End Class
-------------------------------------------------------------------
検証用コード

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

Dim colName() As String = Nothing
Dim excelData(,) As Object
Dim errNo As Integer

Dim excelIn As New ExcelImport
excelIn.excelFolderName = "D:\Work\"
excelIn.excelFileName = "TEST.xlsx"
'シート番号を指定し全列,全行を取り込み
excelData = excelIn.excelImport(1, "", 0, colName)
errNo = excelIn.errNo
excelIn = Nothing

excelIn = New ExcelImport
excelIn.excelFolderName = "D:\Work\"
excelIn.excelFileName = "TEST.xlsx"
'シート名を指定し全列,全行を取り込み
excelData = excelIn.excelImport(0, "Sheet1", 0, colName)
errNo &= excelIn.errNo
excelIn = Nothing

excelIn = New ExcelImport
excelIn.excelFolderName = "D:\Work\"
excelIn.excelFileName = "TEST.xlsx"
'列番号を指定して取り込み
colName = {"1", "7", "22"}
excelData = excelIn.excelImport(0, "Sheet1", 1, colName)
errNo &= excelIn.errNo
excelIn = Nothing

excelIn = New ExcelImport
excelIn.excelFolderName = "D:\Work\"
excelIn.excelFileName = "TEST.xlsx"
'列名を指定して取り込み ※列名は1行目であること
colName = {"日付", "時間", "データA", "データB", "合計"}
excelData = excelIn.excelImport(1, "", 2, colName)
errNo &= excelIn.errNo
excelIn = Nothing

If errNo = 0 Then
MessageBox.Show("処理が終了しました。", _
                       "処理終了", _
                       MessageBoxButtons.OK)
Else
MessageBox.Show("エラーNo=" & errNo, _
                       "エラー", _
                       MessageBoxButtons.OK, _
                       MessageBoxIcon.Error)
End If
End Sub

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