Using ADO in an mdb application

[Robert Morley]

That is not really true. Movelast fully populates the recordset, and

he has a snapshot, so each record in the recordset is fully populated.
There is no 'jumping' involved

Even in a snapshot situation, most of the data is stored internally to the
provider, and does not need to be set up in the client (ADO/DAO), so the
results are unlikely to be significantly different. When you access each
record, it marshals it from the provider to the client (unless it's a
client-side cursor, of course). THAT's where you'll begin to see
significant differences. As I remember it, there's a significant difference
in other operations such as client-side filtering and sorting, etc., as
well.

No doubt, ADO is more powerful though, and is therefore probably preferable
in most situations unless speed is absolutely essential.



Rob

 

[Guest]

record, it marshals it from the provider to the client (unless it's a

client-side cursor, of course). THAT's where you'll begin to see

ADODB defaults to server-side cursor for SQL Server, but I
think that ADOR only supports client-side cursor, and in any
case the Jet provider only provides a client-side cursor for the
SQL statement shown.

Since the Jet provider only provides a client-side cursor for the
SQL statement shown, the DAO example is a client-side cursor
also.

(david)

 

[Robert Morley]

Regardless of that, probably the best quick test is to simply use something
like GetRows/GetString to ensure that all rows are being hit. I prefer
something like this to blindly using MoveLast, and having to figure out
whether that's a sufficient test or not.



Rob

 

[Aaron Kempf]

and more importantly; you can rewrite ADO commands to work against ADOMD (or
just leaving it in ADO and changing the ole provider)

try doing THAT with DAO

 

[Aaron Kempf]

crosstab queries dont exist in SQL Server?

are you friggin kidding me?

a) PIVOT keyword
b) Analysis Services


stop spreading mis-information, kid

 

[Aaron Kempf]

CORRECTION!

ADO is not slower against MDB because MDB is not a database!

 

[Susie Johnson]

just to be clear

a) MDB is not a database, so of course ADO is faster
b) ADO is _SIMPLER_.. because I can do everything from one language, instead
of mixing DAO and VBA
c) ADO is the default
d) ADO is included with all versions of Windows
e) ADO is compatible with other languages (Analysis Services for example)
f) ADO is not a memory leak; you don't need to .CLOSE and SET rst = NOTHING
every 10 seconds
g) ADO supports CONNECTION SHARING- DAO does not

 

[Robert Morley]

"Susie Johnson", huh? Nice try Aaron. Bye bye.



Rob

 

[Susie Johnson]

please give some examples of DAO that do not include mixing SQL and VBA

I can do everything from a single library and you can't


add a field?

ALTER TABLE ADD COLUMN

create a query?
CREATE VIEW vME as Select * FROM SYSOBJECTS
CREATE PROCEDURE spME as Select * FROM SYSOBJECTS

 

[seroseles]

In this case, you're using "MoveLast", which jumps directly to the end of
the recordset. You're essentially measuring a single operation. Try
scrolling through every last record in the recordset (a more normal
operation in most circumstances), or performing an update to every recordin
the recordset, and you will likely see substantially greater differences.

You said the «try any test you want». I said, by my test, that you
should obtain the SQL engine to carry out work. To make a loop in
recordsets is incorrect for SQL, incorrect tool. It is my test. Why
movelast? To seek all the records. DAO seems sometimes fast because it
did not seek all records and you cannot obtain the recordcount. ADO
always this.

 

[Robert Morley]

DAO will always give you a correct recordcount after a MoveLast, but it
won't necessarily seek all the records (see the post where the person
monitored the back-end operations to discover that only indexes and viewed
records were retrieved, not the entire recordset).

As I've stated in several posts now, if you're using SQL Server or any other
external data source, that's a completely different scenario. When I said
that DAO was invariably faster than ADO, I meant it strictly for local Jet
databases, not for anything else. Poor wording on my part in the original
message.



Rob

In this case, you're using "MoveLast", which jumps directly to the end of
the recordset. You're essentially measuring a single operation. Try
scrolling through every last record in the recordset (a more normal
operation in most circumstances), or performing an update to every record
in
the recordset, and you will likely see substantially greater differences.

You said the «try any test you want». I said, by my test, that you
should obtain the SQL engine to carry out work. To make a loop in
recordsets is incorrect for SQL, incorrect tool. It is my test. Why
movelast? To seek all the records. DAO seems sometimes fast because it
did not seek all records and you cannot obtain the recordcount. ADO
always this.

 

[Fifth Amendment]

JET is not a database and it hasn't been for a decade

it is obsolete; I've quoted articles where MS states that JET has been
obsolete for a decade

move to SQL Server and Screw DAO

 

[seroseles]

DAO will always give you a correct recordcount after a MoveLast

You know my test then!

As I've stated in several posts now, if you're using SQL Server or any other
external data source, that's a completely different scenario.

