Steve Schapel
steve at datamanagementsolutions.biz
Fri Jun 3 15:37:27 CDT 2011
Great information, Jim. Thanks. Regards Steve -----Original Message----- From: Jim Dettman Sent: Saturday, June 04, 2011 1:25 AM To: 'Access Developers discussion and problem solving' Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com