Asger Blond
ab-mi at post3.tele.dk
Fri Jun 3 18:09:51 CDT 2011
That would be my guess too. IMO a RECORDSET in DAO or ADO is the equivalent to a CURSOR in SQL Server, and then I'll expect a WHERE in a plain SQL to be faster than a SEEK on a RECORDSET. As for Jim's excellent posting on indexing just this note on compound indexes (here I'm referring to plain SQL queries solely): It's only partly true that the fields in the search must be listed in the same order as used when the compound index was created - only partly, because this just holds for the query engine's ability to use the index for its *search* operation, not for its *fetch* operation. If you have a compound index on say field A, B and C (created in that order), and then makes a query selecting only these fields and using a where condition on field B or C or both, then the query engine can't use the index for a search, but it can use it for its fetch: It can find the wanted information by scanning the relatively small index instead of scanning the maybe huge table itself. In that case you have what's called a "covering index", and it's very fast. Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan Sendt: 3. juni 2011 23:31 Til: Access Developers discussion and problem solving Emne: 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: > > Seek beats anything hands down, bar none. But as you pointed out, it > requires an index. And the more fields you have to search on, the more > problematic that becomes both with concurrency issues and the fact > that JET/ACE allows only 32 indexes per table. > > Also, with a split DB setup, you need to open a connection to the BE > db (to > use seek, a recordset must be opened on a local table). That's not a > killer, but with a bound app, it does double the connections to the > BE. > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell > Burns Sent: Friday, June 03, 2011 11:49 AM To: 'Access Developers > discussion and problem solving' Subject: Re: [AccessD] Most efficient > means to retrieve most recent record > > I think you'll find searching against an index to be extremely fast. > Merely opening a recordset and setting an index takes no time, what > takes time is scanning it. That's why, when you set the descending > index, your scan begins at the most recent entry. Faster still would > be an index on all of your search criteria, but that would be > impractical if you don't always search on the same fields. Not sure > how fast the SQL query would be in comparison...probably negligible on > a small database. I'd be interested to hear from Stuart & Steve if > they've ever done a comparison. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William > Benson (VBACreations.Com) Sent: Friday, June 03, 2011 8:02 AM To: > 'Access Developers discussion and problem solving' Subject: Re: > [AccessD] Most efficient means to retrieve most recent record > > Oh OK ... Just realized SEEK has to be on the INDEX just created so > that is why you are not using it. > > I think this approach might be slower because it opens a recordset > based on an entire table (slower than creating a dynamic recordset, > right?) and then loops to find records meeting criteria rather than > returning only the relevant records from the initialization of the > recordset. > > So based on Stuarts and Steve's inputs, I think I lean toward > > SELECT TOP 1 * > FROM TBL_CONFIRM_IMPORT > WHERE NAME = 'BILL' AND OCCUPATION = 'ACCOUNTANT' > ORDER BY WHENRECORDIMPORTED DESC > > > -----Original Message----- > From: William Benson (VBACreations.Com) > [mailto:vbacreations at gmail.com] Sent: Friday, June 03, 2011 10:30 AM > To: Access Developers discussion and problem solving Subject: RE: > [AccessD] Most efficient means to retrieve most recent record > > OK, I think I understand this fulle ... and will be on the lookout for > some code which will create a descending index using DAO within VBA > since this is a code-created table. But what I don't understand now > is, suppose Bill's not been imported in the last 25 imports. > > > BTW, if we already know we want Bill the Accountant, why have you > written a recordset loop ... can't we use SEEK here as in your earlier > post? > > Thanks. > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell > Burns Sent: Friday, June 03, 2011 10:16 AM To: 'Access Developers > discussion and problem solving' Subject: Re: [AccessD] Most efficient > means to retrieve most recent record > > Hi Bill. By "timestamp" I was referring to your "WhenRecordImported" > field. I picked 5/10/2011 just as an example. If you put a descending > index on WhenRecordImported and set that index on the recordset, the > newest record will be the first record in the recordset. > > 1. Set an index called "ixWhenRecordImported" on the field > WhenRecordImported, descending 2. set rst = > db.openrecordset(TableName, dbOpenTable) ... note: the recordset type > must be dbOpenTable. it doesn't need to be explicitly stated. 3. > rst.index = " ixWhenRecordImported" The recordset will now be sorted > in descending order by date/time, so your first occurrence of Bill the > Accountant will be the most recent... 4. Do While not rst.eof > If rst!Name = "Bill" and rst!Occupation = "Accountant" then > <GoTo somewhere and do something> > End if > Rst.MoveNext > Loop > > If you want to find a specific date to begin your search, use the seek > method to move the recordset starting point to that date, then start > your loop. > > HTH, > Darrell > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William > Benson (VBACreations.Com) Sent: Friday, June 03, 2011 12:53 AM To: > 'Access Developers discussion and problem solving' Subject: Re: > [AccessD] Most efficient means to retrieve most recent record > > Hi Darrell, > > I am still trying to digest most of your reply, thanks, but in the > meantime Let me make sure we understand one another. > > My objective is to find the latest occurring record where (for > example) Name = "Bill" and Occupation = "Accountant"; Suppose I have 3 > records like this, timestamped > > Name Occupation WhenRecordImported > Bill Accountant 1/15/2011 3:52AM > Bill Accountant 1/17/2011 1:52AM > Bill Accountant 1/22/2011 2:52AM > > When you say index on WhenRecordImported and use SEEK, what is driving > your example date of 5/10/2011 - why are you picking that? I have no > knowledge of what timestamps are in the table when I employ this > device. > > Thanks. > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell > Burns Sent: Thursday, June 02, 2011 9:21 PM To: 'Access Developers > discussion and problem solving' Subject: Re: [AccessD] Most efficient > means to retrieve most recent record > > Hi William. > I think you'd get the fastest retrieval by putting an index on your > timestamp field. Let's call it "ixTimeStamp". Then after you set your > recordset, set the index as... 1. rst.index = "ixTimeStamp" This will > sort your recordset on the index. Then do a seek. For example, to > search the recordset for stuff that happened on 5/10/2011... 2. > rst.seek ">=", #5/10/2011# (I think that's the correct syntax for a > date value) Then start a loop on the recordset and exit the loop when > the date changes... 3. do while not rst.eof > If rst!TimeStamp > #5/10/2011# then > Exit do > > You may also want to consider splitting the date and time fields. I > usually convert my dates to a YYYYMMDD string format and index the > string field. For example... Rst.index = "ixStrDate" Rst.seek, "=", > "20110510" > > After you do a seek, you have to test for a NoMatch. For example... > > Rst.index = "ixStrDate" > Rst.seek "=", "20110602" > If NOT rst.nomatch then > Do While not rst.eof > If rst!strDate > "20110602" then > Exit do > End if > <your code> > Rst.movenext > Loop > End If > > Sorry bout dat. > Darrell > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William > Benson (VBACreations.Com) Sent: Thursday, June 02, 2011 5:12 PM To: > 'Access Developers discussion and problem solving' Subject: [AccessD] > Most efficient means to retrieve most recent record > > Hello, > > In an Access table where I import and append very similar data > routinely, I stamp every record with the same ImportTime at time of > import. > > I am wondering the most efficient means of getting at the latest > record matching certain criteria? I see three ways. > > > METHOD 1 > (1) Set a recordset = the entire table ordered descending, with no > WHERE > clause > (2) use Rst.FindFirst and put the criteria here as arguments > > > METHOD 2 > (1) Use an ORDER BY (desc) clause, select all the records > where those fields have the values I specify > (2) Use the first record in the recordset. > > METHOD 3 > (1) Select the max date where those fields have the values I > specify (2) Select "the" record where those fields have the values > I specify > AND > Date = Date found from (1) .... to me this seems unlikely to be > fastest, but who knows. > > -- > 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 > > -- > 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