Really slow query

  • Thread starter Thread starter NeoFax
  • Start date Start date
N

NeoFax

I have a update query that takes like 20 minutes to run and I would
like to know if there is a better way to achieve the same results but
faster as it is only going to get slower as the year progresses with
more data. Here is the SQL for my query:
INSERT INTO tblSAPvsMECAPPHrs ( HELO, STATION, SAP_Hrs, MECAPP_Hrs,
MECAPP_HrsWkd, SAP_Delta, MECAPP_Delta, MECAPP_WkdDelta, trackDate )
SELECT qrySAPvsMECAPPHrs.HELO, qrySAPvsMECAPPHrs.STATION, FormatNumber
(nz([SAP_Hrs],0),2) AS SAP_Hours, nz([MECAPP_Hrs],0) AS MECAPP_Hours,
nz([MECAPP_HrsWkd],0) AS MECAPP_HoursWkd, (nz([SAP_Hrs],0))-nz(DSum
("[SAP_Hrs]","tblSAPvsMECAPPHrs","[HELO]=" & [HELO] & " and [STATION]
='" & [STATION] & "' and [trackDate]=#" & DMax
("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS SAP_Delta, (nz
([MECAPP_Hrs],0))-nz(DSum("[MECAPP_Hrs]","tblSAPvsMECAPPHrs","[HELO]="
& [HELO] & " and [STATION]='" & [STATION] & "' and [trackDate]=#" &
DMax("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS MECAPP_Delta, (nz
([MECAPP_HrsWkd],0))-nz(DSum
("[MECAPP_HrsWkd]","tblSAPvsMECAPPHrs","[HELO]=" & [HELO] & " and
[STATION]='" & [STATION] & "' and [trackDate]=#" & DMax
("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS MECAPP_WkdDelta,
tblStatics.runDate AS trackDate
FROM qrySAPvsMECAPPHrs, tblStatics;

Thank you for any help!
 
Concentrate on getting a SELECT query that runs quickly. Your three
complex expressions using both a DSum and DMax are probably the major
bottleneck. That means you are running the equivalent of 6 queries for
each row of data you want to add. That is probably a LOT of queries.

You might be able to solve that with a query that looks like the
following using two subqueries - one nested inside the other - in the
FROM clause of the SELECT query. Note that the calculation to get the
maximum TrackDate runs ONE time in total for the entire query instead of
three times for each row

Then that value is used in a query that calculates all three sums in one
pass, instead of running three DSUM functions (equates to 3 queries) for
each row. There may be additional things you could do in
qrySAPvsMECAPPHrs to make things faster.

SELECT qrySAPvsMECAPPHrs.HELO
, qrySAPvsMECAPPHrs.STATION
, FormatNumber(nz([SAP_Hrs],0),2) AS SAP_Hours
, nz([MECAPP_Hrs],0) AS MECAPP_Hours
, nz([MECAPP_HrsWkd],0) AS MECAPP_HoursWkd

, nz([SAP_Hrs],0)-nz(TtlSAP,0) AS SAP_Delta

, nz([MECAPP_Hrs],0) -nz(TtlMECAPPhrs,0) AS MECAPP_Delta

, nz([MECAPP_HrsWkd],0)-nz(TtlHrsWorked,0) AS MECAPP_WkdDelta

, tblStatics.runDate AS trackDate
FROM tblStatics, qrySAPvsMECAPPHrs LEFT JOIN
(
SELECT HELO, STATION
, Sum(SAP_Hrs) as TtlSAP
, Sum(MeCapp_Hrs) as TtlMECAPPhrs
, Sum(MECAPP_hrsWorked) as TtlHrsWorked
FROM tblSAPvsMECAPPHrs
WHERE TrackDate = (SELECT Max(TrackDate)
FROM tblSAPvsMECAPPHrs)
GROUP BY HELO, STATION
) as XX
ON qrySAPvsMECAPPHrs.Helo = XX.HELO
AND qrySAPvsMECAPPHrs.Station = XX.Station

Also make sure you have indexes on the TrackDate, Helo, and Station
fields to ensure best performance of this query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a update query that takes like 20 minutes to run and I would
like to know if there is a better way to achieve the same results but
faster as it is only going to get slower as the year progresses with
more data. Here is the SQL for my query:
INSERT INTO tblSAPvsMECAPPHrs ( HELO, STATION, SAP_Hrs, MECAPP_Hrs,
MECAPP_HrsWkd, SAP_Delta, MECAPP_Delta, MECAPP_WkdDelta, trackDate )
SELECT qrySAPvsMECAPPHrs.HELO, qrySAPvsMECAPPHrs.STATION, FormatNumber
(nz([SAP_Hrs],0),2) AS SAP_Hours, nz([MECAPP_Hrs],0) AS MECAPP_Hours,
nz([MECAPP_HrsWkd],0) AS MECAPP_HoursWkd, (nz([SAP_Hrs],0))-nz(DSum
("[SAP_Hrs]","tblSAPvsMECAPPHrs","[HELO]=" & [HELO] & " and [STATION]
='" & [STATION] & "' and [trackDate]=#" & DMax
("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS SAP_Delta, (nz
([MECAPP_Hrs],0))-nz(DSum("[MECAPP_Hrs]","tblSAPvsMECAPPHrs","[HELO]="
& [HELO] & " and [STATION]='" & [STATION] & "' and [trackDate]=#" &
DMax("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS MECAPP_Delta, (nz
([MECAPP_HrsWkd],0))-nz(DSum
("[MECAPP_HrsWkd]","tblSAPvsMECAPPHrs","[HELO]=" & [HELO] & " and
[STATION]='" & [STATION] & "' and [trackDate]=#" & DMax
("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS MECAPP_WkdDelta,
tblStatics.runDate AS trackDate
FROM qrySAPvsMECAPPHrs, tblStatics;

Thank you for any help!
Click to expand...
 
Concentrate on getting a SELECT query that runs quickly.  Your three
complex expressions using both a DSum and DMax are probably the major
bottleneck.  That means you are running the equivalent of 6 queries for
each row of data you want to add.  That is probably a LOT of queries.

You might be able to solve that with a query that looks like the
following using two subqueries - one nested inside the other - in the
FROM clause of the SELECT query.  Note that the calculation to get the
maximum TrackDate runs ONE time in total for the entire query instead of
three times for each row

Then that value is used in a query that calculates all three sums in one
pass, instead of running three DSUM functions (equates to 3 queries) for
each row.   There may be additional things you could do in
qrySAPvsMECAPPHrs to make things faster.

SELECT qrySAPvsMECAPPHrs.HELO
, qrySAPvsMECAPPHrs.STATION
, FormatNumber(nz([SAP_Hrs],0),2) AS SAP_Hours
, nz([MECAPP_Hrs],0) AS MECAPP_Hours
, nz([MECAPP_HrsWkd],0) AS MECAPP_HoursWkd

, nz([SAP_Hrs],0)-nz(TtlSAP,0) AS SAP_Delta

, nz([MECAPP_Hrs],0) -nz(TtlMECAPPhrs,0) AS MECAPP_Delta

, nz([MECAPP_HrsWkd],0)-nz(TtlHrsWorked,0) AS MECAPP_WkdDelta

, tblStatics.runDate AS trackDate
FROM tblStatics,  qrySAPvsMECAPPHrs LEFT JOIN
(
   SELECT HELO, STATION
   , Sum(SAP_Hrs) as TtlSAP
   , Sum(MeCapp_Hrs) as TtlMECAPPhrs
   , Sum(MECAPP_hrsWorked) as TtlHrsWorked
   FROM tblSAPvsMECAPPHrs
   WHERE TrackDate = (SELECT Max(TrackDate)
   FROM tblSAPvsMECAPPHrs)
   GROUP BY HELO, STATION
) as XX
ON qrySAPvsMECAPPHrs.Helo = XX.HELO
AND qrySAPvsMECAPPHrs.Station = XX.Station

Also make sure you have indexes on the TrackDate, Helo, and Station
fields to ensure best performance of this query.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================


I have a update query that takes like 20 minutes to run and I would
like to know if there is a better way to achieve the same results but
faster as it is only going to get slower as the year progresses with
more data.  Here is the SQL for my query:
INSERT INTO tblSAPvsMECAPPHrs ( HELO, STATION, SAP_Hrs, MECAPP_Hrs,
MECAPP_HrsWkd, SAP_Delta, MECAPP_Delta, MECAPP_WkdDelta, trackDate )
SELECT qrySAPvsMECAPPHrs.HELO, qrySAPvsMECAPPHrs.STATION, FormatNumber
(nz([SAP_Hrs],0),2) AS SAP_Hours, nz([MECAPP_Hrs],0) AS MECAPP_Hours,
nz([MECAPP_HrsWkd],0) AS MECAPP_HoursWkd, (nz([SAP_Hrs],0))-nz(DSum
("[SAP_Hrs]","tblSAPvsMECAPPHrs","[HELO]=" & [HELO] & " and [STATION]
='" & [STATION] & "' and [trackDate]=#" & DMax
("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS SAP_Delta, (nz
([MECAPP_Hrs],0))-nz(DSum("[MECAPP_Hrs]","tblSAPvsMECAPPHrs","[HELO]="
& [HELO] & " and [STATION]='" & [STATION] & "' and [trackDate]=#" &
DMax("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS MECAPP_Delta, (nz
([MECAPP_HrsWkd],0))-nz(DSum
("[MECAPP_HrsWkd]","tblSAPvsMECAPPHrs","[HELO]=" & [HELO] & " and
[STATION]='" & [STATION] & "' and [trackDate]=#" & DMax
("[trackDate]","tblSAPvsMECAPPHrs") & "#"),0) AS MECAPP_WkdDelta,
tblStatics.runDate AS trackDate
FROM qrySAPvsMECAPPHrs, tblStatics;
Click to expand...
Thank you for any help!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Here is the current iteration of the query:

SELECT qrySAPvsMECAPPHrs.HELO, qrySAPvsMECAPPHrs.STATION, FormatNumber
(nz([SAP_Hrs],0),2) AS SAP_Hours, nz([MECAPP_Hrs],0) AS MECAPP_Hours,
nz([MECAPP_HrsWkd],0) AS MECAPP_HoursWkd, nz([SAP_Hrs],0)-nz(TtlSAP,0)
AS SAP_Delta, nz([MECAPP_Hrs],0)-nz(TtlMECAPPhrs,0) AS MECAPP_Delta, nz
([MECAPP_HrsWkd],0)-nz(TtlHrsWorked,0) AS MECAPP_WkdDelta,
tblStatics.runDate
FROM tblStatics, qrySAPvsMECAPPHrs INNER JOIN qrySAPvsMECAPPMax ON
(qrySAPvsMECAPPHrs.STATION = qrySAPvsMECAPPMax.STATION) AND
(qrySAPvsMECAPPHrs.HELO = qrySAPvsMECAPPMax.HELO);

I had to create a seperate query that grabs the Totals. Also, it does
not allow me to do a Left Join.
 
Back
Top