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|adMod
The recordset is opened once before reading or saving a group of points :
m_pRstPoints->CursorType = adOpenDynamic; // tried adOpenStatic, no influence
on = adUseClient;
* FROM POINTS",
_variant_t((IDispatch*) m_pConnection, TRUE),
Reading a group of points :
while (! m_pRstPoints->IsEof())
Saving a group of points :
while ( iterate list of points in memory )
tag = point->tag;
agPoint = %d") tag);
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)