ADO vs DAO performance

ADO vs DAO performance

Post by Uladzimir Liashkevi » Wed, 25 Sep 2002 18:04:57


Can someone explain me why DAO performance on Access 97 database is
better than ADO performance on the same Access 2000 database.
I tested simple queries with or without conditional expressions, with
or without sorting, etc. The result was always the same - DAO executes
queries 1.5-5 times faster and fetches records sometimes 10x faster.
ADO recordset options are:
    adCmdText Or adAsyncFetch Or adAsyncFetchNonBlocking




1. ADO vs DAO performance

Hi all,
This topic is similar to Gene's from Wednesday on "Switch back to DAO?"

I've noticed a big increase in the time it takes to do relatively simple
tasks after switching from DAO to ADO.

I have an Access 2000 database with about 40  ADOX Catalog procedures
(action queries & parameter queries), 60 views (Select queries), and 110
tables (the 40 procedures + 50 actual tables).

The two simple tasks I refer to are changing the query text of a saved
query on the fly, and executing an action query.

With DAO, I did:
Set qdf = CurrentDb.Querydef("query1")
qdf.SQL = "Select..."

In terms of speed, changing the SQL property was instantaneous. The
action query may have taken a few seconds longer, depending on what had
to be done.

With ADO, I now do:
   Set cnn = New ADODB.Connection
   Set cat = New ADOX.Catalog
   cat.ActiveConnection = CurrentProject.Connection
   Set vw = cat.Views("query1")
   Set cmd = vw.Command
   cmd.CommandText ="Select..."
   Set cat.Views( = cmd

In my database, it takes about 20 seconds to get past this line: Set vw
= cat.Views("query1")
Similarly, for executing an action query, with Set proc =
cat.Procedures("actionquery1"), this too takes about 20 seconds to get
past this line.

In many instances, I have gone back to DAO. Has anyone else similar
experiences on this performance issue?
Geof Wyght.

2. Posting solutions?

3. ADO's Performance vs DAO's Performance

4. MD-Annapolis Junction-195588--Data Warehousing-ORACLE-Performance Tuning-SQL-PL/

5. performance of DAO vs ADO

6. ctid & updates

7. DAO vs ADO Performance

8. server side cursor error

9. ADO: Performance Testing vs. DAO

10. Performance ADO Vs DAO

11. ADO vs RDO vs DAO?

12. ADO vs DAO vs RDO

13. VB5 vs VB6, DAO vs ADO?