テキストファイルをMySQLへ取り込む

２－８　テキストファイルをMySQLテーブルへ読込む

　MySQLテーブルの追加・更新の３回に引き続き　今回も同じテーマである。前回「元のMySQLのデータ、テキストファイルの件数共に　より多くのデータがある状態で何度か繰り返せば　正確な値に近い物が得られるように思う。（例えば、以前に提示したマスターに対して２０００件程度のテキストファイルを使用するようなものを準備できれば）。」と書いていたので、その点についていくつかの実験を行い、Visual Basic 2005 ExpressからMySQLを更新する処理の最後としたい。従って、今回の内容としては新しい項目は少ない。以下の点が今まで使用していなかった内容となろう。
１．Select文のパラメータが１３桁の文字になった点
２．MySQLテーブルの内容を初期化する処理
３．二つのテーブルから読込んだDataSetにインデックスを設定する

　内容については、順次説明してゆくが、以下の各項目で　以前に使用したマスターのテーブルを利用するようにする。このテーブルにはデータが２０万件弱入れてある。このテーブルに対し、千六百件強のテキストファイルを読み込んで追加・更新するという、まさに前回の最後に書いたものの実践となる。
１．テーブルの初期化とデータセット
　何度かテストを繰り返す予定なので、マスターテーブルを初期化し　更にデータをセットするバッチファイルを作成した（こちらのページを参照の事）。右の図に示したようにMySQLのコマンドを打ち込んだファイルを作成し、BtCMD.SQlという名前にて登録。次に、このファイルをMySQLを起動後に読み込んで実行する。この一つのバッチファイルを起動することで、対象のマスターテーブルを初期化し、次に既定のデータを書き込んで処理していまうわけだ。
２．コマンドによる更新
　基本的には、前回に紹介した「コマンドを利用してテキストファイルのデータを追加更新」の項目２の内容と変わりが無い。違いといえば、項目が２個から１０個に増えた点で　その分だけ処理を長く書かなければならない。以下にそのコードに一部を示す。なお、フォーム上にボタンがあると煩雑なので　処理はメニューから起動するように変更した。「マスター表示」が前回の内容。「コマンドテスト」が今回の２に、「自動作成テスト」が３、「取込テスト」は４にあたる。
・・・前半省略・・・
'接続文字列を設定
sqlCont.ConnectionString = MyconString
Iifile = File.OpenText("d:\nettest\Projects\mstset\mstmnt.txt")
Try
'MAK Select command
sqlCMDS = sqlCont.CreateCommand
sqlCMDS.CommandText = "select tancode from syotbl where tancode = ?tancode"
Dim MySParm = sqlCMDS.Parameters.Add("?tancode", MySqlDbType.VarChar, 13)
'Mak Update command
sqlCMDU = sqlCont.CreateCommand
sqlCMDU.CommandText = "UPDATE syotbl SET grpcode = ?grpcode, bmncode = ?bmncode, kancode = ?kancode" & _
", syoname = ?syoname, vdrcode = ?vdrcode, genka = ?genka, baika = ?baika" & _
", yyyymmdd = ?yyyymmdd, kubun = ?kubun " & _
" WHERE tancode = ?tancode"
sqlCMDU.Parameters.Add(New MySqlParameter("?tancode", MySqlDbType.VarChar))
sqlCMDU.Parameters.Add(New MySqlParameter("?grpcode", MySqlDbType.UInt16))
sqlCMDU.Parameters.Add(New MySqlParameter("?bmncode", MySqlDbType.UInt32))
sqlCMDU.Parameters.Add(New MySqlParameter("?kancode", MySqlDbType.VarString))
sqlCMDU.Parameters.Add(New MySqlParameter("?syoname", MySqlDbType.VarChar))
sqlCMDU.Parameters.Add(New MySqlParameter("?vdrcode", MySqlDbType.UInt32))
sqlCMDU.Parameters.Add(New MySqlParameter("?genka", MySqlDbType.Decimal))
sqlCMDU.Parameters.Add(New MySqlParameter("?baika", MySqlDbType.Decimal))
sqlCMDU.Parameters.Add(New MySqlParameter("?yyyymmdd", MySqlDbType.Decimal))
sqlCMDU.Parameters.Add(New MySqlParameter("?kubun", MySqlDbType.UInt16))
'Mak Insert command
sqlCMDI = sqlCont.CreateCommand
sqlCMDI.CommandText = "INSERT INTO syotbl (tancode, grpcode, bmncode, kancode, syoname, vdrcode, genka,
baika, yyyymmdd, kubun)" & _
" VALUES (?tancode, ?grpcode, ?bmncode, ?kancode, ?syoname, ?vdrcode, ?genka, ?
baika, ?yyyymmdd, ?kubun)"
sqlCMDI.Parameters.Add(New MySqlParameter("?tancode", MySqlDbType.VarChar))
sqlCMDI.Parameters.Add(New MySqlParameter("?grpcode", MySqlDbType.UInt16))
sqlCMDI.Parameters.Add(New MySqlParameter("?bmncode", MySqlDbType.UInt32))
sqlCMDI.Parameters.Add(New MySqlParameter("?kancode", MySqlDbType.VarString))
sqlCMDI.Parameters.Add(New MySqlParameter("?syoname", MySqlDbType.VarChar))
sqlCMDI.Parameters.Add(New MySqlParameter("?vdrcode", MySqlDbType.UInt32))
sqlCMDI.Parameters.Add(New MySqlParameter("?genka", MySqlDbType.Decimal))
sqlCMDI.Parameters.Add(New MySqlParameter("?baika", MySqlDbType.Decimal))
sqlCMDI.Parameters.Add(New MySqlParameter("?yyyymmdd", MySqlDbType.Decimal))
sqlCMDI.Parameters.Add(New MySqlParameter("?kubun", MySqlDbType.UInt16))
'ここからデータ読み込み
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
'
Try
'接続を作成して
sqlCont.Open()
'パラメータ値を指定して
MySParm.Value = Trim(Iidata(0))
'検索結果を確認する。
sqlDr = sqlCMDS.ExecuteReader
I = 0
While (sqlDr.Read())
I = I + 1
End While
sqlCont.Close()
'接続を作成して
sqlCont.Open()
Select Case I
Case 0
sqlCMDI.Parameters("?tancode").Value = Trim(Iidata(0))
sqlCMDI.Parameters("?grpcode").Value = Iidata(1)
sqlCMDI.Parameters("?bmncode").Value = Iidata(2)
sqlCMDI.Parameters("?kancode").Value = Iidata(3)
sqlCMDI.Parameters("?syoname").Value = Trim(Iidata(4))
sqlCMDI.Parameters("?vdrcode").Value = Iidata(5)
sqlCMDI.Parameters("?genka").Value = Iidata(6)
sqlCMDI.Parameters("?baika").Value = Iidata(7)
sqlCMDI.Parameters("?yyyymmdd").Value = Iidata(8)
sqlCMDI.Parameters("?kubun").Value = Iidata(9)
'内容を追加します。
sqlCMDI.ExecuteNonQuery()
J1 = J1 + 1
Case Else
sqlCMDU.Parameters("?tancode").Value = Trim(Iidata(0))
sqlCMDU.Parameters("?grpcode").Value = Iidata(1)
sqlCMDU.Parameters("?bmncode").Value = Iidata(2)
sqlCMDU.Parameters("?kancode").Value = Iidata(3)
sqlCMDU.Parameters("?syoname").Value = Trim(Iidata(4))
sqlCMDU.Parameters("?vdrcode").Value = Iidata(5)
sqlCMDU.Parameters("?genka").Value = Iidata(6)
sqlCMDU.Parameters("?baika").Value = Iidata(7)
sqlCMDU.Parameters("?yyyymmdd").Value = Iidata(8)
sqlCMDU.Parameters("?kubun").Value = Iidata(9)
'内容を更新します。
sqlCMDU.ExecuteNonQuery()
J2 = J2 + 1
・・・以下省略・・・

