William Benson (VBACreations.Com)
vbacreations at gmail.com
Sun Jun 5 21:14:53 CDT 2011
Awesome, thanks a lot... esp. for running that test!! I never thought of
that, will try to learn from your creativity Darrell.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell Burns
Sent: Sunday, June 05, 2011 5:03 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] [Spam]8.31 Re: Most efficient means to retrieve most
recent record
You're welcome, Bill.
The cloned recordset does not copy the 1st recordset's index.
Here's a quick test I put together on a table named "Product" which has a
Primary index on Prod_ID and a non-unique index on Prod_PartNum. In this
example I'm printing the first 10 rows from a rSet with the index set to
Part#, then creating a clone and printing the 1st 10 rows without setting
any index. As you can see, the clone reverts to the Primary index....
Private Sub TestClone()
Dim r As Recordset
Dim rClone As Recordset
Dim n As Integer
Const NumLimit As Integer = 10
SetClientDB 'this sets a database handle on the back-end Client database
Set r = dbClient.OpenRecordset("Product", dbOpenTable)
r.Index = "Prod_PartNum"
n = 0
'Print 1st 10 records of recordset indexed by Part#...
Debug.Print "ID Part# ... Index = Prod_PartNum"
Do While Not r.EOF
n = n + 1
Debug.Print r!Prod_ID & " " & r!Prod_PartNum
If n = NumLimit Then
Exit Do
End If
r.MoveNext
Loop
Set rClone = r.Clone
n = 0
rClone.MoveFirst
'Print 1st 10 records of cloned recordset with no index...
Debug.Print "ID Part# ... Cloned recordset"
Do While Not rClone.EOF
n = n + 1
Debug.Print rClone!Prod_ID & " " & rClone!Prod_PartNum
If n = NumLimit Then
Exit Do
End If
rClone.MoveNext
Loop
r.Close
rClone.Close
End Sub
Output:
ID Part# ... Index = Prod_PartNum
221
223
292 10 GB XFP SR
224 10G-SFPP-SR
225 10G-SFPP-SR
290 10G-XFP-SR
235 1P578714-001
236 228481-007
291 252663-D76
237 295633-B22
ID Part# ... Cloned recordset
204 507019-B21
207 507864-B21
209 517520-B21
211 595727-L21
212 595727-B21
213 500662-B21
214 512547-B21
215 462968-B21
216 339778-B21
217 447047-B21
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
Sent: Sunday, June 05, 2011 12:30 PM
To: Access Developers discussion and problem solving
Subject: [Spam]8.31 Re: [AccessD] Most efficient means to retrieve most
recent record
I have to reply to your HTH with definite YES. Thanks foe those insights.
One more question... on recordset cloning. Does cloning rst have to be done
before the first rst.index statement or does the recordsetclone property
instantiate a fresh non indexed and nonfiltered copy of the initial one? If
I had to guess with no knowledge I would have thought the index would
propagate... so that it would not be possible to index one on the primary
key then make its clone then index the clone on another field. Ah but you
say you do this by habit so I guess I am wrong and the clone is fresh...? I
would have tested this for myself if I knew how yo test a recordset (I.e.
the clone) to see whether or how it has been indexed.
Thanks a lot Darrell!
Bill Benson
Owner
VBACreations, LLC
On Jun 5, 2011 3:09 PM, "Darrell Burns" <dhb at flsi.com> wrote:
>
> 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
>
>
> --
> 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