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

元開発職→社内SE→派遣で営業支援→開発戻り浦島太郎状態の三流プログラマのIT技術メモ書き。 このメモが忘れっぽい自分とググってきた技術者の役に立ってくれれば幸いです。

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

前の記事の続きです。

最後に、コマンド オブジェクトによる新規レコードの挿入例です。

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

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

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

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

 

'接続型(コマンド オブジェクト)でDBにつなぐ

Dim cn As New OleDb.OleDbConnection

cn.ConnectionString = m_adpZipCode.Connection.ConnectionString'DataSetデザイナで定義してあるコネクション使う

'コネクションオープン

cn.Open()

'コマンドオブジェクトは高速化のため、1回だけインスタンス作成して使いまわす

Dim cmd As New OleDb.OleDbCommand()

cmd.Connection = cn

Dim sql As String

 

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

'コマンドオブジェクトのパラメータクリア

cmd.Parameters.Clear()

'SQL生成

sql = "INSERT INTO ZipCode (ZipCode , PrefName , CityName , TownName , EnableFlg) VALUES (?,?,?,?,?) "

'パラメータに挿入値追加

Dim plamZipcode As New OleDb.OleDbParameter("@ZipCode", System.Data.OleDb.OleDbType.VarChar)

plamZipcode.Value = row(2)

cmd.Parameters.Add(plamZipcode)

Dim plamPrefName As New OleDb.OleDbParameter("@PrefName", System.Data.OleDb.OleDbType.VarChar)

plamPrefName.Value = row(6)

cmd.Parameters.Add(plamPrefName)

Dim plamCityName As New OleDb.OleDbParameter("@CityName", System.Data.OleDb.OleDbType.VarChar)

plamCityName.Value = row(7)

cmd.Parameters.Add(plamCityName)

Dim plamTownName As New OleDb.OleDbParameter("@TownName", System.Data.OleDb.OleDbType.VarChar)

plamTownName.Value = row(8)

cmd.Parameters.Add(plamTownName)

Dim plamEnableFlg As New OleDb.OleDbParameter("@EnableFlg", System.Data.OleDb.OleDbType.Boolean)

plamEnableFlg.Value = False

cmd.Parameters.Add(plamEnableFlg)

cmd.CommandText = sql 'SQL文字列をコマンドオブジェクトに追加

'SQL処理を実行

cmd.ExecuteNonQuery()

lCount += 1

End While

 

'コネクションをクローズする。

cn.Close()

 

Dim dt_en As DateTime = DateTime.Now()

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

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

この結果は下記の通り。

開始時刻:2008/12/04 16:57:00

0行:00:00:00.0020000

10000行:00:00:05.2570000

20000行:00:00:10.4640000

30000行:00:00:15.4640000

40000行:00:00:20.4970000

50000行:00:00:25.5640000

60000行:00:00:30.5950000

70000行:00:00:35.6430000

80000行:00:00:40.7520000

90000行:00:00:45.8170000

100000行:00:00:50.9000000

110000行:00:00:56.0530000

120000行:00:01:01.1890000

終了時刻:2008/12/04 16:58:03

経過時間:00:01:02.6380000

TableAdapter.Updateとほぼ同等の速度ですが、総合的には若干こっちのほうが早いみたいです。

ちなみに、上記のコマンド オブジェクトによる新規レコードの挿入例では、コマンドオブジェクトを最初1回だけインスタンスを作って、使いまわしてますが、ループの中でコマンドオブジェクトを作成すると経過時間が

経過時間:00:01:28.6720000

となりました。

やはり、ループ内で、オブジェクトの作成はコストが高いそうです。

ちなみに、DataSet経由でDBにアクセスするときは非接続型、コマンドオブジェクトでコネクションを手動でオープンしてDBにアクセスするときは接続型と呼ばれるようです。

おそらく TableAdapter.Insert() ではメソッド実行毎にコネクションを接続し、切断してるのでしょう。

TableAdapter.Update()はコマンドオブジェクトとほぼ同じ速度からして、実は内部的には接続型で処理してるのではないでしょうか。

ま、推測ですが。

大量データをテーブルに入れるときはどの方法を使うか注意して使いましょう。

参考:

CSVファイルを読み込むには?

第2回:ADO.NET とデータベース操作

@IT .NETデータ・プロバイダによるレコードの挿入/削除/更新

追記(2009/02/23):

前の記事の2つ目の方法(TableAdapter.Update)で、100万行のテストデータを追加するコードを作ったところ、メモリをかなり使い果たしたあげく、処理にかなり時間がかかってしまいました。まあ、いったんDataTableを介するのメモリを数百メガ使うのは仕方ないのでしょうけど。。やはり、大量データを入れるときは、接続型のコマンドオブジェクトを使ったほうが得策です。