Slow function calls...

[MeisoT]

I have a query that calls a custom function to calculate the difference between two dates, which are fields in the query. Each record in the query calls the function, making for very, very slow run-time for the report bound to the query because the query can return hundreds of records.

What alternatives are available for speeding up the calculations (i.e.- using arrays, recordsets in memory, etc).

Access version = 2000

Thanks.

 

[PHV]

Any chance you could post the code of your custom function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MeisoT]

Here is the code:

Function CalcTTime(BegDate As Date, EndDate As Date) As Integer

Dim i as integer
Dim DaysToSubtract as long
Dim DaysBetween as long

DaysToSubtract=0
DaysBetween=(EndDate - BegDate) - 1

For i=1 To DaysBetween
'Check if date is a weekend
If (Weekday(BegDate +i)=1) or (Weekday(BegDate + i) = 7) Then
DaysToSubtract = DaysToSubtract + 1)

'Check if date is a holiday
ElseIf Not IsNull(DLookup("Holidaydate", "tblHolidays", "[HolidayDate]=#" & (BegDate + i) & "#")) Then
DaysToSubtract = (DaysToSubtract + 1)
end If

next i

CalcTTime = (Enddate - BegDate) - DaysToSubtract

End Function


Summary: The Function finds the number of days between the beginning and ending dates, then cycles through each date and increments a variable that keeps track of how many days to subtract (weekends and holidays) from the number of days between the two dates. There is a local table stored in the database with a list of holidays which is checked using the Dlookup function.

I think the Dlookup function might be one of the bottlenecks that is slowing everything down because it is called for each day in between the two dates. Need some suggestions for alternatives.

Thanks.

 

[Blorf]

Hello MeisoT.

The Killer in your code is the Dlookup. Dlookup is a killer with something like this.

I wrote similar function. Below is my code to get around the dlookup

Public Hdays(100) As Date
Public Hcount As Integer
Public Wdays(7) As Integer

''''''''''' Below code loads holidays into public memory array.

Public Function Load_H()
Set Sele1 = CurrentDb.OpenRecordset("Holidays")
Sele1.MoveFirst
Hcount = 0
While Not Sele1.EOF()
Hcount = Hcount + 1
Hdays(Hcount) = Sele1.Holiday
Sele1.MoveNext
Wend
Wdays(1) = 0
Wdays(2) = 1
Wdays(3) = 1
Wdays(4) = 1
Wdays(5) = 1
Wdays(6) = 1
Wdays(7) = 0
End Function

Then, in your function something like

For Q = 1 To Hcount
If Hdays(Q) = I Then OK = 0
Next Q

to tell you if its a Holiday.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[PHV]

Perhaps something like this ?
Function CalcTTime(BegDate As Date, EndDate As Date) As Integer
Dim i As Date, DaysToSubtract As Long
DaysToSubtract = DCount("*", "tblHolidays", "Weekday(HolidayDate,2)<6 AND HolidayDate Between #" & (BegDate + 1) & "# And #" & EndDate & "#")
For i = (BegDate + 1) To EndDate
'Check if date is a weekend
If Weekday(i, 2) >= 6 Then
DaysToSubtract = DaysToSubtract + 1
End If
Next
CalcTTime = (Enddate - BegDate) - DaysToSubtract
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MeisoT]

Blorf and PHV,

Thanks so much for your suggestions!!! I will test both methods to see which provides the speediest output.