　前回からのコードの違いは、項目が増えた事が主だが、一点
Dim MySParm = sqlCMDS.Parameters.Add("?tancode", MySqlDbType.VarChar, 13)
の部分は、前回（項目内用に従って書き換えると）は
Dim MySParm = sqlCMDS.Parameters.AddWithValue("?tancode", MySqlDbType.VarChar)
となっていた。AddWithValueの解説を読んでもよく理解できなかったのだが、下のコードで処理しようとすると、「int32型の値が大きすぎるか、または小さすぎます」というランタイムエラーとなった。入力データが文字ではなく数字として認識されてしまうらしい。AddWithValueをAddに代えると　文字数の設定が行えたので、この形で実行すると問題なく動作した。
３．DataSetを利用した追加・更新
　前回行ったのと同様に　一旦MySQLのテーブル内容から索引付きDataSetを作成。このDataSetに対して追加・更新を行って　最後にDataSetの変更内容を　自動作成されたコマンドを利用してテーブルに戻してやる流れだ。コード自体はそう変更なく、項目を増やすだけで使用出来た。
・・・前半省略・・・
Try
'接続を作成します
sqlDA.SelectCommand = New MySqlCommand()
sqlDA.SelectCommand.CommandType = CommandType.Text
sqlDA.SelectCommand.CommandText = "select * from syotbl"
sqlDA.SelectCommand.Connection = New MySqlConnection(MyconString)
'スキーマ情報をDatasetに追加する
sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
'検索結果を確認する。
sqlDA.Fill(resultDS, "syotbl")
II2 = Now() 'DATASET作成済時間
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
Try
oDR = resultDS.Tables("syotbl").Rows.Find(Trim(Iidata(0)))
If oDR Is Nothing Then
oDR = resultDS.Tables("syotbl").NewRow()
oDR("tancode") = Trim(Iidata(0))
oDR("grpcode") = Iidata(1)
oDR("bmncode") = Iidata(2)
oDR("kancode") = Iidata(3)
oDR("syoname") = Trim(Iidata(4))
oDR("vdrcode") = Iidata(5)
oDR("genka") = Iidata(6)
oDR("baika") = Iidata(7)
oDR("yyyymmdd") = Iidata(8)
oDR("kubun") = Iidata(9)
resultDS.Tables("syotbl").Rows.Add(oDR)
J1 = J1 + 1
Else
'テーブル"syotbl"の先頭値に対する更新処理
oDR("tancode") = Trim(Iidata(0))
oDR("grpcode") = Iidata(1)
oDR("bmncode") = Iidata(2)
oDR("kancode") = Iidata(3)
oDR("syoname") = Trim(Iidata(4))
oDR("vdrcode") = Iidata(5)
oDR("genka") = Iidata(6)
oDR("baika") = Iidata(7)
oDR("yyyymmdd") = Iidata(8)
oDR("kubun") = Iidata(9)
J2 = J2 + 1
End If
・・・以下省略・・・

