Need to speed up this query !

[ZABADAK]

I need to speed up query qryMaxThru SSR (below), it uses the two other queries listed. This query extracts the latest record from a set of records with date ranges. The latest date range for each key (SSR/Task) needs to be extracted. This needs to run extremely fast as it is executed many times to generate a report. Right now it is laboriously slow. It seems fairly quick when I open the query in designer. Lots of the columns are indexed but which ones might be slowing it down?

qryMaxThruSSR:

SELECT qrySSRData.*
FROM qrySSRData, qryMaxThru
WHERE ([qrySSRData].[SSR_#]=[qryMaxThru].[SSR_#]) And ([qrySSRData].[Task_#]=[QryMaxThru].[Task_#]) And ([qryMaxThru].[Max_Thru_Date]=[qrySSRData].[REPORT_THRU_DATE]);

qryMaxThru:

SELECT [qrySSRData].[SSR_#] AS [SSR_#], [qrySSRData].[Task_#] AS [Task_#], Max([qrySSRData].[REPORT_THRU_DATE]) AS Max_Thru_Date
FROM qrySSRData
GROUP BY [qrySSRData].[SSR_#], [qrySSRData].[Task_#];

qrySSRData:

SELECT *
FROM tblSSRData
WHERE ( [TASK_TYPE] IN (SELECT Code from tblTaskType WHERE incSW = TRUE)) OR ([TASK_TYPE] = 'NETW' AND TEAMGROUP IS NOT NULL);

 

[lespaul]

why are you using a cartesian product instead of joining on the relative fields?

SELECT qrySSRData.*
FROM qrySSRData
INNER JOIN qryMaxThru ON ([qrySSRData].[SSR_#]=[qryMaxThru].[SSR_#]) And ([qrySSRData].[Task_#]=[QryMaxThru].[Task_#]) And ([qryMaxThru].[Max_Thru_Date]=[qrySSRData].[REPORT_THRU_DATE]);

And doesn't this return the same info in a single query?

SELECT *
FROM tblSSRData S

INNER JOIN 
(SELECT [SSR_#], [Task_#], Max([REPORT_THRU_DATE]) AS Max_Thru_Date FROM tblSSRData
GROUP BY [SSR_#], [Task_#]) As A 
ON (S.[SSR_#]=A.[SSR_#]) And (S.[Task_#]=A.[Task_#]) And (A.[Max_Thru_Date]=S.[REPORT_THRU_DATE])

WHERE ( [TASK_TYPE] IN (SELECT Code from tblTaskType WHERE incSW = TRUE)) OR ([TASK_TYPE] = 'NETW'  AND TEAMGROUP IS NOT NULL)

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell