Hi All,

I have an SQL statemnent that is slowing down execution of reliant queries and views in my database. The query in question executes fine, less than 1 second to execute, but selecting a field in the query takes a long time. Same with trying to apply a sort order to the query.
A number of queries rely on this and they all inherit the effects of this and execution time for these dependant objects is very slow. I need to find an alternative to the SQL below, perhaps splitting the query. The query that sources this one is very quick and has none of the detrimental properties that this one has so the problem is not inherited from any objects above this one (qry_Item_Actions and above).

SELECT A.Action_Item_ID, A.ID, A.ActionDetail, A.Date
FROM qry_Item_Actions AS A
WHERE (((A.Date)=(SELECT MAX(Date) FROM qry_Item_Actions AS B Where A.Action_Item_ID = B.Action_Item_ID)));

Can anyone recommend an alternative method to get the most recent ID without using a nested query? I need get the ID where the date is the most recent, grouping by Action_Item_ID.

thanks,

Jon

I use a very similar query in one of my databases with no performance problems whatsoever.

Assuming that you're actually calling the date field [Date], the first thing I would try is changing the name of field in your tables. Date is a function in VBA and you may be causing Access to have to decide whether you mean the field or the VBA function.

If you choose not to rename the [Date] field, be sure that you bracket it ([]) everywhere it is used.

Another thing you might try is putting an index on the date field (allowing duplicates).

Last thing that won't necessarily improve performance is used the aggregate function DMax. e.g.

WHERE A.[Date] = DMax("[Date]","[qry_Item_Actions]","[Action_Item_ID] = " & A.[Action_Item_ID])

I've heard that aggregate functions are slower.

Also, I wonder if you really need the query in your subselect. It looks like the Action_Item_ID and [Date] field will be in the same table. (I'm making an assumption about your table design.) If so, why not just go right to the table that contains the Action_Item_ID and the [Date] field for your subselect?

And lastly, the query looks like you should just do a group by, but I don't know what [ID] and [ActionDetail] are. You could build a query that just gets the latest (Call it qry_Item_Actions_Latest):

Select [Action_Item_ID], Max([Date]) As MaxDate
From qry_Item_Actions
Group by [Action_Item_ID]

Then join:

Select A.[Action_Item_ID], [ID], [ActionDetail], B.[MaxDate]
From qry_Item_Actions as A Inner Join qry_Item_Actions_Latest As B On A.Action_Item_ID = B.Action_Item_ID

I wrote that SQL from scratch, so it may not be exact.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org