４．作成するDataSetを小さくできないか？
　単純に考えると、テーブル内の全データを一旦すべてDatasetに取り込むのはいかにも時間がかかりそうな処理だ。この部分を入力ファイルのデータを基に作成出来ないかと考えて、以下のようにしてみた。
１．ワークのテーブルを作成
２．ワークのテーブルの内容を初期化
３．ワークのテーブルにテキストファイルの単品コードを追加
４．ワークのテーブルとMySQLのテーブルを結合し、DataSetを作成する。
５．テキストファイルを再度読み込み、追加・更新をDataSetに書き込む。
６．DataSetの変更を元のMySQLテーブルへ書き戻す。
この流れの中で、一番先のテーブル作成はPHPMySQLAdminによって作成。２から６までが実質的な処理となる。処理中でデータファイルを２度読みするような形となるが　作成されるDataSetはスリムな形で作成できるので　ひょっとしたら早く動くかもしれない。

５．ワークのテーブルを初期化
　まず、テーブルの初期化であるが、これは以下のようなコードで実現している。簡単に言えば、ワークテーブルを初期化するSQL文を作成し、これをExecuteNonQuery()文で起動するのである。
Sub DtCLR()
Dim Cn As New MySql.Data.MySqlClient.MySqlConnection
Dim SQL As MySql.Data.MySqlClient.MySqlCommand
'
Dim s As String
'
s = "server=localhost;user ID=hanbai;password=password;database=tanpin;"
Cn.ConnectionString = s
SQL = Cn.CreateCommand
SQL.CommandText = "delete wktbl.* from wktbl"
Cn.Open()
SQL.ExecuteNonQuery()
'
Cn.Close()
SQL.Dispose()
Cn.Dispose()
End Sub
６．ワークテーブルにデータを書き込む
　これは、いままで行って来たことと同じだ。つまり、上の２番のような形でテキストファイルを読み込み、単品のコードをテーブルに書き込むもので以下のようなコードで実現している。なお、今回はテキストファイルのコードの重複が無いものとして　単純な追加でワークのテーブルに書き出している。
Try
'Mak Insert command
sqlCMDI = sqlCont.CreateCommand
sqlCMDI.CommandText = "INSERT INTO wktbl (tancode) VALUES (?tancode)"
sqlCMDI.Parameters.Add(New MySqlParameter("?tancode", MySqlDbType.VarChar))
'ここからデータ読み込み
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
'
Try
'接続を作成して
sqlCont.Open()
sqlCMDI.Parameters("?tancode").Value = Trim(Iidata(0))
'内容を追加します。
sqlCMDI.ExecuteNonQuery()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
sqlCont.Close()
End Try
End While
'データ読み込みここまで
７．DataSetの作成とテキストファイルの追加・更新
　上の４に示した内容を順次書いてゆくと、下のようになる。ここで注意したいのは、二つのテーブルから作成されたDatasetには　索引がつけられていないという事だ。索引は前回は一つのテーブルからデータを取得した為
sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKeｙ
のような形式で作成する事ができたが、今回はそうはいかない。この部分は、
resultDS.Tables("syotbl").PrimaryKey = New DataColumn() {resultDS.Tables("syotbl").Columns("tancode")}
のような形で　作成されたDataSetに対し検索用の索引を付与することで解決している。
後は、上に示した３と同様の処理を行えば良いだけだ。
・・・前半省略・・・
'MySQL接続文字列
MyconString = "server=localhost;user id=hanbai;password=password;database=tanpin;"
Iifile = File.OpenText("d:\nettest\Projects\mstset\mstmnt.txt")
Call DtCLR() 'DB初期化
Call Dtset() 'DBセット
II3 = Now()
Try
'接続を作成します
sqlDA.SelectCommand = New MySqlCommand()
sqlDA.SelectCommand.CommandType = CommandType.Text
sqlDA.SelectCommand.CommandText = "SELECT syotbl.* FROM wktbl INNER JOIN syotbl ON wktbl.tancode =
syotbl.tancode;"
sqlDA.SelectCommand.Connection = New MySqlConnection(MyconString)
'検索結果を確認する。
sqlDA.Fill(resultDS, "syotbl")
'===============主キーを設定
resultDS.Tables("syotbl").PrimaryKey = New DataColumn() {resultDS.Tables("syotbl").Columns("tancode")}
II2 = Now() 'DATASET作成済時間
'
While Iifile.Peek > -1
Iinstr = Iifile.ReadLine
Iidata = Split(Iinstr, ",")
Try
oDR = resultDS.Tables("syotbl").Rows.Find(Trim(Iidata(0)))
If oDR Is Nothing Then
oDR = resultDS.Tables("syotbl").NewRow()
oDR("tancode") = Trim(Iidata(0))
oDR("grpcode") = Iidata(1)
oDR("bmncode") = Iidata(2)
oDR("kancode") = Iidata(3)
oDR("syoname") = Trim(Iidata(4))
oDR("vdrcode") = Iidata(5)
oDR("genka") = Iidata(6)
oDR("baika") = Iidata(7)
oDR("yyyymmdd") = Iidata(8)
oDR("kubun") = Iidata(9)
resultDS.Tables("syotbl").Rows.Add(oDR)
J1 = J1 + 1
Else
'テーブル"syotbl"の先頭値に対する更新処理
oDR("tancode") = Trim(Iidata(0))
oDR("grpcode") = Iidata(1)
oDR("bmncode") = Iidata(2)
oDR("kancode") = Iidata(3)
oDR("syoname") = Trim(Iidata(4))
oDR("vdrcode") = Iidata(5)
oDR("genka") = Iidata(6)
oDR("baika") = Iidata(7)
oDR("yyyymmdd") = Iidata(8)
oDR("kubun") = Iidata(9)
J2 = J2 + 1
End If
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
End While
'データ処理がおわったら元テーブルを更新
Try
'Update文の自動実行のためにCommandBuilderオブジェクトを作成する
sqlCmdBldr = New MySqlCommandBuilder(sqlDA)
'DataSetに対する更新結果をDBに反映
sqlDA.Update(resultDS, "syotbl")
'MessageBox.Show("レコードは更新されました")
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
Iifile.Close()
End Try
・・・以下省略・・・
８．処理時間について
　ここまでの処理は前回までのまとめのような形なので、順番にテーブルの列名を変えてゆけば実現できると思う。
　問題はその動作時間である。前回の内容のようにミリ秒といったレベルではなく　何分何秒の世界なので以下にその結果をまとめたものを書いてみます。（いずれも、追加４２件、更新１６１８件。なお、信頼性を高める為にアプリ起動後　パフォーマンスモニターを開き、メモリー使用量とディスクアクセス量が安定してから処理を開始している。）

