Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-11-02
12
Medium Priority
?
495 Views
Last Modified: 2012-05-05
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->Requery();
m_pRstPoints->Edit();
m_pRstPoints->m_X = ...
...
m_pRstPoints->Update();

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
(http://www.codeproject.com/database/caaadoclass1.asp).

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),
                       adOpenDynamic,
                                 adLockOptimistic,
                       adCmdText);

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

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

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)
0
Comment
Question by:JP_Goblet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 12

Assisted Solution

by:pique_tech
pique_tech earned 600 total points
ID: 12473556
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.
0
 

Author Comment

by:JP_Goblet
ID: 12473832
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).
0
 
LVL 39

Accepted Solution

by:
stevbe earned 600 total points
ID: 12474861
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Assisted Solution

by:Excalibur_Software
Excalibur_Software earned 300 total points
ID: 12474940
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.
0
 

Author Comment

by:JP_Goblet
ID: 12480894
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.

jpg
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12481043
"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 :-)

Steve
0
 

Author Comment

by:JP_Goblet
ID: 12491133
Ater some googling i have found posts/articles that confirm that for Access databases the best is to stay with DAO.

http://www.dbforums.com/archive/index.php/t-733387.html
http://www.dbforums.com/archive/index.php/t-733378.html
http://www.motobit.com/tips/detpg_Perfdata.htm
http://www.codecomments.com/Visual_Basic/message306111.html
http://accessadvisor.net/doc/05515

(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.

jpg
 
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12505058
I stated specifically that DAO is faster.
0
 

Author Comment

by:JP_Goblet
ID: 12505255
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
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12512594
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
0
 

Author Comment

by:JP_Goblet
ID: 12512753
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.

jp
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question