Help optimizing an ugly query

[kman42]

Hi all,
Let me preface this by saying I don't know what I'm doing. This is part of my first Access database and it evolved by trial and error over time, so it is extremely ugly. Therefore, I beg your patience and tolerance for a newbie.



Anyway, I am running this query as part of a report. It connects to linked tables over a network and it is dog slow. I know it isn't pretty, but if there is anything obvious that you could spot that could point me in the right direction for optimizing, I would appreciate it.

Thanks,
kman

SELECT [%$##@_Alias].InitiativeID, [%$##@_Alias].EventLocation, [%$##@_Alias].fk_EventID, [%$##@_Alias].EventPhase, [%$##@_Alias].DateOfEvent, [%$##@_Alias].ElapsedDays, DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=1 AND fk_InitiativeID=" & [InitiativeID]) AS DateOfPublication
FROM tbl_Initiatives INNER JOIN (SELECT fk_EventID, DateOfEvent, IIF(DateOfEvent<IIF(Nz(DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=4 AND fk_InitiativeID=" & fk_InitiativeID),"")<>"",DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=4 AND fk_InitiativeID=" & fk_InitiativeID),DLookup("Submit_To_DERA_Proposed","tbl_Initiatives","pk_InitiativeID=" & fk_InitiativeID)),"Phase I",IIF(DateOfEvent>=Nz(DLookUp("DateOfEvent","tbl_EventLog","fk_EventID=3 AND fk_InitiativeID=" & fk_InitiativeID),Now()),"Phase III","Phase II")) AS EventPhase, DLookUp('Location','tbl_Events','pk_EventID=' & tbl_EventLog.fk_EventID) AS EventLocation, (SELECT TOP 1 Dupe.DateOfEvent FROM tbl_EventLog AS Dupe WHERE (((Dupe.DateOfEvent)>[tbl_EventLog].[DateOfEvent]) AND (Nz(DLookup('Location','tbl_Events','pk_EventID=' & Dupe.fk_EventID),"")<>"" OR fk_EventID=1) AND ((Dupe.fk_InitiativeID)=[tbl_EventLog].[fk_InitiativeID])) ORDER BY Dupe.DateOfEvent, Dupe.pk_EventLogID) AS NextDate, IIf(Nz([NextDate],"")<>"",[NextDate]-[DateOfEvent],Now()-[DateOfEvent]) AS ElapsedDays, tbl_EventLog.fk_InitiativeID AS InitiativeID FROM tbl_EventLog WHERE (((Nz(DLookUp('Location','tbl_Events','pk_EventID=' & tbl_EventLog.fk_EventID),''))<>'')) And tbl_EventLog.fk_EventID<>1) AS [%$##@_Alias] ON tbl_Initiatives.pk_InitiativeID=[%$##@_Alias].InitiativeID
ORDER BY [%$##@_Alias].InitiativeID, [%$##@_Alias].DateOfEvent;

[rpeare]

Using dlookup functions in queries is extremely inefficient, especially with larger datasets, that is likely what is slowing down your resposne time. Every time a new line in your query is processed your query has to reference EVERY RECORD in your database, and you have at least 5 dlookups in the query. I think I can safely say that's your problem.

[kman42]

Thanks! I'll try to move that functionality to subqueries or VBA and see if it improves. I see some obvious subqueries right off the bat now that you alerted me to the problem.