Query extremely slow after adding VBA function PlusWorkdays

[sseto23]

Hi I'm new to these forums, so please excuse any errors on my part:

Basically, I have a query that is the Record Source for a report. The main query makes use of 2 sub-queries. In one of the sub-queries, it calculates the date of the next call to a customer based on the last call date and a lead time parameter.

When we were just using DateAdd ("d", lead_time, last_call) the query ran quickly about 4 seconds, but now that we are using a function to calculate the next call date to exclude weekends and holidays (the PlusWorkdays function is shown below), it runs extremely slow - about 3 minutes.

Public Function PlusWorkdays (dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d",1,PlusWorkdays)
If Weekday(PlusWorkdays, vbMonday) <=5 And _
IsNull(DLookup("[Holidate]","tbl_Holidays",_
"[Holidate] = #"& PlusWorkdays & #")) Then
intNumDays = intNumDays - 1
End If
Loop
End Function

This function is called in the sub-query as follows:

format(PlusWorkdays(last_call, lead_time),"dd-mmm-yy") AS next_call

This query is performed for about 50,000 records.

Any help to speed up this query would be greatly appreciated.

 

[CBrighton]

A DLookUp for each day you check on each record you check.

Perhaps you should use a recordset instead (Example function in the final post here: http://www.access-programmers.co.uk/forums/showthread.php?t=118886), I think opening a single recordset and using .findifrst should be quicker than opening many DLookUps, however that would still leave you opening 50,000 recordsets, one per record.

 

[spikepl]

You are using a brute-force algorithm: scanning each date. Dlookup is slow for that, recordsets may be faster, but it's the method that is not really good. When you have a date and weekday and number of days you should be able to calculate how many SAt-/SUndays there are in the interval. Likewise, a check how many holiday days between those two dates is much faster by a query than a brute-force scan.

 

[CBrighton]

If you can calculate the number of working days once & save it somewhere like a temp table or a hidden control then you can use that saved value for the other 49,999 records too.

In fact, that sounds like a good idea.

Call a function once to get the number of working days which should be added, then use that one figure in a single update query.

 

[vbaInet]

Create a table with all the holidays (and if you like include the weekends too, but not necessary). Link this up to your date field and do a:

NOT IN (SELECT Dates FROM HolidaysTable)

 

[sseto23]

Thanks everyone for replying so quickly.

Just a bit of clarification that might help determine what my options are:

1 - I am using a holiday table called tbl_Holidays that store just the holidays, not the weekends

2 - Each of my 50,000 records can have different last call dates and lead times, so I don't think I can do the business day calculation only once, I will probably have to do it for each record

3 - I might have a calculation that allows me to determine the next call date based on the last call date and the lead time, but it will only exclude weekends not business days. If I have that date, I'm not sure how I would go about excluding the business days to arrive at the final next call date?????

Sorry for being so dense. I'm kind of new at this Access and VBA stuff.

 

[vbaInet]

I see what you mean. So you want to be able to exclude the weekends + holidays in the DateDiff. Hmmm... might need some thinking.

Here is an idea (which will require some fine tuning):

1. Get the number of weekends between the dates:

NumWeekends: DateDiff("ww", Lead_Time, Last_Call, [COLOR=blue]1[/COLOR]) + DateDiff("ww", Lead_Time, Last_Call, [COLOR=blue]6[/COLOR])

1 is for Sunday and 6 represents Saturday.

2. Count how many holiday days that fall within the range:

NumHols: (SELECT Count(Holidate) FROM tbl_Holidays WHERE Holidate BETWEEN [COLOR=blue]MainTable.Lead_Time[/COLOR] AND [COLOR=blue]MainTable.Last_Call[/COLOR])

3. Get the number of workdays (excluding weekends and holidays)

WorkDays: DateDiff("ww", Lead_Time, Last_Call, 1) - NumWeekends - NumHols

Just a thought so try it out.

 

[gemma-the-husky]

stuff like this is always tricky

having a complex function to calculate something for each row is never going to be quick. if the calculated value is non-volatile, this is probably an occasion when calculating and storing the date when you enter the new record is a good idea.

alternatively, rather than build this into the query, calculate it in the report's print event, just for the rows in the report.

worth trying different strategies to see which works the best.

 

[sseto23]

I'm still racking my brains on this. I did try the suggested solution of changing from using DLookUp to using a recordset and that helped a bit, but unfortunately not much

In response to the last 2 replies....

1) I think the suggestion of using the DateDiff would only give me the number of business days (excluding weekends and holidays) between a start date and an end date. In my case Lead_time is not a date, it's actually the # of business days that I need to add to the Last_call date to arrive at my Next_call date. So I have the start date but no end date - it's the end date which I am actually trying to calculate.

2) I thought about somehow building the calculation into the report so that the calculation is done only for the rows on the report, but unfortunately what will appear on the report is actually determined by calculated Next_call date, so I have to do the calculation for each of my 50,000 rows just to determine which rows will eventually end up on the report

Soooooo frustrating!

 

[spikepl]

One idea:

Prepare a table with business days only.

tblBusinessDay
---------------
BusinessDayID - an id starting with som e number, incrementing by 1 for each business day, indexed, primary key
BusinessDayDate - date of the business day - indexed

From BusinessDayDate you can lookup the BusinessDayID. BusinessDayID+Lead_Time gives you the BusinessDayID for the day of next call. The corresponding BusinessDayDate gives you the date. This could be concocted using queries.

 

[vbaInet]

spikepl's idea could work but what date will the table start from? Today, tomorrow? It can't really be determined.

What you need is a recursive function and a collection or dictionary object. Pre-populate your dictionary or collection with the holiday days, create a recursive function to add the Lead_Time to the Last_Call date and recursively check if the day is a weekend or a holiday (using the Exists method of the dictionary). Being recursive it will terminate if the condition is not met and it will not run for ALL records.

But we need one more thing clarified. Here's a scenario, let's say I have these values:

Last_Call date: Mon, 10th October
Lead_Time: 5
Weekends: 15th and 16th October
Holiday: Mon, 17th October

What will the Next_Call date be? Tuesday, 18th October?

 

[spikepl]

Errr, what about the KISS-principle?

 

[sseto23]

In the scenario VbaInet provided, the answer is yes Tuesday Oct. 18th would be the next call date.

Not sure about all this stuff about collection or dictionary objects

I think that stuff might be a bit beyond me. I was hoping for something simplier too Sigh....

 

[gemma-the-husky]

there are quite a few ideas here - the thing you need to appreciate is that because work/holiday days cannot simply be evaluated, this is not a trivial problem.

i would be inclined to store the next call date somewhere, and run a procedure every day to reset nectr call dates for all customers. then at least you only get the overhead once

 

[sseto23]

Believe me I know that this is not a simple problem to solve. Anything to do with date calculations are almost always challenging.

I really appreciate everyone's time and suggestions.

 

[spikepl]

Dave is right.In my fancy table, for each LeadTime you could make a column and calculate the corresponding date via query as described. And then it's a simple lookup , or rather a joined query, and that's it.

 

[penguino29]

Dear sseto23,

As already suggested by the experts here, currently the delay is caused by the DLookup disk access to the table.

You should try to load the tbl_Holidays table into a global array. This loads the dates in the memory. You only need to do this once.

Then when you are searching for the holidays, instead of using Dlookup in your function , you should search in the global array (which is already available in memory) for the holidays. This is way much faster than Dlookup.

I've done some simple tests and I reckon you can reduce the search time by at least 50%.

If you need some sample codes just let me know and I will post it.

Good luck and let us know how you get on.

 

[sseto23]

I am in the process of trying out many of your suggestions, and yes I would like to try the global array scenario if I understood it better. It would be great Penguino29 if you could post some sample code so I could figure out how to declare the array, where to put it, how to load it, and of course how I would refer to it.

Once again, thanks to everyone for their help!!!

 

[penguino29]

Hi sseto23,

Just as a quick hack... try this:

Replace the line in your function PlusWorkdays()

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

to

PlusWorkdays = PlusWorkdays + 1

and run some timing test, and also check if it gives the correct date.

 

[vbaInet]

You should try to load the tbl_Holidays table into a global array. This loads the dates in the memory. You only need to do this once.

Yep, penguino gets my idea. Like I previously mentioned, you can use a dictionary object or a collection, load your tbl_Holidays data into it and it's much faster than an array. All you do is check using the Exists function.

No DLookup() will be required. No constant bounce to the database. No fuss, no mess, just pure code

Whichever approach you take, whether you store the values in your table (like gemma-the-husky rightly suggested) or calculate it on the fly, you will still need a function to get your next call date and to be absolutely sure that you get the right date, you must use a recursive function.