This is kind of a follow-up to this thread. This is all with .Net 2.0; for me, at least.
Essentially, Marc (OP from above) tried several different approaches to update an MS Access table with 100,000 records and found that using a DAO connection was roughly 10 - 30x faster than using ADO.Net. I went down virtually the same path (examples below) and came to the same conclusion.
I guess I'm just trying to understand why OleDB and ODBC are so much slower and I'd love to hear if anyone has found a better answer than DAO since that post in 2011. I would really prefer to avoid DAO and/or Automation, since they're going to require the client machine to either have Access or the database engine redistributable (or I'm stuck with DAO 3.6 which doesn't support .ACCDB).
Original attempt; ~100 seconds for 100,000 records/10 columns:
Dim accessDB As New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
accessPath & ";Persist Security Info=True;")
accessDB.Open()
Dim accessCommand As OleDb.OleDbCommand = accessDB.CreateCommand
Dim accessDataAdapter As New OleDb.OleDbDataAdapter( _
"SELECT * FROM " & tableName, accessDB)
Dim accessCommandBuilder As New OleDb.OleDbCommandBuilder(accessDataAdapter)
Dim accessDataTable As New DataTable
accessDataTable.Load(_Reader, System.Data.LoadOption.Upsert)
//This command is what takes 99% of the runtime; loops through each row and runs
//the update command that is built by the command builder. The problem seems to
//be that you can't change the UpdateBatchSize property with MS Access
accessDataAdapter.Update(accessDataTable)
Anyway, I thought this was really odd so I tried se