[AccessD] Most efficient means to retrieve most recent record

Jim Dettman jimdettman at verizon.net
Fri Jun 3 14:05:07 CDT 2011 

 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

More information about the AccessD mailing list