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