Slow Crosstab Query

[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

 

[Duane Hookom]

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.

 

[John Spencer]

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
'====================================================