Slow Crosstab Query

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

NeoFax

I have a crosstab query that takes about 5 minutes to run. I would
like to speed it up or take the hit once and write the data to a table
and base subsequent queries of the table. How would I go about
speeding this crosstab query:

TRANSFORM Sum(nz([percentagestage],0)) AS Percentage
SELECT tblPDPStation_PercentComplete.[HELO#],
IIf([STATION]="6PAINT","5CUSTOM",IIf([STATION]="INT","5CUSTOM",
[STATION])) AS STA, ((DAvg("hours","tblHelo_Summary")*27)/12)/
(MyCountWorkDays(dhFirstWorkdayInMonthA(Date()),dhLastWorkdayInMonthA(Date())))
AS DailyAVG,
MyCountWorkDays(dhFirstWorkdayInMonthA(Date()),dhLastWorkdayInMonthA(Date()))
AS Workdays, Sum(tblPDPStation_PercentComplete.PercentageStage) AS
Total
FROM tblPDPStation_PercentComplete
WHERE
(((IIf([STATION]="6PAINT","5CUSTOM",IIf([STATION]="INT","5CUSTOM",
[STATION]))) Not Like "RWK"))
GROUP BY tblPDPStation_PercentComplete.[HELO#],
IIf([STATION]="6PAINT","5CUSTOM",IIf([STATION]="INT","5CUSTOM",
[STATION])), ((DAvg("hours","tblHelo_Summary")*27)/12)/
(MyCountWorkDays(dhFirstWorkdayInMonthA(Date()),dhLastWorkdayInMonthA(Date()))),
MyCountWorkDays(dhFirstWorkdayInMonthA(Date()),dhLastWorkdayInMonthA(Date()))
PIVOT Format([Date],"yy-mm") In
("07-12","08-01","08-02","08-03","08-04","08-05","08-06","08-07","08-08","08-09","08-10","08-11","08-12");


It is ran on a table that has only 14,778 records currently but rises
every day by roughly 200 records. Thanks!

Terry
 
You are paying a huge performance price for the DAvg() and possibly your
other functions of MyCountWorkDays(),dhFirstWorkDayInMonthA(), and
dhLastWorkDayInMonthA().

We don't know the code for these functions and which if any fields are
indexed.
 
One, CHANGE the WHERE clause to the following.
WHERE Station <> "RWK"

Second, make sure Station is indexed

Third, examine your functions to see if you can make them more
efficient. It might be worthwhile to build a table with the number of
workdays in a month and use that to get the CountOfWorkDays.
FieldMonthID - Text - values yyyymm format 200801, 200802 ...
WorkDays - Integer 20, 19,22, etc

Then to get the count of workdays you could use the table in your query
(most efficient) or use a dlookup statement
DLookup("WorkDays","tblWorkDays","FieldMonthID='" &
Format(Date(),"YYYYMM") & "'")

Just an FYI DATE is a bad name for a field. The function Date() and a
fieldname [Date] can become confused by the programmer or even by the
SQL interpreter in the right circumstances. If you can I would
recommend that you change the field name to reflect what it is a date
for (workDate, actionDate, BirthDate).

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top