very slow record inserts into Jet database using ADO.NET / OleDb

Question

I'm trying to insert a lot of records into a Jet (Access) database via ADO.NET / OleDb. It is running painfully slowly. The slowness is mainly due to the DbDataAdapter.Update method. In Microsoft's words,

...these statements are not performed as a batch process; each row is updated individually.

This is a real WTF as my database application gives about 30 times slower performance than the equivalent code in VB6 using old ADO or DAO (a Recordset with an AddNew / Update loop).

The SqlClient provider has the SqlBulkCopy class; is there anything equivalent for OleDb?

Even being able to change its write-caching behaviour might help. (ie. don't flush the cache after each row is inserted!)

Is there anything I can do, or is ADO.NET just broken for Jet?

*Edited to add: Here's a cut down version of my code, using a cut down test database.

First, the VBA/ADO version (Access 2003):

Dim con As ADODB.Connection
Set con = CurrentProject.Connection
con.Execute "DELETE * FROM tblTest", , adCmdText Or adExecuteNoRecords
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblTest", con, , adLockPessimistic
Dim i&, t!
t = Timer
For i = 1 To 10000
    rs.AddNew
    rs!mainKey = i
    rs!testColumn = Rnd * 100
    rs.Update
Next
rs.Close
Debug.Print "time to add 10000 (by ADO) " & Timer - t

Output: time to add 10000 (by ADO) 0.296875

Now the ADO.NET version (VB.NET 2010):

Dim sProvider As String = "PROVIDER = Microsoft.Jet.OLEDB.4.0;"
Dim sDataSource As String = "Data Source = 'C:\test.mdb'"
Dim connection As New OleDbConnection(sProvider & sDataSource)
connection.Open()
Dim q As New OleDbCommand("DELETE * FROM tblTest", connection)
q.ExecuteNonQuery()
Dim ds As New DataSet
Dim selectCmd As OleDbCommand = connection.CreateCommand()
selectCmd.CommandText = "SELECT * FROM tblTest"
Dim da As New OleDbDataAdapter(selectCmd)
da.Fill(ds, "tblTest")
Dim theTable As DataTable = ds.Tables("tblTest")
For i As Integer = 1 To 10000
    Dim row = theTable.NewRow()
    row.Item("mainKey") = i
    row.Item("testColumn") = Rnd() * 100
    theTable.Rows.Add(row)
Next i
Dim t! : t = Microsoft.VisualBasic.Timer
Dim cb As New OleDbCommandBuilder(da)
da.Update(ds, "tblTest")
Debug.Print("time to add 10000 (by ADO.NET): " & Microsoft.VisualBasic.Timer - t)
connection.Close()

Output: time to add 10000 (by ADO.NET): 5.859375

Answer 1

Make sure that the connection is open when the method is called. If the connection is closed before the update method is called (I actually saw that in some example code) the update method may attempt to open the connection in a non-optimal way. Opening connections in Jet can be very slow if the connection is not pooled. You may need to add OLE DB SERVICES = -1 to make sure the connection is pooled.

Answer 2

Slow MSAccess disk writing

Answer 3

Are you really trying to populate a table with random values? If so, there are faster ways to do it (using an INSERT based on an existing table, or on the table you're appending to, so you can run it multiple times and quickly reach the number of desired records).

In general, a SQL INSERT is going to be an order of magnitude faster than adding one record at a time. If you have to do it the way you're doing it, then you might look into whether or not you can use a Jet/ACE transaction via ADO/ADO.NET. I haven't a clue whether that's available or not. If it's not, and assuming COM is an option, you should consider just using DAO so you could use Jet/ACE transactions, which will delay the write to the very end (like posting a batch).

I'm no ADO maven, but I recally there being some batch functions in Classic ADO, too, so you might consider investigating that, as well.