Speeding up a function by any means necessary.

[Nilo]

About a year ago, I found this function which I believe was originally posted by MichaelRed(it's a great function!).
It counts the days between StartDate and EndDate while excluding weekends and Holidays (excludes Holidays by referencing the tblHolidays table which includes a date field called Holidate).

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date&quot

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

Anyway, this works great but I think I might be asking too much of it.
I have an application in Access 97.
This application has close to 10,000 records.
Each record relates to a request that needs to be completed.
Every one of those records(requests)has 3 date fields:
ReceiptDate - When the request was keyed in
PendingDate - The last time somebody did work on the request.
ClosedDate - When the request was completed.
(Just in case it's helpful, the UID field name is Tracking#)

Here's the SQL statement from one of the queries I use:

SELECT tLog.Coordinator, tLog.Tracking#, tLog.ReceiptDate, tLog.State, tLog.RequestType, tLog.ClosureDate, tLog.PendingDate, DeltaDays([ReceiptDate],Date()) AS TotalDaysOpen, DeltaDays([PendingDate],Date()) AS PendingTillClosed
FROM tLog
WHERE (((tLog.ClosureDate) Is Null));

It doesn't take long to run, but if I try to send it to Excel or put it behind a report in Access, it takes at least five minutes.
My machine specs are:
1.4 Ghz processor
256 M RAM

So now I have two questions:
1. In this scenario, does indexing the date fields even make a difference?(I know I'm reaching here, I'm desperate)
2. Is there anything that would help speed up the process?

Sorry if I glazed over anything that would help.
Let me know if I did.
Thanks in Advance.
Chris

 

[MichaelRed]

Not sure there is any way -or point- to sppeding this up sufficiently to satisfy whatever criteria you may have. If the routine performs 'acceptably' in generating a recordset, but balks (slows down?) during another operation (export to ...), it seems reasonable to ask the question a bit differently, like: What is the difference in exporting the recordset with the calculated field vs, the same routine w/o the calculated field?

As I noted to another poster earlier today, it might be worthe the effort to profile the process (and thus KNOW where the time is being consumed) before assuming the need to improve the performance of an individual procedure.


Still, thanks for the nice comment re the routine.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

Michael Red is correct. You don't really know where the problem is. Try changing the query to a Make-Table query and taking that temp table in as input to your Report. You could also use that temp-table as the data to be exported.

If the make-table is quick but the other export and report processes are slow in your opinion then the problem does not exist in the Function but rather in your subsequent processes. Bob Scriver

 

[Nilo]

Thank you for your responses.
You guys are right.
I did make some assumptions here about this being the function's fault.
The post's subject should have been:
Speeding up a process by any means necessary

I just chopped down my main table from 10,000 records to 3,000 just to see what would happen to processing time(the Select query takes 5 sec. tops while any other action or query takes 1 min. + to run).
Here's the SQL statement from the Make Table query I just ran:

SELECT tLog.[Tracking #], DeltaDays([Receipt Date],[Closure Date]) AS TDO, tLog.[Closure Date] INTO tTEST
FROM tLog
WHERE (((tLog.[Closure Date]) Is Not Null));

This took approx. a minute and a half.
Unfortunately I need the reports to be realtime so even updating the report's backing data a few times a day via a Make Table query wouldn't work.
(I tried that in the past and everybody started complaining, what else is new.)

MichaelRed,
You mentioned "profiling the process'.
Could you elaborate on a good way to do this?

I'm self-taught and I'm an awful teacher so there are a lot of basic trouble shooting techniques that I don't know about.
At this point, I'm not so concerned about my original question as I am on sharpening my trouble shooting skills.
Thanks for everything.
Chris

 

[MichaelRed]

I can't help a lot here. The concept is to institute a process which tracks activity. The norm is to list the activity and time started, where activity refers to any process or procedure within the app, or possibly even a subsection where you have the ability to (programatically) subdivide it. Typically, the divisions are along (in Ms. A.) the lines of individual procedures. Some versions also log the end time of a lump. This can be quite useful in the event orientated environment like Ms.s' Visual thingggyyysssss, as some process my complete and 'nothing' is happening (CPU released to O.S., waiting for user input, ...) so the profile needs to "know" about the idle time. Analysis of the results is quite varied, however some of the more common issues are:

Which parts (procedures) are most often entered (this is the most likely to yield overall improvement in perfromance), which parts take the most time for an individual instantiation (execution), which parts appear to be executed more often than expected (perhaps this is being caled un-necessarily). Additional information may be available, depending on the package used, such as what is the result of (some) operation (perhaps a procedure which APPEARS to be calculating a value actually always returns the same value, so it might be simplified to return a constant, or some-one may have set the return a procedure to a constant for some testing and it was never corrected to return a calculated value).

When constructing profiles "Manually", a common practice is to just add via hard coding some statements which record what the programmer thinks might be important. This is often counter-productive, as the profiling code then needs to be manually inserted and later removed, and until this (removal) process is complete it tends to adversly impact the performance of the app. Somewhere within Tek-Tips, I have previously posted a routine aimed at tracing the execution of an app, along with some relatively verbose discussion of the ancilliary objects necessary for a reasonable implementation. In this process, the generic process I used to limit the impact of the trace code on the overall performance is discussed in some more detail. It is basically to define a global flag for the process, and only turn it "on" for the specific occassion of doing the trace (or profile). Thereby, limiting the impact to a single conditional at each execution point.

IF "search" was working, you might find references to the process with "trace" or "tracelog".



MichaelRed
m.red@att.net

Searching for employment in all the wrong places