(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を介するのメモリを数百メガ使うのは仕方ないのでしょうけど。。やはり、大量データを入れるときは、接続型のコマンドオブジェクトを使ったほうが得策です。