Need to speed up this query

[Guest]

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 of 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);

 

[Brian]

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 of 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);

Here's a couple of suggested improvements to the SQL, although I really
can't say whether they will improve performance:

qryMaxThruSSR:

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

qrySSRData:

SELECT *
FROM tblSSRData S LEFT JOIN tblTaskType T ON (S.TASK_TYPE = T.Code AND
T.incSW = True)
WHERE T.Code IS NOT NULL OR (S.TASK_TYPE = 'NETW' AND S.TEAMGROUP IS NOT
NULL);