Is ADO much slower than DAO, or is there a fault in my code ?

I'm working on a CAD application that saves the drawing in an Access database.
Until now it was written in C++ and the CDao classes.
For example, saving a point was done this way :

m_pRstPoints->m_strFilter = "..." ;  // filter to select a given point by its tag (m_pRstPoints is a CDaoRecordset)
m_pRstPoints->m_X = ...

Of course updating a db always takes time, but generally performance was accepted as correct.

Now, for 'strategic reasons', my boss has decided to upgrade the databases in Access2000 format, and to use
ADO. In fact we don't program directly in ADO, for simplicity we use the CADOxxx classes written by Carlos Antollini

With ADO, performance is severely reduced ! Generally, reading or saving tables is now at least twice more slower than
before. The Microsoft components installed on my PC are Jet 4.0 Service Pack 3 and Data Access Components 3.5.
Note that i made tests with Access 97 and Access 2000 databases, but the format doesn't seem to have influence on performance
(perhaps Access 2000 databases are a bit slower).

So Is ADO intrinsically (so much) more slower than DAO, or is there a problem with my code ?
The database is open with mode adModeShareDenyWrite|adModeReadWrite.

The recordset is opened once before reading or saving a group of points :

m_pRstPoints->CursorType = adOpenDynamic; // tried adOpenStatic, no influence
m_pRstPoints->CursorLocation = adUseClient;
m_pRstPoints->Open("SELECT * FROM POINTS",
                                _variant_t((IDispatch*) m_pConnection, TRUE),

Reading a group of points :
while (! m_pRstPoints->IsEof())
  m_pRstPoints->GetFieldValue("X", X);

Saving a group of points :
while ( iterate list of points in memory )
  tag = point->tag;
  m_pRstPoints->FindFirst("TagPoint = %d") tag);
  m_pRstPoints->SetFieldValue("X", point->X);

FindFirst is very fast (there is an index on TagPoint), so i know that the real cause of slowness is in the update.
I got much better speed by calling BeginTransaction() before the iteration and CommitTransaction() at the end, so that the real
writing on the disk is postposed and done in one shot, but even so it's still more slower than the DAO code
(which is not accelerated by a transaction) !

In the same vein, i tried UpdateBatch() rather than Update(), but the transaction is faster.

I dig get a small improvement too by replacing the ADO functions (SetFieldValue & Update) by execution of a sql query
("UPDATE POINTS SET X = ... WHERE TagPoint = ...").

I can accept that more modern databases drivers, giving more possibilities, do carry a small penalty on performances, but
certainly not to the point that performance is halved or worse.
I presume there is a magic trick to recover speed, it's valued 500 points.

jpg  (sorry for my curious english, it's not my 1st langage)
Who is Participating?
DAO was written specifically for Jet and does in fact perform much better than ADO.

Microsoft always recommends that you use their latest technology whether it is faster/slower, whatever. In fact I am surprised you found any references to VB6 as they moved up to VB.NET a few years ago and they want you to use ADO.NET which is only shares the a similarity in name as the object model is entirely different from ADO.

DAO can still do things that cannot be done at all in ADO.
I've experienced exactly what you're describing when trying to use ADO with Access--it was incredibly slow compared with DAO.  I haven't found any documentation about why this might be, and ADO seems acceptably fast against a SQL database, but my practical response has been to use DAO whenever I'm in a "purely Access" environment.
JP_GobletAuthor Commented:
So this problem would be specific to Access ?
After all, Access and ADO are both written by Microsoft, so it's just normal that some incompatibilities occur ...
I've read somewhere that Microsoft recommends ADO for Visual Basic, and OLE DB for C++,
would it be a clue for an explanation ? In fact i don't even know what's OLE DB (i didn't search either).
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

When you are using Ado what connect string are you using?
If you use OLE DB method rather than using a direct connection then ADO will be a lot slower.
Here is something for you to try, create a database in Access 2000 with some test data
In another database create a blank form and then use ADO to fill in a few fields and see how much time it takes.
Now create a form in VB using ADO connecting to the same database and see how much time it takes. I think you will see a big change in the time between the 2 methods.
There are a few things you can do to help speed up your connection but as you mention you have found out it is not the writing of the data that make that much deference in speed but the method of getting the data to and from the database as well as the amount of data. What I mean by amount is that if the person who coded the program did not type the data being fed to the database then it will be sent as a var. type and the database will change the data type as needed on the fly. Out side of the fact that you are sending more data than needs to be sent, using this method, but the database has to change it as well, giving you a even larger speed loss.
JP_GobletAuthor Commented:
to stevbe :
effectively Microsoft push us (developers) to give up DAO and to use rather ADO; perhaps we have
to step back, i will talk about it with my boss.

to 'Excalibur_Software' :
the connect string is "Microsoft.Jet.OLEDB.4.0"; how can i get a direct connection ?

About the test (filling a form with ADO), i'm not sure to understand what you mean :
in one case i'm filling with ADO and C++ (as i do usually), in the other case i'm filling with ADO but in VB ?
What will this test compare in fact ?

Concerning the method for getting data, effectively all transfers by ADO methods are done via variants;
but i'm not sure that's the real source of slowness : i have made tests with a sql query ("UPDATE SET ...")
rather than ADO methods, and i gain some speed but mot much.

"effectively Microsoft push us (developers) to give up DAO and to use rather ADO"

and for all that M$ wants us to move into ADO/ADO.NET the default recordset object of a form in Access is still a DAO recordset ... so they can not even convince their own people to give up on DAO. Don't get me wrong ... most of my code is ADO, so I'm ding my part for th team but you asked about performance between ADO and DAO :-)

JP_GobletAuthor Commented:
Ater some googling i have found posts/articles that confirm that for Access databases the best is to stay with DAO.

(and others if you search for 'ado dao access performance' in Google)

Thanks all for your comments, but the conclusion is that there is no 'magic trick' in ADO to restore the speed
of DAO (still speaking specifically about Access databases). The real difficulty now for me is to persuade my boss
to stay with DAO ... he is so in love with Lord Bill and His promise for a glorious future (more and more performance)
if we follow the catechism ...

I consider the question is closed, i will ask for a refund of my points.

I stated specifically that DAO is faster.
JP_GobletAuthor Commented:
yes but it was just an opinion ...

at the end of my 1st post i wrote : 'I presume there is a magic trick to recover speed, it's valued 500 points.';
from my point of view i was giving the points for a practical solution in ADO;
however, if i agree to give points for 'opinions', then i will split the points between you and pique_tech
JP ... do what you need to do, I have no need for points. Perhaps I was not explicit enough when I said DAO was written for Jet and does perform better. There is no magic bullet to make ADO as fast as DAO. So what you are saying is that if if the answer is "you can't do that" then your not awarding points? Good luck with your project
JP_GobletAuthor Commented:
oh la la ... voilà, everyone gets its part (i splitted points between all the participants).

i can admit that Steve's comment, as well as the others participant's comments, are, sort of, an answer to my question; it's sometimes difficult to draw a line between an informative comment and a 'good' answer.

But more than that, i surely don't want to make some people on this site irritated at me simply beacause of a misunderstanding; after all its' not like it was my money.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.