Slow Crosstabs

[Yuli O via AccessMonster.com]

Can some one review my code and tell me where I can change it to make my
query faster.

I is taking 5-10 minutes to run. I'm using 3 of these queries to get a
report that shows me total hours per day, total count per day, and then the
details of each employee scheduled to work.

***Schedule to Work SQL***
TRANSFORM Min(FinalSchedule.[Off]) AS MinOfOff
SELECT FinalSchedule.ID, Format([StartTime],"Medium Time") & " - " & Format(
[endtime],"Medium Time") AS Timet, tblShifts.Dept, tblShifts.Position, [Forms]
![Datespicker]![start] AS Start, [Forms]![Datespicker]![End] AS [End], QMain.
name, ScheduleHeader.STARTDATE, ScheduleHeader.ENDDATE
FROM (tblShifts RIGHT JOIN (ScheduleHeader RIGHT JOIN FinalSchedule ON
ScheduleHeader.ID = FinalSchedule.ID) ON tblShifts.ClassID = ScheduleHeader.
ShiftID) LEFT JOIN QMain ON FinalSchedule.ID = QMain.ID
WHERE (((FinalSchedule.SDate) Between [Forms]![Datespicker]![start] And
[Forms]![Datespicker]![End]) AND ((ScheduleHeader.STARTDATE)<[Forms]!
[Datespicker]![start]) AND ((ScheduleHeader.ENDDATE)>[Forms]![Datespicker]!
[end]))
GROUP BY FinalSchedule.ID, Format([StartTime],"Medium Time") & " - " & Format
([endtime],"Medium Time"), tblShifts.Dept, tblShifts.Position, [Forms]!
[Datespicker]![start], [Forms]![Datespicker]![End], QMain.name,
ScheduleHeader.STARTDATE, ScheduleHeader.ENDDATE
PIVOT Format([sdate],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

***Scheduled Total Hours***
TRANSFORM Last(Hours.Hrs) AS LastOfHrs
SELECT Hours.ID, Format([StartTime],"Medium Time") & " - " & Format([endtime],
"Medium Time") AS Timet, tblShifts.Dept, tblShifts.Position, [Forms]!
[Datespicker]![start] AS Start, [Forms]![Datespicker]![End] AS [End], QMain.
name
FROM (Hours RIGHT JOIN (tblShifts RIGHT JOIN ScheduleHeader ON tblShifts.
ClassID = ScheduleHeader.ShiftID) ON Hours.ID = ScheduleHeader.ID) LEFT JOIN
QMain ON Hours.ID = QMain.ID
WHERE (((Hours.Workdays) Between [Forms]![Datespicker]![start] And [Forms]!
[Datespicker]![End]))
GROUP BY Hours.ID, Format([StartTime],"Medium Time") & " - " & Format(
[endtime],"Medium Time"), tblShifts.Dept, tblShifts.Position, [Forms]!
[Datespicker]![start], [Forms]![Datespicker]![End], QMain.name
PIVOT Format([workdays],"ddd ") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat")
;

***Scheduled Total Count***
TRANSFORM Sum(IIf([off]="X",1,0)) AS DOff
SELECT tblShifts.Dept
FROM FinalSchedule LEFT JOIN tblShifts ON FinalSchedule.ShiftID = tblShifts.
ClassID
WHERE (((tblShifts.StartTime) Is Not Null) AND ((FinalSchedule.SDate) Between
[Forms]![Datespicker]![start] And [Forms]![Datespicker]![End]))
GROUP BY tblShifts.Dept, tblShifts.StartTime
PIVOT Format([sdate],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

 

[Jerry Whittle]

1. Make sure that all the fields in the joins and Where clauses are indexed.

2. Create queries that gather up the data and filter it through the Where
clause. Then base the crosstab queries on these preliminary queries.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Can some one review my code and tell me where I can change it to make my
query faster.

I is taking 5-10 minutes to run. I'm using 3 of these queries to get a
report that shows me total hours per day, total count per day, and then the
details of each employee scheduled to work.

***Schedule to Work SQL***
TRANSFORM Min(FinalSchedule.[Off]) AS MinOfOff
SELECT FinalSchedule.ID, Format([StartTime],"Medium Time") & " - " & Format(
[endtime],"Medium Time") AS Timet, tblShifts.Dept, tblShifts.Position, [Forms]
![Datespicker]![start] AS Start, [Forms]![Datespicker]![End] AS [End], QMain.
name, ScheduleHeader.STARTDATE, ScheduleHeader.ENDDATE
FROM (tblShifts RIGHT JOIN (ScheduleHeader RIGHT JOIN FinalSchedule ON
ScheduleHeader.ID = FinalSchedule.ID) ON tblShifts.ClassID = ScheduleHeader.
ShiftID) LEFT JOIN QMain ON FinalSchedule.ID = QMain.ID
WHERE (((FinalSchedule.SDate) Between [Forms]![Datespicker]![start] And
[Forms]![Datespicker]![End]) AND ((ScheduleHeader.STARTDATE)<[Forms]!
[Datespicker]![start]) AND ((ScheduleHeader.ENDDATE)>[Forms]![Datespicker]!
[end]))
GROUP BY FinalSchedule.ID, Format([StartTime],"Medium Time") & " - " & Format
([endtime],"Medium Time"), tblShifts.Dept, tblShifts.Position, [Forms]!
[Datespicker]![start], [Forms]![Datespicker]![End], QMain.name,
ScheduleHeader.STARTDATE, ScheduleHeader.ENDDATE
PIVOT Format([sdate],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

***Scheduled Total Hours***
TRANSFORM Last(Hours.Hrs) AS LastOfHrs
SELECT Hours.ID, Format([StartTime],"Medium Time") & " - " & Format([endtime],
"Medium Time") AS Timet, tblShifts.Dept, tblShifts.Position, [Forms]!
[Datespicker]![start] AS Start, [Forms]![Datespicker]![End] AS [End], QMain.
name
FROM (Hours RIGHT JOIN (tblShifts RIGHT JOIN ScheduleHeader ON tblShifts.
ClassID = ScheduleHeader.ShiftID) ON Hours.ID = ScheduleHeader.ID) LEFT JOIN
QMain ON Hours.ID = QMain.ID
WHERE (((Hours.Workdays) Between [Forms]![Datespicker]![start] And [Forms]!
[Datespicker]![End]))
GROUP BY Hours.ID, Format([StartTime],"Medium Time") & " - " & Format(
[endtime],"Medium Time"), tblShifts.Dept, tblShifts.Position, [Forms]!
[Datespicker]![start], [Forms]![Datespicker]![End], QMain.name
PIVOT Format([workdays],"ddd ") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat")
;

***Scheduled Total Count***
TRANSFORM Sum(IIf([off]="X",1,0)) AS DOff
SELECT tblShifts.Dept
FROM FinalSchedule LEFT JOIN tblShifts ON FinalSchedule.ShiftID = tblShifts.
ClassID
WHERE (((tblShifts.StartTime) Is Not Null) AND ((FinalSchedule.SDate) Between
[Forms]![Datespicker]![start] And [Forms]![Datespicker]![End]))
GROUP BY tblShifts.Dept, tblShifts.StartTime
PIVOT Format([sdate],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

 

[jocasio via AccessMonster.com]

Thank you, I did as you said.

Analyzed a copy of my database and it fixed the problem by creating indexes
where needed.

1. Make sure that all the fields in the joins and Where clauses are indexed.

2. Create queries that gather up the data and filter it through the Where
clause. Then base the crosstab queries on these preliminary queries.

Can some one review my code and tell me where I can change it to make my
query faster.

[quoted text clipped - 48 lines]

GROUP BY tblShifts.Dept, tblShifts.StartTime
PIVOT Format([sdate],"ddd") In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");