[AccessD] Most efficient means to retrieve most recent record

Asger Blond ab-mi at post3.tele.dk
Fri Jun 3 19:08:12 CDT 2011


William,
And help me too understand something ;-)
Isn't rst.index just creating a *temporary* index which would have to be recreated each time you execute your procedure? Frankly I don't know - but why not create an ordinary once-and-for-all index which could then be used by every subsequent queries whether on a recordset or in a plain sql?
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af William Benson (VBACreations.Com)
Sendt: 4. juni 2011 00:40
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Most efficient means to retrieve most recent record

Help me understand something please:  Wasn't the tenor of a previous post
that opening a table-type recordset is not really the same thing as trying
to hold the entire table in memory -- that it is not until one actually
scans the table that the database experiences a performance impact?

If I inferred badly, then nevermind my next question...

If what I inferred is correct, then here is a follow-up:  If the table-type
recordset has the index method implemented using Rst.Index "ixwhatever", and
one uses SEEK, how is that any slower than implementing the Select TOP 1
statement within the SQL at the time a recordset is opened? In the former
case, one is ultimately SEEKing on an indexed entity. In the latter, one is
executing an SQL statement on a (slow) data source.

I would follow THAT question up with another... is there a difference here
when we're talking one-offs versus repeated SEEKs. In other words, perhaps
implementing .INDEX is slow, but it will be more than made up for by the
fact that each SEEK statement will then be many times faster, whereas
repeated executions of a dynaset recordset against a non-indexed table will
ultimately fall behind in performance?

Thanks.


-----Original Message-----
From: Stuart McLachlan
Sent: Saturday, June 04, 2011 9:31 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Most efficient means to retrieve most recent record

I think you will find that SELECT TOP 1..... will use SEEK under the hood
and it has got to be faster just retrieving one record than retrieving a
potentially large recordset and then using SEEK on it in code.


--
Stuart
On 3 Jun 2011 at 15:05, Jim Dettman wrote:


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list