I am converting my database access module from DAO to ADO in my C++ program. The program is just for writing about one million records to a Access database. I got two problems in my development.

1. I found that the writing speed with ADO module is much slower than with DAO module. But people told me that ADO can work faster. Anybody has done the writing speed comparison between ADO and DAO module? There is any way I can increase the ADO writing speed?

2. There are two ways in ADO module for writing new records to a database:
    1) Using recordset->Fields->Item["field_name"].value to assign the value to each field directly.
    2) Use the data binding function to first bind a class to recordset.
    Anybody know which way can make the writing speed faster?

Any answer is appreciated.

Roger

DAO is the fastest method for working with an Access database. ADO adds a layer of abstraction that sacrifices speed and functionality in order to provide a generic interface to a broader variety of data sources. I would not expect data binding to add much overhead but it does add some.

If the records are in a format that can be linked, you would probably do best with an append query from the linked data. No matter what method you use, keep the connection object (ADO) or database object (DAO) open betweem writes. The next question is whether to open a recordset and keep it open, with .AddNew, .Update in a loop or running a series of connection or database executes in the loop. If you are using an mdb file of type A2k or newer, it is a good idea to turn off single record locking as single record locks force each record to use a 2k file page which may cause signficant file bloat (recoverable with a compact).

If there are complex indexes, it is more time consuming to add records while the indexes exist. It is much more efficient to add the records into a table without indexes and then add the indexes afterwards.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]