3流プログラマのメモ書き

元開発職→社内SE→派遣で営業支援の三流プログラマのIT技術メモ書き。 このメモが忘れっぽい自分とググってきた技術者の役に立ってくれれば幸いです。(jehupc.exblog.jpから移転中)

(VB.Net)ADO.Netで大量のデータをテーブルに追加するときのレスポンス Part1

したいことは、郵便番号データCSVをDB(今回はOLEDB経由のmdb)にインポートすることです。

全国版の郵便番号データCSVは12万件程度あるのでDBにインポートもそれなりに時間がかかると思います。

試してみた方法はMSDN:「方法 : データベースに新しいレコードを挿入する」にあった下記の3つの方法です。

・TableAdapter.Insert メソッドを使用してデータベースに新規レコードを挿入

・TableAdapter.Update メソッドを使用してデータベースに新規レコードを挿入

・コマンド オブジェクトによる新規レコードの挿入

まず、TableAdapter.Insert メソッドで郵便番号データをDBにインポートするソースです。

郵便番号データを格納するテーブルを ZipCode とし、郵便番号、都道府県、市区町村、町名をインポートします。

CSVファイルを1行ずつ読み込み、TableAdapter.Insertで挿入します。

'CSVファイルをTextFieldParserクラスを使用して読み込む

Dim parser As New FileIO.TextFieldParser("KEN_ALL.CSV", System.Text.Encoding.GetEncoding("Shift_JIS"))

parser.TextFieldType = FileIO.FieldType.Delimited 'フィールドが区切られていることを指定

parser.SetDelimiters(",") ' 区切り文字はコンマ

 

'テーブルアダプタ,DataTable生成(データセットデザイナでテーブルアダプタ作成済み)

Dim tblAdp As New ZipCodeTableAdapter()

 

Dim dt_st As DateTime = DateTime.Now()

Console.WriteLine("開始時刻:" & dt_st.ToString())

 

Dim lCount As Int64 = 0

'CSVファイルの最後になるまでループ

While Not parser.EndOfData

Dim row As String() = parser.ReadFields() ' 1行読み込み

'1万件ごとの処理時間を算出

If lCount Mod 10000 = 0 Then

Dim dt As DateTime = DateTime.Now()

Console.WriteLine(lCount & "行:" & dt.Subtract(dt_st).ToString())

End If

'DBにデータ追加(郵便番号、都道府県、市区町村、町名)

tblAdp.Insert(row(2), row(6), row(7), row(8))

lCount += 1

End While

 

Dim dt_en As DateTime = DateTime.Now()

Console.WriteLine("終了時刻:" & dt_en.ToString())

Console.WriteLine("経過時間:" & dt_en.Subtract(dt_st).ToString())

これを実行した結果が下記です。

開始時刻:2008/12/04 16:10:51

0行:00:00:00.0520000

10000行:00:06:34.1280000

20000行:00:13:19.5580000

1万行処理するのに6分もかかり、あまりに時間かかりすぎたので途中で中断しました。

このまま12万件処理するとおおよそ78分かかる計算になります。かなりというか、相当遅すぎです。

次は TableAdapter.Update メソッドを使ったソースです。

'CSVファイルをTextFieldParserクラスを使用して読み込む

Dim parser As New FileIO.TextFieldParser("KEN_ALL.CSV", System.Text.Encoding.GetEncoding("Shift_JIS"))

parser.TextFieldType = FileIO.FieldType.Delimited 'フィールドが区切られていることを指定

parser.SetDelimiters(",") ' 区切り文字はコンマ

 

'テーブルアダプタ,DataTable生成(データセットデザイナでテーブルアダプタ作成済み)

Dim tblAdp As New ZipCodeTableAdapter()

'DataTable作成(データセットデザイナでZipCodeDataTable作成済み)

Dim dtbl As New RhythmSalesDataSet.ZipCodeDataTable()

 

Dim dt_st As DateTime = DateTime.Now()

Console.WriteLine("開始時刻:" & dt_st.ToString())

 

Dim lCount As Int64 = 0

'CSVファイルの最後になるまでループ

While Not parser.EndOfData

Dim row As String() = parser.ReadFields() ' 1行読み込み

'1万件ごとの処理時間を算出

If lCount Mod 10000 = 0 Then

Dim dt As DateTime = DateTime.Now()

Console.WriteLine(lCount & "行:" & dt.Subtract(dt_st).ToString())

End If

'DataTableに新規行追加。

Dim dataRow As DataRow

dataRow = dtbl.NewRow

dataRow("ZipCode") = row(2) '郵便番号追加

dataRow("PrefName") = row(6) '都道府県追加

dataRow("CityName") = row(7) '市区町村追加

dataRow("TownName") = row(8) '町名追加

dtbl.Rows.Add(dataRow) 'DataTableに行追加

lCount += 1

End While

 

'TableAdapter.Updateで、メモリ上のDataTableのデータをDBに反映

tblAdp.Update(dtbl)

 

Dim dt_en As DateTime = DateTime.Now()

Console.WriteLine("終了時刻:" & dt_en.ToString())

Console.WriteLine("経過時間:" & dt_en.Subtract(dt_st).ToString())

結果は下記の通りです。

開始時刻:2008/12/04 17:34:54

0行:00:00:00.0050000

10000行:00:00:00.6280000

20000行:00:00:01.2210000

30000行:00:00:01.7630000

40000行:00:00:02.4060000

50000行:00:00:02.9530000

60000行:00:00:03.5110000

70000行:00:00:04.0980000

80000行:00:00:04.6500000

90000行:00:00:05.2710000

100000行:00:00:05.8530000

110000行:00:00:06.4880000

120000行:00:00:07.1040000

終了時刻:2008/12/04 17:36:00

経過時間:00:01:05.9010000

この場合、1万行ごとの処理時間はDataTableに追加するだけなので、ほとんど意味がありません。

経過時間から120000行の時間を引いたものが、TableAdapter.UpdateでのDBへの挿入時間となります。

これだとすべて処理するのに58秒かかってますね。78分に比べると大幅に高速化されてます。

最後の例ですが、文字数の都合上次の記事にします。

ADO.Netで大量のデータをテーブルに追加するときのレスポンス Part2はこちら