Visual Basic 中学校 掲示板 投稿内容
タグのない投稿を抽出 統計 RSS

Visual Basic 中学校 > 投稿一覧 >

mdbのテーブルへのテキストファイルの一括登録について 解決済み

タグの編集...

投稿者 koutukasa   (社会人)   投稿日時 2011/5/24 21:34:45
開発環境はVB2008express edition OSは windows7 homepremiumです。
VBおよびデータベースは主にVisual Basic中学校を参考にさせてもらって学習してきました。
現在、mdbのデータベースの中に複数のテーブルを作成しており、個々のテーブルのデータを
CSV形式のテキストファイルで一括で登録することで更新しています。その登録のしかたとして、
datatableを利用して、現在のテーブルのデータをループして削除した上で、テキストファイルを
1行づつ読み込んで、行単位で追加をするという方法で処理しています。
ところが、この方法だと数千行のデータの登録の場合は、数分で登録でき実用に耐えるのですが、テーブルの種類も7テーブル位あり、数十万行のデータもあるので、実用上困難な状況です。
高速な登録の仕方をお教え願えないでしょうか。データの更新は全取替えでかまいません。






投稿者 shu   (社会人)   投稿日時 2011/5/24 23:03:25
DoCmd.TransferDatabase でAccess上のVBAで一括テキストインポートが出来るので
Accessを起動してこの処理を行うようにしてみてはどうでしょう?

.Netからの操作だとkoutukasaさんの行っている方法にトランザクションによる一括書き込み
をするぐらいでしょうか。といっても一度にたくさんのレコードを登録してしまうと時間がかかりすぎるのでどの程度のレコード数がよいか調整しそのレコード数毎にコミットしていくような感じに
なるかと思います。

投稿者 shu   (社会人)   投稿日時 2011/5/24 23:06:08
追記:
テキストファイルの場合、TransferDatabaseでなくTransferTextだったかもです。

投稿者 魔界の仮面弁士   (社会人)   投稿日時 2011/5/25 13:16:55
> データの更新は全取替えでかまいません。
対象となるテキストファイルが、 CSV もしくは固定長テキストである場合は、
 SELECT * INTO [NewTable1] FROM [Text;Database=C:\Folder\].[Test.csv]
のようにして 新規テーブルに変換するか、もしくは
 INSERT INTO [Table1] SELECT * FROM [Text;Database=C:\Folder\].[Test.csv]
のようにして登録するのが高速です。Schema.ini を作る必要がありますけれども。


> 数千行のデータの登録の場合は、数分で登録でき実用に耐えるのですが
ということは、変換速度は秒間10~100行程度といったところでしょうか。
PC のスペックにも依存しますが、それはかなり遅いように感じます。
現在はどのようにして登録していますか?

後述のソースで 50万行を登録してみましたが、当方では平均 18.6 秒 で登録できました。


> 高速な登録の仕方をお教え願えないでしょうか。
OleDb 利用だとしたら、Dao のインターフェイスを使った方が高速です。

VB6 から行うと、.NET 層を通らないのでさらに高速化できますが、
今回は 2008 とのことなので、2008 での Dao サンプルを書いておきます。
(なお、最速は Access 経由で取り込むことです)

CREATE TABLE TABLE1 (
  COL1 INTEGER PRIMARY KEY,
  COL2 CHAR(6) NOT NULL,
  COL3 NVARCHAR(20) NULL,
  COL4 DATE DEFAULT #1/1/2000# NOT NULL
)
というテーブルを持った Sample.mdb に、50万行のランダムデータを登録します。
当方では


