Solved

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

Posted on 2004-11-02
471 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
Question by:JP_Goblet
    11 Comments
     
    LVL 12

    Assisted Solution

    by:pique_tech
    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
    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:
    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
     
    LVL 4

    Assisted Solution

    by:Excalibur_Software
    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
    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
    "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
    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
    I stated specifically that DAO is faster.
    0
     

    Author Comment

    by:JP_Goblet
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now