[AccessD] Most efficient means to retrieve most recent record

Darrell Burns dhb at flsi.com
Sun Jun 5 14:08:35 CDT 2011


Bill,
The answer is yes, you can open a global recordset and have it persist from
one procedure to another until the cows come home (or until you encounter an
unhandled exception, or change it by accident, etc). One major danger in
doing this is if you have multiple procedures working on it simultaneously
and you don't know what you're doing. Another danger is that you close the
recordset in one proc and the other one is expecting it to be open. The
better approach is to keep the recordset definitions local. 
Assuming we're talking about DAO table-type recordsets, opening a recordset
does not mean that you're reading or copying the records, so don't worry
about the "overhead". You can open multiple recordsets on the same table at
the same time, and each acts independently. I do this frequently to have one
recordset indexed on the PrimaryKey and another indexed on some search field
against the same table. The fastest way to do this is to clone your
recordset. The clone can be manipulated independently and you can sync the
two using bookmarks.
There's no concern about locking conflicts if all your manipulation is
read-only. If one is updating, then the second may be delayed while the
first does its write.
HTH,
Darrell

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
Sent: Friday, June 03, 2011 8:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Most efficient means to retrieve most recent record

Hey... I am new to this. What I wonder is if in a procedure where I was
setting a recordset equal to a whole table then maybe that rst can be
declared publicly in which case that index lasts pretty much my whole
session once it is called, unless it is ret to nothing?  Or would that (a)
lock up my table so it couldn't be used or ( b) slow down my database or (c)
be too dumb for you to even comment on? ;)

Bill Benson
Ownet
VBACreations, LLC
On Jun 3, 2011 8:09 PM, "Asger Blond" <ab-mi at post3.tele.dk> wrote:
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
-- 
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