MS Access Batch Update via ADO.Net and COM Compatibility

Question

This is a continuation of this next topic . This is all with .Net 2.0 ; For me at least.

Essentially, Marc (OP on top) tried several different approaches to updating the MS Access table with 100,000 records and found that using a DAO connection was about 10 to 30 times faster than using ADO.Net. I went almost the same way (examples below) and came to the same conclusion.

I think I'm just trying to understand why OleDB and ODBC are so slower, and I would like to hear if anyone found a better answer than the DAO from this post in 2011. I would really prefer to avoid DAO and / or Automation, as they will require the client machine to either distribute Access or the database engine (or I'm stuck with DAO 3.6, which does not support .ACCDB).

Initial 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 it was really strange, so I tried several options for the same:

• Disabling OleDB for ODBC
• Quoting from a data table and executing an INSERT statement for each row
  • This is what .Update is anyway
• Using ACE Provider Instead of Jet (ODBC and OleDB)
• Starting a data adapter update from a DataReader.Read cycle
  • Due to disappointment; It was fun.

Finally, I tried using DAO. The code should basically do the same; besides this, obviously not, because it works after ~ 10 seconds.

  Dim dbEngine As New DAO.DBEngine Dim accessDB As DAO.Database = dbEngine.OpenDatabase(accessPath) Dim accessTable As DAO.Recordset = accessDB.OpenRecordset(tableName) While _Reader.Read accessTable.AddNew() For i = 0 To _Reader.FieldCount - 1 accessTable.Fields(i).Value = _Reader.Item(i).ToString Next accessTable.Update() End While 

A few other notes:

• Everything is converted to Strings in all examples to try to keep everything as simple and consistent as possible.
  • Exception: in my first example, using the Table.Load function, I’m not because ... well, I really can’t, but I did basically the same thing when I looped the reader and created the insert commands (this is what he does, anyway). It did not help.
• For each field ... Next to field (i) against field (name) there was no value for me
• Every test I ran started with an empty, pre-built data table in a recently compressed Access database
• Loading data into a data table in memory takes ~ 3 seconds
• I don’t think this is a problem with data marshaling, because a Marc message showed that downloading a text file through Automation is as fast as a DAO - if anything, it should not marshal data when using ODBC / OleDB, but when using Automation
• All this bothers me more than necessary, because it makes no sense

Hope someone can shed some light on this ... it's just weird. Thanks in advance!

Answer 1

The reason here is that the DAO driver is much closer to the MS Access database engine than the ODBC driver.

The DAO methods AddNew and Update directly delegate the equivalents of MS Access, in no case generate SQL, so SQL should not be parsed into MS Access.

On the other hand, the DataAdapter code generates an Update statement for each row, this update statement is passed to ODBC, which then passes this to the MSAccess driver, which either

• independently parses SQL and issues AddNew and Update commands to the Access database or
• passes SQL to MS Access, which is not optimized for parsing SQL, and which, after parsing, completes the translation of SQL into AddNew and Update .

anyway, your time is taken by generating SQL, and then something interprets that SQL, where the DAO approach bypasses SQL generation / interpretation and goes straight into the metal.

One way is to create your own “database service” running on a machine with db access. It organizes your selections and updates and can communicate with the client through remote access, WCF (http or any other). This is a lot of work and significantly changes the logic of the application.

Displaying the correct name for the database driver (e.g. Jet or something else) is an exercise left to the reader

Answer 2

I know this question is old, but the answer can help someone else deal with it.

There is another way to consider. Since both the source and destination connection strings are known, the source tables can be linked to the Access target database, possibly by some sort of connection string parsing, via DAO or ADOX (I know ADOX is off topic).
Then the data in the so-called tables can be transferred quite quickly, writing out such statements regarding the DAO or OleDb for the target Access database:

 SELECT * INTO Table1 FROM _LINKED_Table1 

Some disadvantages (indicate what I missed):

• source table must contain primary key
• primary keys and indexes must be recreated by examining the original index scheme
• it is not easy to get the status of the progress of the transfer during the execution of the request

Some advantages (indicate what I missed):

• only to check the schema of the source tables if all user tables should be copied
• no need to check the original column schema to generate column definitions for CREATE TABLE statements
  (for example, try to get reliable information about AUTONUMBER / IDENTITY from the OleDb scheme, that is, without assumptions about combinations of column values ​​and flag bits based on the study of other schemes).
• you don’t need to generate huge amounts of INSERT INTO ... VALUES ... statements that take into account the AUTONUMBER / IDENTITY columns in your code, or otherwise start a database operation for each line of your code.
• ability to specify filtering criteria for transferred records
• no need to worry about columns of text, date or time, or how to delimit, avoid, or format your values ​​in queries, unless they are used in query criteria

This method was used in the production project and turned out to be the fastest for me, at least for me .: O)