Imports System.Runtime.InteropServices
'新規プロジェクトを用意し、フォームに Button1 と Label1 を貼ってください。 
'それと、Dao.dll を参照設定しておいてください。私が使ったのは、Office 2007 版の PIA です。 
Public Class Form1

    Private Sub Button1_Click(ByVal sender As ObjectByVal e As EventArgs) Handles Button1.Click
        Dim mdbPath As String = "C:\sample.mdb"
        Dim rTest As New Random()   'デモデータ作成用 

        'データベースを開く。 
        Dim de As dao.DBEngine = New dao.DBEngineClass()
        Dim ws As dao.Workspace = de.CreateWorkspace("""Admin""")
        '高速化のため、True を指定して排他モードにする。 
        Dim db As dao.Database = ws.OpenDatabase(mdbPath, True)

        '処理時間測定開始 
        Dim sw As Stopwatch = Stopwatch.StartNew()

        ws.BeginTrans()

        '全件削除 
        db.Execute("DELETE TABLE1")

        'Tableタイプのレコードセットとして開く 
        Dim rs1 As dao.Recordset
        rs1 = db.OpenRecordset("TABLE1", dao.RecordsetTypeEnum.dbOpenTable)

        'Fieldオブジェクトを変数にキャッシュする 
        Dim fields As dao.Fields
        Dim cols As New List(Of dao.Field)()
        fields = rs1.Fields
        For n As Integer = 0 To fields.Count - 1
            cols.Add(fields.Item(n))
        Next
        ReleaseObject(fields)

        '本題。50万回のループ処理 
        For n As Integer = 1 To 500000
            Dim col1 As Integer = n
            Dim col2 As String = n.ToString("000000")
            Dim col3 As String = New String("*"c, rTest.Next(21))
            Dim col4 As Date = Now

            rs1.AddNew()
            cols(0).Value = col1
            cols(1).Value = col2
            cols(2).Value = col3
            cols(3).Value = col4
            rs1.Update()
        Next

        'キャッシュしていたFieldオブジェクトを解放 
        For n As Integer = cols.Count - 1 To 0 Step -1
            ReleaseObject(cols(n))
        Next
        cols.Clear()

        'レコードセットを閉じる 
        rs1.Close()
        ReleaseObject(rs1)

        ws.CommitTrans()

        '時間測定終了 
        sw.Stop()
        Label1.Text = sw.ToString()

        'データベースを閉じる 
        db.Close()
        ReleaseObject(db)
        ws.Close()
        ReleaseObject(ws)
        ReleaseObject(de, True)
    End Sub

    Private Sub ReleaseObject(Of T)(ByRef o As T, Optional ByVal force As Boolean = False)
        If o IsNot Nothing Then
            If Marshal.IsComObject(o) Then
                If force Then
                    Marshal.FinalReleaseComObject(o)
                Else
                    Marshal.ReleaseComObject(o)
                End If
                o = Nothing
            End If
        End If
    End Sub
End Class


投稿者 koutukasa   (社会人)   投稿日時 2011/5/27 03:40:17
shuさん
早速に回答いただきありがとうございます。ちょっと体調をくずしているため確認が遅れてしまいました。
はじめに記載しておけばよかったのですが、accessの環境がない状態で更新しているためvb.netでできる方法をさがしております。
また、追記でお知らせいただいたように、テキストファイルの場合は、TransferTextのようです。


投稿者 koutukasa   (社会人)   投稿日時 2011/5/27 03:47:49
魔界の仮面弁士さん、丁寧なご指摘いただきありがとうございます。早速、教授いただいた方法で試してみようと思っていますが、すこし体調をくずしているため、結果の報告はすこし、お時間をいただきたいと思います。

投稿者 魔界の仮面弁士   (社会人)   投稿日時 2011/5/27 10:27:13
修正前のコードを掲載していました…。
訂正させてください。

★修正1★
        '全件削除  
        db.Execute("DELETE TABLE1")
上記は
        '全件削除 
        db.Execute("DELETE FROM TABLE1")
と修正してください。FROM が無い場合は構文エラーになります。


★修正2★
        '時間測定終了  
        sw.Stop()
        Label1.Text = sw.ToString()
この部分は、
        '時間測定終了   
        sw.Stop()
        Label1.Text = sw.Elapsed.ToString()
