Re: Any REAL reason to use ADO vs. DAO?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Olaf

I'd like you guys to give additonal information: i don't think that
you're using the optimal ADO options

I've run this test and I've _NEVER_ seen DAO faster than ADO

maybe you kids should stop runnig Windows 95



On May 4, 3:55 pm, "Schmidt" <s...@xxxxxxxxx> wrote:
"RobertMorley" <rmor...@xxxxxxxxxxxxxxxxxxxxxxxxx> schrieb im Newsbeitragnews:%23Oqun7ojHHA.4552@xxxxxxxxxxxxxxxxxxxxxxx

Don't know if there is a KB article. DAO Recordsets operate in
a connected state. That's the key
problem. Even with ADO, Recordsets can be marshaled
(properly) across processes in a *disconnected* state.

But what's to prevent you from passing a recordset in a connected
state...you just have to design your app not to close the recordset
prematurely. The fact that ADO supports disconnected recordsets
has little to do with marshalling a recordset object across processes.

If you pass a DAO-Object to another Process (e.g. an ActiveX-Exe),
and "perform" a 'Do Until Rs.EOF-Loop' there, every single Method-
and Property-Call has to be marshaled into the Creator-Process of
this Recordset - with huge Overhead.
A "ByVal-passed", disconnected ADO-Rs can be "enumerated"
inside the receiving process with much better performance.

DAO (using JET) works always bound (with something like a "server-
cursor"). With ADO/JET you can use both ways - a DAO-like
server-cursor or a client-cursor (to force the ADO-Rs, to allocate
Memory to store a Copy of all selected Records).
Just tested against the Invoices-View of NWind (wich is a Join):

ADO (adOpenKeyset, adLockOptimistic) = 19.1msec (adUseServer)
DAO (dbOpenDynaset, dbOptimistic) = 16.6msec
That's basically (only) the time to perform the Join, until the first
Record can be "reported" into the Recordset.
Here both, ADO and DAO, don't allocate much Memory (no Copy
of the Records is made) - these could be retrieved "on the fly".

For ADO we use the clientside Cursor now - adUseClient - this
allocates memory and stores a (serializable) copy of the Records
whilst performing the Select:
ADO (adOpenStatic, adLockBatchOptimistic) = 99.4msec
DAO (adOpenSnapshot) followed by: Rs.GetRows = 92.8msec
Now both approaches would be ready, to pass their "Select-Content"
(Byval) over Process- or Host-Boundaries.
With the Difference, that the Data from the "DAO-Select" comes
as a "naked" Variant-Array, the ADO-Rs-Data can be "rematerialized"
into a nice Obj-Model.
BTW, dhSQLite (wich always works in "Copy-Mode" with a clientside
Cursor) needs 28.9msec for the same task (Select * from Invoices).

Clunky, yes, but not necessarily bloated. If your ADODB recordset
takes 300k to store a recordset, and your DAO recordset takes 150k
for the same recordset (with the same caching options, etc.), then I'd
say it's the ADODB that's bloated, not DAO. These are made up figures, ...

Just tested this - the Calls with the "serverside-cursors" don't take up
much memory (since Data will be retrieved "live") - in the second scenario
both need ca. the same Memory (ADO ca. 3MB, DAO ca. 2.8MB).

So we can say, DAO is indeed faster, but not as much as one would
expect. From my tests some years ago, the difference was more
significant IIRC - maybe they have optimized the additional Layer
a bit in the meantime (inside the JET-OleDB-Driver or ADO 2.8).

Olaf


.

Relevant Pages

  • Re: filter with vb code
    ... I guess ADO classic doesn't interest them. ... There may be no recordset in your post be there certainly is a memory ... SQL involving an open workbook causes a memory leak. ...
    (microsoft.public.excel.programming)
  • Re: convert query into xml-file
    ... You can use both DAO and ADO in the same Access application. ... e.g. "Dim rst AS DAO.Recordset" or "Dim rst As ... ADODB.Recordset" rather than just "Dim rst As Recordset". ...
    (microsoft.public.access.queries)
  • Re: Datensatz in Recordset finden
    ... Dein Hinweis auf .FindFirst lässt nur vermuten, dass Du mit DAO arbeitest. ... > ich brauche ja mein Recordset Objekt so wie es ist. ... statt mit DAO mit ADO zu arbeiten. ... > Ansprechpartner. ...
    (microsoft.public.de.vb.datenbank)
  • Re: too few parameters error
    ... You're trying to use DAO code below. ... did you remove the reference to ADO at the same time? ... Recordset is an object in both the DAO and ADO models. ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.formscoding)
  • Re: Knowledge Base Help Not working
    ... Database is a DAO object. ... both Access 2000 and 2002 use ADO. ... Microsoft DAO 3.6 Object Library, ... For example, to ensure that you get a DAO recordset, you'll need to ...
    (microsoft.public.access.formscoding)