[AccessD] Most efficient means to retrieve most recent record

No matter which approach you use, a couple of things on indexing:

1. Keep in mind that indexes generally help, so the more criteria fields
that you can index, the better the performance will be.  That however also
means more overhead on inserting/updating records. 

2. If you using compound indexes (multiple fields) and you use the find or
seek methods, then fields must be listed in the same order as the index,
otherwise it will not be used (with seek it just doesn't work - where most
get caught on this is with find).  I've found that in general, your usually
better off with single field indexes unless the search is always the same.

3. You said the records have a lot of like data.  If a given column is more
then 80% the same (like a yes/no field where most of the values are no),
then an index is a bad idea.  Using the index takes longer then the query
just doing a table scan.

4. If you can mark an index unique, do so.  This gives the query optimizer
more options in performing join operations.

5. Try increasing the MaxBuffers setting.  If your going to run this on a
high memory machine, you probably want to set it to the max, which is 65535.
The default setting of 0 takes a machine's memory into account, but because
of a builtin cap in the algorithum, the max cache size it will allow is
64MB.   The 65536 setting will give you a 256MB cache.

  Add the line:

 dbEngine.SetOption dbMaxBufferSize, 65536

  To your DB at startup.

6. It you really want to dive deep, use JET SHOWPLAN and the ISAMStats
methods to see what the query processor is actually doing with the various
approaches.

HTH,
Jim.

-----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 04:02 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Most efficient means to retrieve most recent record

Great, thanks guys. I feel smarter now (or maybe dumber because it makes me
wonder about how many simple tricks I am missing) 



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, June 02, 2011 9:56 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Most efficient means to retrieve most recent record

That's probably the way I would do it too.

-- 
Stuart

On 3 Jun 2011 at 13:42, Steve Schapel wrote:

> William
> 
> Modification of your Method 2:
> 
> SELECT TOP 1 FROM YourTable
> WHERE Blabla
> ORDER BY YourDate DESC
> 
> Regards
> Steve
> 
> -----Original Message----- 
> From: William Benson (VBACreations.Com) 
> Sent: Friday, June 03, 2011 12: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