の間違いです。

Elapsed 付きなら、"00:00:18.1205812" といった文字列が表示されますが、
Elapsed を忘れると "System.Diagnostics.Stopwatch" と表示されてしまいます。


★修正3★
    Private Sub ReleaseObject(Of T)(ByRef o As T, Optional ByVal force As Boolean = False)
        If o IsNot Nothing Then
            If Marshal.IsComObject(o) Then
                If force Then
                    Marshal.FinalReleaseComObject(o)
                Else
                    Marshal.ReleaseComObject(o)
                End If
                o = Nothing
            End If
        End If
    End Sub

上記は、Nothing の代入箇所に誤りがあります。
下記に差し替えてください。
    Private Sub ReleaseObject(Of T)(ByRef o As T, Optional ByVal force As Boolean = False)
        If o IsNot Nothing Then
            If Marshal.IsComObject(o) Then
                If force Then
                    Marshal.FinalReleaseComObject(o)
                Else
                    Marshal.ReleaseComObject(o)
                End If
            End If
            o = Nothing
        End If
    End Sub



※補足※
> 'それと、Dao.dll を参照設定しておいてください。私が使ったのは、Office 2007 版の PIA です。 
より正確には、私は VSTO 3.0 版の Dao.dll (下記)を使用していました。
C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\dao.dll

参照設定の「.NET」タブに "Dao" や "Microsoft.Office.interop.access.dao" というエントリーが
見当たらない場合には、上記 dao.dll がどこかのフォルダーにインストールされていないかどうか、
探してみて、ファイル名指定で参照してみてください。

もしも DAO の PIA が見当たらない場合は、Office インストーラの「機能の追加/削除」で
「.NET プログラミングサポート」機能をインストールすれば入手できるかと思います。

それでも見つからない場合は、下記から入手する事が出来ます。

≪Office 2010≫
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=938fe8ad-583b-4bd7-a345-23250dc15855

≪Office 2007≫
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=59daebaa-bed4-4282-a28c-b864d8bfa513

≪Office 2003≫
http://support.microsoft.com/kb/897646/ja
http://www.microsoft.com/downloads/details.aspx?FamilyID=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad

≪Office XP≫
http://support.microsoft.com/kb/328912/ja
http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52

《Office プライマリ相互運用機能アセンブリ(PIA : Primary Interop Assemblies)≫
http://msdn.microsoft.com/ja-jp/library/kh3965hw.aspx
http://msdn.microsoft.com/ja-jp/library/15s06t57.aspx


なお、
 Dao.dll
の代わりに
 Microsoft.Office.interop.access.dao.dll
の PIA を使う事もできます。前者は DAO 3.6 用、後者は DAO 12.0 用です。
後者を使う場合は、先のコードの先頭に以下の2行を加えてください。
Imports Microsoft.Office.Interop.Access
Imports Microsoft.Office.Interop.Access.Dao


投稿者 koutukasa   (社会人)   投稿日時 2011/5/30 21:56:45
魔界の仮面弁士さん
お教えいただいた方法で実行しところ私の環境でも22.8秒で処理できました。
そこで、下記の条件で同様に実行できるのではと考え実行しましたが、処理はエラー表示なく実行されますがmdbファイルへは結果がまったく反映されません、おそらく、私自身が一番基本的なところを理解していないのではないかと不安に思っていますが、何度繰り返しても、同じ結果になってしまいます。
できれば、原因をお教え願えないでしょうか、よろしくお願いします。

