Code:
Current Query: Mech Final Equip 3 Mth
SELECT DISTINCTROW
Format$(T1.[Up Date/Time],'yyyy mm') AS [Up Date/Time By Month],
T1.[Final Equipment] AS Expr1,
Sum(T1.Down) AS [Sum Of Down],
Count(*) AS [Count Of 416 Data]
FROM [416 Data] AS T1
GROUP BY
Format$([416 Data].[Up Date/Time],'yyyy mm'),
T1.[Final Equipment],
T1.Status,
T1.Reason,
Year(T1.[Up Date/Time])*12+DatePart('m',T1.[Up Date/Time])-1
HAVING
(((Format$([416 Data].[Up Date/Time],'yyyy mm'))
Between (Format([Forms]![H36 Date Selector]![Start Date 1],'yyyy mm'))
And (Format([Forms]![H36 Date Selector]![End Date 1],"yyyy mm")))
AND (([416 Data].[Final Equipment]) Is Not Null)
AND (([416 Data].[Status])="brkdwn")
AND (([416 Data].[Reason])="mech"));
1) DISTINCT is only needed because you have extra levels in the GROUP BY that aren't broken out in the SELECT. Since you don't actually need the results broken out by status and reason, since only one type of each will be returned, you can remove them from GROUP BY clause, and kill the DISTINCT keyword.
2) The numeric calc for the year/month in the GROUP BY isn't needed, because "YYYY MM" format yields the same effective result.
3) No reason to rename [Final Equipment]. That's probably left over from testing different versions.
4) Depending on Jet's optimization algorythms, it can be more efficient to move the HAVING conditions to a WHERE clause for any conditions where the HAVING can be determined at record selection time.
5) I generally like to count something specific rather than count *. I can't swear from knowledge of Jet's internals that it's always faster, but my experience tells me that at least sometimes it is.
6) I always alias my tables to save typing and make the SQl easier to read.
Based on those, the following is a more optimized version of [Mech Final Equip 3 Mth]
Code:
Partially Optimized Query: Mech Final Equip 3 Mth
SELECT
Format$(T1.[Up Date/Time],'yyyy mm') AS [Up Date/Time By Month],
T1.[Final Equipment],
Sum(T1.Down) AS [Sum Of Down],
Count(T1.Down) AS [Count Of 416 Data]
FROM [416 Data] AS T1
WHERE (((T1.[Final Equipment]) Is Not Null)
AND ((T1.[Status])="brkdwn")
AND ((T1.[Reason])="mech"))
AND ( (Format$(T1.[Up Date/Time],'yyyy mm'))
BETWEEN (Format([Forms]![H36 Date Selector]![Start Date 1],'yyyy mm'))
AND (Format([Forms]![H36 Date Selector]![End Date 1],"yyyy mm"))
))
GROUP BY
Format$(T1.[Up Date/Time],'yyyy mm'),
T1.[Final Equipment];
One more optimization is available, and I'd strongly suggest you use it. You are currently calculating every single 'yyyy mm' at the record level BEFORE you exclude all the records that are out of the date range. Just by NOT changing the format of the date field before that condition test, you should achieve a massive increase in speed.
Code:
Optimized Query: Mech Final Equip 3 Mth
SELECT
Format$(T1.[Up Date/Time],'yyyy mm') AS [Up Date/Time By Month],
T1.[Final Equipment],
Sum(T1.Down) AS [Sum Of Down],
Count(*) AS [Count Of 416 Data]
FROM [416 Data] AS T1
WHERE (((T1.[Final Equipment]) Is Not Null)
AND ((T1.[Status])="brkdwn")
AND ((T1.[Reason])="mech")
AND ((T1.[Up Date/Time])
BETWEEN ([Forms]![H36 Date Selector]![Start Date 1])
AND ([Forms]![H36 Date Selector]![End Date 1])
))
GROUP BY
Format$(T1.[Up Date/Time],'yyyy mm'),
T1.[Final Equipment];
Windows 7 64bit 
Access 2013
Reply With Quote
Originally Posted by Dal Jeanis