I never mentioned the "SQL Sever", I speak of JET. SQL means the
language, not the product "SQL Sever".

 

[Robert Morley]

I misunderstood the message.

If you're speaking strictly of Jet data, then I suspect that the data
retrieval in DAO is even more optimized than it would be for remote data.
MoveLast may not be sufficient to retrieve all recordset data in many
situations, even if it's a snapshot.

That said, my original wording was perhaps not the greatest...there will
always be tests in which ADO and DAO will perform similarly or ADO will even
outdo DAO, but if you look at average speed comparing common operations in
both, overall, DAO will always come out a clear winner at the end of the
day, provided that the data is strictly local Jet data and not being
marshalled across processes, as discussed elsewhere.

At a million records, I had similar results to yours, with ADO actually
finishing a bit faster than DAO, indicating that perhaps with very large
recordsets, ADO manages its memory better or something of that nature. But
I would submit that 1,000,000 records in a single table is an unusual
situation for a Jet database. For smaller recordsets, my results differ
significantly. (Oh and off-topic, what on EARTH are you testing on that you
got speeds 10 times faster than mine? My computer is admittedly about 2
years old now, but it was a pretty decent machine at the time, and I
wouldn't have expected THAT much difference.)

In my own tests, I used QueryPerformanceCounter instead of GetTickCount,
simply to get more granularity for the smaller tests...GetTickCount reported
times of 0 for the 1-record tests. Not sure what you used for a table, but
my table consists of three fields, ID is an AutoNumber and is the Primary
Key, as well as two junk fields, Text1 and Text2, which are completely
ignored, both filled with 36 characters for each record (all lower-case
letters plus each digit 0-9). Before running each set of tests, I also
compacted the database, to ensure that indexing was optimal, etc. The code
itself was simply run straight out of the IDE. In my tests, I came out with
the following, averaged over 10 tests for each:


MoveLast in Snapshot (as per original test):

1 record: ADO = 222.8 ms; DAO = 81.5 ms
1000 records: ADO = 3600 ms; DAO = 2037 ms
1,000,000 records: ADO = 60 s; DAO = 65 s

Looping through all records in Snapshot and assigning rs!ID.Value to a long
variable:

1 record: ADO = 199.5 ms; DAO = 86.5 ms
1000 records: ADO = 12739 ms; DAO = 4350 ms
1,000,000 records: ADO = 196 s; DAO = 98 s

As you can see, the more real-world tests came out with the significant
speed differences I mentioned, as did most of the not-very-realistic tests.
A Jet database with a million records in a single table is unusual in
itself; opening that table as a snapshot just to move to the end of it and
grab a single field value is not particularly logical as a test. Why would
you EVER do that in a real-world scenario? Perhaps in my original
statement, I should have said something like "run whatever real-world tests
you want"?

As an aside, I tried moving the database from a slow IDE drive to a
high-speed striped SATA array, and there was virtually no difference in
these figures, often with changes of only one or two percent, both for DAO
and ADO. Apparently drive speed is not the major factor I would have
thought for Jet databases.



Rob

 

[seroseles]

my original wording was perhaps not the greatest...there will
always be tests in which ADO and DAO will perform similarly or ADO will even
outdo DAO, but if you look at average speed comparing common operations in
both, overall,DAO will always come out a clear winner at the end of the
day, provided that the data is strictly local Jet data and not being
marshalled across processes, as discussed elsewhere.

Thank you for your complete answer. Many provisory clauses - it is not
simple this one or the other, is it I say, select that which you
like. The troll like the other, good for her, eh?

 

[Robert Morley]

Thank you for your complete answer. Many provisory clauses - it is not

simple this one or the other, is it I say, select that which you
like.

Yes, there's a lot of provisory clauses in any discussion as to what's
better for what purposes. As I've said, ADO is my choice, simply because of
its universality and that I find it somewhat easier to use, but for what
it's good for, DAO will beat ADO hands down...but only for that, and not
much else.

The troll like the other, good for her, eh?

Not sure what you mean by that.


Rob

 

[aaron.kempf]

DAO is not relevent anywhere

jsut because we got a bunch of DAO dorks around here; that doesn't
make it a scientifically valid survey

go and ask if you should be using DAO in a real vb newsgroup! lol

 

[aaron.kempf]

I disagree
OLEDB is not a layer on top of ODBC

 

[aaron.kempf]

Facts Straight?

You used DAO against a MDB and yu think that it's faster?
Big FUCKING deal bro

compare DAO against MDB to ADO against SQL Server
and then when I get serious; I'll rewrite my connection string to
Analysis Services and you'll be stuck in the stone ages

 

[aaron.kempf]

and again; this is from the company that made Access 2000 and SQL 2000
NOT COMPATIBLE and then they sold us on a new version 6 months later

what a piece of shit company