Imports System.Data.OleDb
Imports Microsoft.Office.Interop
Imports System.IO
Imports System.Runtime.InteropServices
Public Class Form1

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Thisfolder As String
        Thisfolder = Application.StartupPath & "\K_gd.mdb"
        If IO.File.Exists(Thisfolder) Then
        Else
            MsgBox("テーブルファイルが見つかりません", MsgBoxStyle.Critical, "警告")
            Exit Sub
        End If
        Dim de As dao.DBEngine = New dao.DBEngineClass()
        Dim ws As dao.Workspace = de.CreateWorkspace("", "Admin", "")
        '高速化のため、True を指定して排他モードにする。 
        Dim db As dao.Database = ws.OpenDatabase(Thisfolder, True, False, ";PWD=pasword")
        ws.BeginTrans()
        db.Execute("delete from yaku")
        Dim rs1 As dao.Recordset
        rs1 = db.OpenRecordset("yaku", dao.RecordsetTypeEnum.dbOpenTable)
        'Fieldオブジェクトを変数にキャッシュする 
        Dim fields As dao.Fields
        Dim cols As New List(Of dao.Field)()
        fields = rs1.Fields
        For n As Integer = 0 To fields.Count - 1
            cols.Add(fields.Item(n))
        Next
        ReleaseObject(fields)
       
        Dim Cnt As Long
        Cnt = 0
        Dim strdata(0 To 7) As String

        Dim reader As New IO.StreamReader(Application.StartupPath & "\" & TextBox1.Text, System.Text.Encoding.GetEncoding("Shift-JIS"))
        Do While reader.Peek > -1
            Cnt = Cnt + 1
            strdata = Split(reader.ReadLine, ",")
            'データ新規追加処理
            rs1.AddNew()
            If strdata(0) <> "" Then
                cols(0).Value = CInt(strdata(0))
            End If
            If strdata(1) <> "" Then
                cols(1).Value = CDate(Strings.Replace(strdata(1), ".", "/"))
     
            End If
            If strdata(3) <> "" Then
                cols(2).Value = strdata(3)
            End If
            If strdata(4) <> "" Then
                cols(3).Value = strdata(4)
            End If
            If strdata(6) <> "" Then
                cols(4).Value = strdata(6)
            End If
            If strdata(7) <> "" Then
                cols(5).Value = strdata(7)
            End If
            If strdata(2) <> "" Then
                cols(6).Value = CInt(strdata(2))
            End If
            If strdata(5) <> "" Then
                cols(7).Value = strdata(5)
            End If
            rs1.Update()
        Loop
        reader.Close()
        'キャッシュしていたFieldオブジェクトを解放 
        For n As Integer = cols.Count - 1 To 0 Step -1
            ReleaseObject(cols(n))
        Next
        cols.Clear()
        'レコードセットを閉じる 
        rs1.Close()
        ReleaseObject(rs1)

        If Cnt > 0 Then
            MsgBox(Cnt & "件を登録しました", MsgBoxStyle.Information, "結果報告")
        End If
        'データベースを閉じる 
        db.Close()
        ReleaseObject(db)
        ws.Close()
        ReleaseObject(ws)
        ReleaseObject(de, True)
    End Sub

    Private Sub ReleaseObject(Of T)(ByRef o As T, Optional ByVal force As Boolean = False)
        If o IsNot Nothing Then
            If Marshal.IsComObject(o) Then
                If force Then
                    Marshal.FinalReleaseComObject(o)
                Else
                    Marshal.ReleaseComObject(o)
                End If
            End If
            o = Nothing
        End If
    End Sub
End Class


投稿者 shu   (社会人)   投稿日時 2011/5/30 22:30:41
ぱっと見、コミットしてない気がする。

投稿者 koutukasa   (社会人)   投稿日時 2011/5/31 18:15:03
shuさんありがとうございます。
一応
loopの後ろにws.CommitTrans()を追加することで、mdbに処理が反映されました。
ただ、魔界の仮面弁士さんが例示していただいた処理では、ws..CommitTrans()が
なくても反映することができ、私の書いた処理では明示的に追加しなければならないのか
理解できません。できましたらお教えください。


投稿者 koutukasa   (社会人)   投稿日時 2011/5/31 19:02:50
 魔界の仮面弁士さん、以下の処理が最初に実施していた処理です。
余計なことしていたため時間を費やしていたのでしょうか。お教えください。
Dim Thisfolder As String
        Dim maintable As New DataTable
        Dim adapter As New OleDbDataAdapter
        Thisfolder = Application.StartupPath & "\K_gd.mdb"
        If IO.File.Exists(Thisfolder) Then
        Else
            MsgBox("テーブルファイルが見つかりません", MsgBoxStyle.Critical, "警告")
            Exit Sub
        End If
        Dim Passw As String = "password"
        Dim UserId As String = "Admin"
        Dim ConnectionString As String
        ConnectionString = "Provider=""Microsoft.Jet.OLEDB.4.0"";"
        ConnectionString &= "Data Source=""" & Thisfolder & """;"
        ConnectionString &= "User ID=" & UserId & ";"
        ConnectionString &= "Jet OLEDB:Database Password=" & Passw
        Dim Cn As New OleDbConnection(ConnectionString)
        Dim SQLCM As OleDbCommand = Cn.CreateCommand
        adapter = New OleDbDataAdapter(SQLCM)
        SQLCM.CommandText = "Select * From yaku"
        adapter.Fill(maintable)
        Dim Row2 As DataRow
        For Each Row2 In maintable.Rows
            Row2.Delete()
            Dim sql2 As String = ""
            Select Case Row2.RowState
                Case DataRowState.Deleted
                    sql2 = "DELETE FROM yaku"
            End Select
            SQLCM.CommandText = sql2
            Cn.Open()
            SQLCM.ExecuteNonQuery()
            Cn.Close()
        Next
        Dim strdata(0 To 7) As String
        Dim Cnt As Long
        Cnt = 0
        Dim reader As New IO.StreamReader(Application.StartupPath & "\" & TextBox1.Text, System.Text.Encoding.GetEncoding("Shift-JIS"))
        Do While reader.Peek > -1
            Cnt = Cnt + 1
            strdata = Split(reader.ReadLine, ",")
            'データ新規追加処理
            Dim Row As DataRow
            Row = maintable.NewRow
            If strdata(0) <> "" Then
                Row("ID") = CLng(strdata(0))
            End If
            If strdata(1) <> "" Then
                Row("診療日") = CDate(Strings.Replace(strdata(1), ".", "/"))
            End If
            If strdata(2) <> "" Then
                Row("厚生省コード") = CLng(strdata(2))
            End If
            If strdata(3) <> "" Then
                Row("薬剤") = strdata(3)
            End If
            If strdata(4) <> "" Then
                Row("数量") = strdata(4)
            End If
            If strdata(5) <> "" Then
                Row("処方") = strdata(5)
            End If
            If strdata(6) <> "" Then
                Row("処方医師") = strdata(6)
            End If
            If strdata(7) <> "" Then
                Row("外入") = strdata(7)
            End If
            maintable.Rows.Add(Row)
        Loop
        reader.Close()
        Dim sql1 As String = ""
        For Each row1 As DataRow In maintable.Rows
            Select Case row1.RowState
                Case DataRowState.Added
                    sql1 = "INSERT INTO D_data VALUES ("
                    sql1 &= row1("ID") & ", "
                    sql1 &= "'" & row1("診療日") & "', " '
                    sql1 &= row1("厚生省コード") & ", " '
                    sql1 &= "'" & row1("薬剤") & "', " '
                    sql1 &= "'" & row1("数量") & "', "
                    sql1 &= "'" & row1("処方") & "', "
                    sql1 &= "'" & row1("処方医師") & "', "
                    sql1 &= "'" & row1("外入") & "' "
                    sql1 &= ")"
                Case Else
                    Continue For
            End Select
            '●更新実行
            SQLCM.CommandText = sql1
            Cn.Open()
            SQLCM.ExecuteNonQuery()
            Cn.Close()
        Next

        maintable.AcceptChanges()
        maintable.Dispose()
        adapter.Dispose()
        SQLCM.Dispose()
        Cn.Dispose()
        If Cnt > 0 Then
            MsgBox(Cnt & "件を登録しました", MsgBoxStyle.Information, "結果報告")
        End If

    End Sub
   

投稿者 shu   (社会人)   投稿日時 2011/5/31 21:14:58
> ただ、魔界の仮面弁士さんが例示していただいた処理では、ws..CommitTrans()が
> なくても反映することができ、私の書いた処理では明示的に追加しなければならないのか
> 理解できません。できましたらお教えください。
魔界の仮面弁士さんが書かれたコードには記述されてますが、わざわざなくして試され
登録を確認されたということですか?



2011/5/31 19:02:50の内容へ
>            SQLCM.CommandText = sql1
>             Cn.Open()
>             SQLCM.ExecuteNonQuery()
>             Cn.Close()
ループ毎に毎回、オープン、クローズは時間がかかると思います。
後、トランザクション付ければそれなりに速くなるかも。dao経由よりは遅いと思いますが。

投稿者 koutukasa   (社会人)   投稿日時 2011/5/31 21:46:42
魔界の仮面弁士さん、shuさん、すいません。もう一度見直してみたら、自分の処理に書き直したときに、時間経過の表示を省き、その時にに気づかずかってにws.CommitTrans()を削除していたようです。shuさんの指摘で色々調べていたら、おそらくここに必要だろうと追加してうまくいったので、魔界の仮面弁士さんのものにはもともとなくても処理できていたと思い込んでいました。
とんだ勘違いで申し訳ありません。

投稿者 魔界の仮面弁士   (社会人)   投稿日時 2011/5/31 22:02:22
コミット漏れという件に関しては、shu さんが解説されている通りです。

> 余計なことしていたため時間を費やしていたのでしょうか。

DataSet 経由の更新処理というのは、パフォーマンス面では不利なのです。

それにもともと、OLE DB という機構自体がデータ操作用の“汎用的”な代物ですから、
Jet データベースの操作に特化して設計された DAO と比べれば、効率は当然落ちてしまいます。

とはいえ、Microsoft.JET.OLEDB.4.0 プロバイダーであっても、設定さえきちんと施してやれば、
DAO のパフォーマンスにそこそこ近づけることはできます。しかし残念ながら、ADO.NET 自体が
それらの機能を完全に引き出せるような仕組みにはなっていないという実情があります。
(それらを利用したいなら、ADODB を使うか OLE DB API を呼び出すかしないといけません)


とはいえ何よりも、そもそもの更新のための仕組みの違いが大きいです。

DataAdapter というものは、DataTable の各行の状態が
 ・変更されていない行
 ・追加された行
 ・削除された行
 ・変更された行
のいずれかであるのかという情報に基づいて、プロバイダーに対して
INSERT / DELETE / UPDATE のコマンドを発行させるためのものです。

そして JET においては、SQL コマンドによるデータ操作は、テーブルタイプカーソルへの
単純追加操作に比べると、圧倒的に低速な操作となります。
一度のクエリーで一括挿入できるような場合は状況が変わりますが、今回のように
件数分の INSERT が実行される状況においては、処理効率はかなり劣る事になります。

さらに言えば、DataTable では「元データ(今回は追加なので0件)」と「変更後のデータ」を
全レコード分蓄えておかねばならないという点も不利となります。DAO によるカーソル操作なら
現在行1レコード分だけの情報(+キーセット情報)だけで更新できるわけですから、
処理データ数が多くなるほど、実行に必要なメモリ消費量も大きく差が開くことになります。

投稿者 koutukasa   (社会人)   投稿日時 2011/5/31 22:39:54
shuさん、魔界の仮面弁士さんありがとうございました。登録処理はDAOで処理することにより、飛躍的に速くなりました。登録だけでなく更新等の方法についてもすこし、やり方を考えてみることにします。