[AccessD] Most efficient means to retrieve most recent record

Hi Bill --

Returning to your original question: "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.
..." 

What (total) execution time difference are you trying to gain using this or
that method?
Is that time difference equals to something like 0.01 seconds?
I mean nowadays computers are so speedy that spending hours in development
trying to gain 100 ms speed difference could be considered as a waste of
time.

In your certain case when you're stamping every record with the same
timestamp during *records by record(?) import* I'd better reengineered your
import procedure by making import in batches, and of course once retrieved
I'd keep (if that is applicable for your import procedure logic) ImportTime
value in memory...

Or did you try to consider using UPDATE SQL to set ImportTime for all
imported records by using just one SQL statement?
When imported into DB could a record "live" there for some time without
ImportTime value till that value will be set for all newly imported records
using just one UPDATE sql statement?

Just wanted to note that discussion about speed difference of "select top 1
.... desc" and ".seek" and whatever else method look a bit theoretical for
your certain case - and batch import would give you significant execution
speed gain...

I can be wrong if your import procedure logic requires to execute "select
top 1 ... desc" or ".seek" for every record to be imported...

Thank you.

--
Shamil

P.S. My vote if for:

<<<
SELECT TOP 1 *
FROM TBL_CONFIRM_IMPORT
WHERE NAME = 'BILL' AND OCCUPATION = 'ACCOUNTANT'
ORDER BY WHENRECORDIMPORTED DESC
>>>
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
Sent: 4 ???? 2011 ?. 7:28
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:

<< snip >>>