Jim Dettman
jimdettman at verizon.net
Fri Jun 3 17:20:17 CDT 2011
Stuart, SHOWPLAN will give the query costing plan of that. But keep in mind that to execute a query, it needs to get costed first, which takes time. Unless the plan is stored, which doesn't happen with a SQL statement in code. You'd be better to open a recordset against a saved query. Then again, if the table is dynamic, a saved plan is not always a good bet either. As far as the seek, you would not be retrieving any of the records unless you starting using move methods on it. The seek is against an index. Since JET uses B-Tree indexes, it only needs to go 4 - 6 levels deep (depends on the number of records in the table and the size of the key) and then scan the last index page to locate the record, then read the record itself. So your reading about seven or so pages to find a record. That's why it's extremely fast. ISAMStats would give you the low down on that. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Friday, June 03, 2011 05:31 PM 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: > > 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