Asger Blond
ab-mi at post3.tele.dk
Sat Jun 4 09:58:13 CDT 2011
No, the index property of a recordset invokes an existing index (which is physically persisted as index pages in the database). Have a look at this link: http://msdn.microsoft.com/en-us/library/ff194103.aspx Quote from the link: "The specified Index object must already be defined. If you set the Index property to an Index object that doesn't exist or if the Index property isn't set when you use the Seek method, a trappable error occurs." 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 15:54 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] Most efficient means to retrieve most recent record Woah ... I was totally in agreement that rst.index creates an index! It doesn't create the field, but it is on hundred percent creating an index - right then and there in memory - on that field of the recordset, the moment it is invoked ..... right? My point is just that there's no reason to get rid of it unless -- it slows down the database's other queries for some reason -- prevents new records from being entered into the table -- fails to persist even though intuition tells me it should (unless it goes out fo scope). The latter part... going out of scope... appears to me to be one of the more likely situations because my buggy code often creates runtime errors which throw all variables out of scope at some point. But to handle that, I would just declare a function as a recordset in a manner that the function looks for a recordset and if it is undefinied, re-opens it, and if it is already open, simply returns it. Rambling now... -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Saturday, June 04, 2011 5:16 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Most efficient means to retrieve most recent record Certainly not (c)! I was the dumb one suggesting that rst.index is creating a temporary index - in fact it is invoking an already created index. As to (a) and (b) I don't know - maybe some other? Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af William Benson Sendt: 4. juni 2011 05:28 Til: Access Developers discussion and problem solving Emne: 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