Query with calculations slow

[Gurk]

I am building a small database to track overtime claims - I am only saving userid, date and start / stop times. The dollar amounts I am calculating at the time the report is generated so that no personal financial information is stored which eliminates any security concerns. I am running a make table query as the source for the report as I want running totals which cannot be calculated in a report - so I perform the calculations in the query.

Two of the columns in the query are calculated with a function which gets the amount of time and multiplies it by the modifier (which is looked up from another table to hold static information). All the calculations are correct, but the running of the query is very slow - to return a one page report with 7 lines takes about 10 seconds. If I change the query to a select query, I can watch it build line by line.

Any suggestions would be greatly appreciated.

 

[dhookom]

Maybe you could share the function or the sql or other significant information about your tables, indexes, sort order, criteria, ...

Duane
Hook'D on Access
MS Access MVP

 

[Gurk]

Sorry - not thinking clearly.

The query SQL:

SELECT tblUsers.StaffUserID, tblOT.otDate, tblOT.otStart, tblOT.otStop, Round(Hour([otStop]-[otStart])+Minute([otStop]-[otStart])/60,2) AS otActualHours, Round(CalculateOT([otDate],[otStop],[otStart],[Callback],[HolidayDay],[Travel]),2) AS otAdjustedHours, Int(Forms!frmOTEdit!txtAnnualSalary) AS AnnualSalary, Round([AnnualSalary]/1956.6,2) AS HourlyWage, nz(Round(CalcNight([otStart],[otStop]),2),0) AS NightHours, nz(Round(CalcNightAdjusted([otDate],[otStart],[otStop],[Callback],[HolidayDay],[Travel]),2),0) AS NightHoursAdj, Round([hourlywage]*[nighthours]*0.3,2) AS NightAmount, Round([otAdjustedHours]*[HourlyWage],2) AS otAmount, Round([nightamount]+[otamount],2) AS otTotalAmount, tblUsers.UserName, tblUsers.StaffPosition, tblOT.Callback, tblOT.HolidayDay, tblOT.Travel, tblOT.otNotes INTO tempOT
FROM tblUsers RIGHT JOIN tblOT ON tblUsers.StaffUserID = tblOT.StaffUserID
WHERE (((tblUsers.StaffUserID)=[Forms]![frmOTEdit]![StaffUserID]) AND ((Month([otDate]))=[Forms]![frmOTEdit]![cboMonthName]) AND ((Year([otDate]))=[Forms]![frmOTEdit]![txtReportYear]));

And here is the function:
Function CalculateOT(dtOTDate As Date, dtOTStart As Date, dtOTStop As Date, varCallBack As Variant, varHolidayDay As Variant, varTravelDay As Variant)

Dim intCallBackHours As Integer
Dim dblTotalOTHours As Double
Dim varIsHoliday As Variant
Dim varWorkDay As Variant
Dim strLookupCriteria As String
Dim dblHolidayPercent As Double
Dim dblWeekendPercent As Double
Dim dblOTPercent As Double

intCallBackHours = DFirst("[CallbackHours]", "tblMissionInfo")
dblTotalOTHours = Hour(dtOTStop - dtOTStart) + Minute(dtOTStop - dtOTStart) / 60
dblHolidayPercent = DFirst("[HolidayPercent]", "tblMissionInfo")
dblWeekendPercent = DFirst("[WeekendPercent]", "tblMissionInfo")
dblOTPercent = DFirst("[OTPercent]", "tblMissionInfo")

strWeekday = Format(dtOTDate, "dddd")


varIsHoliday = DLookup("[HolidayDate]", "tblHoliday", "[HolidayDate] = #" & Format(dtOTDate, "yyyy\/mm\/dd") & "#")
varWorkDay = DLookup("[NormalWorkDay]", "tblWorkDays", "[DayofWeek] = '" & Format(dtOTDate, "dddd") & "'")

If varHolidayDay = True Or Not IsNull(varIsHoliday) Then
dblTotalOTHours = dblTotalOTHours * dblHolidayPercent
ElseIf varWorkDay = False Then
dblTotalOTHours = dblTotalOTHours * dblWeekendPercent
Else
dblTotalOTHours = dblTotalOTHours * dblOTPercent
End If

If varCallBack = True Then
If dblTotalOTHours < intCallBackHours Then
CalculateOT = intCallBackHours
Else
CalculateOT = dblTotalOTHours
End If
Else
CalculateOT = dblTotalOTHours
End If

If varTravelDay = True Then
If CalculateOT > 12 Then
CalculateOT = 12
End If
End If


End Function

 

[dhookom]

Six domain aggregate functions called from each row of the query is bound to be a huge resource hog. All of the DFirst()s should be pulled out for starters. Since they all return just a single value from a single record, you can just add a totals query from the table tblMissionInfo to the query and send in the required field.

I don't have the time to review everything but there are a number of methods to optimize your SQL and the function.

Duane
Hook'D on Access
MS Access MVP

 

[Gurk]

Thank you for the comment - I had not thought of that before, guess that's what you get for learning as you go. I replaced all the DFirst with global variables which I set with my switchboard form. That alone reduced the report from about 40s to load to under 10.

I appreciate your advice, I am sure there are more improvements I can find so I will keep digging.

Thanks again!