Query Efficiency

J

Jahlu

To anyone that can offer some assistance,

I am looking to find a more efficient way of processing the following query...

SELECT EMP_NAMES_CHAS.[Associate Director], EMP_NAMES_CHAS.Supervisor,
EMP_NAMES_CHAS.Name, EMP_NAMES_CHAS.EMP_ID, nz((Select Count(*) from
[QTEAM_CHAS] where Year([MON_DATA_CREATE_DATE]) = [Year?] and
Month([MON_DATA_CREATE_DATE]) = [Month?] and [REP_ID] =
[EMP_NAMES_CHAS].[EMP_ID]),0) AS Monitors, nz((Select Sum([Exceptions]) from
[QTEAM_MON_EXCEPTIONS] where [EMP_ID] = [EMP_NAMES_CHAS].[EMP_ID] and [YEAR]
= [Year?] and [MONTH] = [Month?]),0) AS Exceptions,
Val([Monitors])+Val([Exceptions]) AS Total INTO QTEAM_CHAS_MONTHLY
FROM (((EMP_NAMES_CHAS INNER JOIN EMP_CHAS_CURRENT ON EMP_NAMES_CHAS.EMP_ID
= EMP_CHAS_CURRENT.EMP_ID) INNER JOIN EMP_CHAS_CURRENT AS EMP_CHAS_CURRENT_1
ON EMP_CHAS_CURRENT.SUPER_ID_LVL2_EMP_ID = EMP_CHAS_CURRENT_1.EMP_ID) LEFT
JOIN QTEAM_CHAS ON EMP_NAMES_CHAS.EMP_ID = QTEAM_CHAS.REP_ID) LEFT JOIN
QTEAM_MON_EXCEPTIONS ON EMP_NAMES_CHAS.EMP_ID = QTEAM_MON_EXCEPTIONS.EMP_ID
WHERE (((EMP_NAMES_CHAS.Supervisor)<>"Peyton, Tojuan"))
GROUP BY EMP_NAMES_CHAS.[Associate Director], EMP_NAMES_CHAS.Supervisor,
EMP_NAMES_CHAS.Name, EMP_NAMES_CHAS.EMP_ID, EMP_CHAS_CURRENT_1.JOB_CD
HAVING (((EMP_CHAS_CURRENT_1.JOB_CD)="V50604" Or
(EMP_CHAS_CURRENT_1.JOB_CD)="10181"))
ORDER BY EMP_NAMES_CHAS.Supervisor DESC;

If anyone has any suggestions, I would be greatly appreciative! Thanks in
advance.
 
J

John W. Vinson

To anyone that can offer some assistance,

I am looking to find a more efficient way of processing the following query...

SELECT EMP_NAMES_CHAS.[Associate Director], EMP_NAMES_CHAS.Supervisor,
EMP_NAMES_CHAS.Name, EMP_NAMES_CHAS.EMP_ID, nz((Select Count(*) from
[QTEAM_CHAS] where Year([MON_DATA_CREATE_DATE]) = [Year?] and
Month([MON_DATA_CREATE_DATE]) = [Month?] and [REP_ID] =
[EMP_NAMES_CHAS].[EMP_ID]),0) AS Monitors, nz((Select Sum([Exceptions]) from
[QTEAM_MON_EXCEPTIONS] where [EMP_ID] = [EMP_NAMES_CHAS].[EMP_ID] and [YEAR]
= [Year?] and [MONTH] = [Month?]),0) AS Exceptions,
Val([Monitors])+Val([Exceptions]) AS Total INTO QTEAM_CHAS_MONTHLY
FROM (((EMP_NAMES_CHAS INNER JOIN EMP_CHAS_CURRENT ON EMP_NAMES_CHAS.EMP_ID
= EMP_CHAS_CURRENT.EMP_ID) INNER JOIN EMP_CHAS_CURRENT AS EMP_CHAS_CURRENT_1
ON EMP_CHAS_CURRENT.SUPER_ID_LVL2_EMP_ID = EMP_CHAS_CURRENT_1.EMP_ID) LEFT
JOIN QTEAM_CHAS ON EMP_NAMES_CHAS.EMP_ID = QTEAM_CHAS.REP_ID) LEFT JOIN
QTEAM_MON_EXCEPTIONS ON EMP_NAMES_CHAS.EMP_ID = QTEAM_MON_EXCEPTIONS.EMP_ID
WHERE (((EMP_NAMES_CHAS.Supervisor)<>"Peyton, Tojuan"))
GROUP BY EMP_NAMES_CHAS.[Associate Director], EMP_NAMES_CHAS.Supervisor,
EMP_NAMES_CHAS.Name, EMP_NAMES_CHAS.EMP_ID, EMP_CHAS_CURRENT_1.JOB_CD
HAVING (((EMP_CHAS_CURRENT_1.JOB_CD)="V50604" Or
(EMP_CHAS_CURRENT_1.JOB_CD)="10181"))
ORDER BY EMP_NAMES_CHAS.Supervisor DESC;

If anyone has any suggestions, I would be greatly appreciative! Thanks in
advance.
Click to expand...

Care to describe the tables, the nature of the inefficiency, what the query is
trying to accomplish??

I can see several things - it's a TOTALS query but you're not actually
totalling anything except in the Subquery; the subquery itself will be a
source of inefficiency. You have the JOB_CD criteria in a HAVING clause which
is applied after all the calculations are done; they should be moved to the
WHERE clause like

AND JOB_CD IN("V50604", "10181")

Just the fact that you're doing a SELECT INTO is a problem right there - you
must face the overhead of selecting, sorting and totalling the records AND
putting them into the QTEAM_CHAS_MONTHLY table. If this is a new table, Access
must put all its fields and attributes into the systems tables, an expensive
operation. What can you do with QTEAM_CHAS_MONTHLY that you can't do with just
the SELECT query?
 
Top