　コマンドによる処理 DataSetによる処理 SQL文でDataSetを制限

１回目開始 16:35:51 16:49:48 19:30:51

１回目ワークテーブル作成　　 19:32:31 (1:40)

１回目DataSet作成　 16:50:52 (1:04) 19:32:39 (0:08)

１回目終了 16:37:42 (1:51) 16:53:00 (2:08) (3:12) 19:34:27 (1:48) (3:36)

２回目開始 20:16:20 20:23:44 20:32:27

２回目ワークテーブル作成　　 20:33:52 (1:25)

２回目DataSet作成　 20:24:50 (1:06) 20:33:56 (0:04)

２回目終了 20:18:15 (1:55) 20:26:27 (1:37) (2:43) 20:35:22 (1:26) (2:55)

　結果を見てみると、コマンドによる処理が最も早く、テキストファイルを二度読みしてDataSetを小さく作った場合が一番時間がかかってしまっている。２度行って、2度共同じ結果であるから　間違いないだろう。結果内容は　非常に興味深いものとなったので、以下いくつかの考察を試みたい。
○　テキストファイルを読み込んで　その内容をデータベースに反映する処理自体は　１回目のDataSetによる処理以外の場合を除くと、コマンドからの起動の場合が一番遅く、１分５０秒程度要している。テキストファイルを読み込む時間が同じとすれば、その差は毎回ディスクアクセスをする場合と一度変更をメモリー内に溜め込む事による差と認識できる。
○　同じテキストを読み込み、その内容を書くのであるから、SQL文を使用する３番目のパターンと1回目のパターンの違いは、Selectコマンドを事前に発行し判定する分　及び書き込む項目数が違う事によるものと思われる。
○　対象テーブルを全てメモリーに展開するのに要した時間は１分５秒程度。これに対して１６００件を処理する場合は５秒。索引をつける時間・二つのテーブルからデータ抽出時間が後者の場合余計にかかったと想定すると　およその時間見積もりは出来るだろう。

　以上の結果から、最も早いのはDataSetを利用せず直接コマンドによって読み書きするパターンである。しかし、これはローカルマシンで実行した場合であるという点は留意すべきだ。データ処理の度に　データベースとの接続を切断し再度開き直す処理が果たしてネットワークを介して動作させる場合どうなのか？さらに、他のマシンからのアクセスが一定時間拒否される点はどうなるか。サーバーのマシンで動作させる前提で　なおかつネットワークからのアクセスが行われない時間帯を確保できるのならば　この方法を検討してみるのも悪くは無いだろう。
　二番目の方法の場合はどうか？一度データベースの内容をメモリーに展開するのに１分以上を要する。更に、右の図の二番目に示したように（図はそれぞれ３つのパターンを実行した時の、メモリーとディスクアクセスの状態の模式図-下手な図で申し訳ありません）二つ目のパターンの場合はメモリーをほぼ１００％使用してしまう。メモリー使用量が２０％程度しか無い他の２パターンと比較するとその差は歴然としている。データ行がそれほど無い場合以外、例えば（MySQLでそのような行のテーブルを使うことは無いと思うが）１０００行単位のテーブルを使用する場合にこのニ番目のパターンを使用するのは明らかに危険である。
　三番目のパターンは　今回一番時間がかかっているが、これはテキストファイルを二度読みした為で、もし、何らかの形で使用するDataSetの中身を制限できるとしたら、この方法が実質的には一番望ましいのではないか？１６００件のテーブルデータを抽出するのに５秒程度、殆ど問題にならない程度の時間だ。実際の書き出しも他の２パターンよりも明らかに短縮されている。（書き出すべきデータ量が少ないのだから当然かとも思うが）。
　いずれの方法を採るべいきかは、使用する状況によって違うものにすべきで、その点をよく考えてからどうすべきか決定しなければならない。そうしないと、メモリー使用量が膨大になりすぎたり、速度が出なかったりする可能性がある。それぞれのパターンの特徴を良く理解しておく事が大切となる所以である。
プログラムの内容を上のように書くと、インデントされていない為に読みづらくなってしまいます。この為、内容をPDF化したものをつけてあります。必要ならこちらを参照してみて下さい。PDFファイル。ちなみに、このファイルはVisualBasic 2005 Expressの内容をOpenOffice.Org2.4のWriterに貼り付けし　PDFにエクスポートしたものです。テキストコピー可の形で作成していますので、そのまま使用することも可能です。

	コマンドによる処理		DataSetによる処理			SQL文でDataSetを制限
１回目開始	16:35:51		16:49:48			19:30:51
１回目ワークテーブル作成						19:32:31	(1:40)
１回目DataSet作成			16:50:52	(1:04)		19:32:39	(0:08)
１回目終了	16:37:42	(1:51)	16:53:00	(2:08)	(3:12)	19:34:27	(1:48)	(3:36)
２回目開始	20:16:20		20:23:44			20:32:27
２回目ワークテーブル作成						20:33:52	(1:25)
２回目DataSet作成			20:24:50	(1:06)		20:33:56	(0:04)
２回目終了	20:18:15	(1:55)	20:26:27	(1:37)	(2:43)	20:35:22	(1:26)	(2:55)