Scheduling beer trucks into timeslots with sufficient performance

Guys,

I have developed an Access application using VBA and DAO to schedule
the filling and transporting of beer kegs around the country. The
application schedules each day of demand for a year of each of 30
brands of beer at each of the 30 warehouses. The application backward
schedules the transfer of full kegs from a brewery to a warehouse (thus
filling each load with 540 kegs), schedules the amount of time that the
keg remains at a bar, and then forward schedules the return of empty
kegs from the warehouse back to the brewery. The objective of the
application is to calculate how many kegs are required to meet demand
for every day of every year.

The problem that I have is modelling the opening hours of warehouses
and breweries. For example, most warehouses are open from 6am to 10pm
from Monday to Friday. If the truck is scheduled to arrive outside of
this time, say 11pm on Friday, the application should model its arrival
at the next opening time of the next working day (say 6am on Monday).
As an order of magnitude, the application schedules 300,000 transport
movements in a year.

The problem that I have is modelling the opening hours with acceptable
application performance. I have tried 3 different approaches.
1) FindNext method of a record set. This takes about 20 minutes to
run.
2) An array with an indexing function to find the appropriate row of an
array. This took about 15 minutes to run.
3) Hard coding using tiered Select Case statements. This takes about 1
minute to run, but it means that users need to know VBA to change the
opening hours.

I also considered using DLookup, but felt that it wouldn't be any
faster than the FindNext method as many records would need to use the
lookup twice (once to verify that the arrival time was outside of the
timeslot, and once to find the next arrival time).

I would really appreciate if someone could suggest another appoach. My
interest is as much from an intellectual point of view as a work point
of view.

Thanks,

carrieroberts

re: Scheduling beer trucks into timeslots with sufficient performance

The in-memory approach would seem to be the best bet. You just need to use
some tricks to reduce the time it takes to find an item in the array or find a
way to make use of hashed key lookups using Collection objects. Without
knowing more about the problem, there's not much more I can offer on the
subject.

On 2 Oct 2005 22:51:58 -0700, carrieroberts@yahoo.com wrote:
[color=blue]
>Guys,
>
>I have developed an Access application using VBA and DAO to schedule
>the filling and transporting of beer kegs around the country. The
>application schedules each day of demand for a year of each of 30
>brands of beer at each of the 30 warehouses. The application backward
>schedules the transfer of full kegs from a brewery to a warehouse (thus
>filling each load with 540 kegs), schedules the amount of time that the
>keg remains at a bar, and then forward schedules the return of empty
>kegs from the warehouse back to the brewery. The objective of the
>application is to calculate how many kegs are required to meet demand
>for every day of every year.
>
>The problem that I have is modelling the opening hours of warehouses
>and breweries. For example, most warehouses are open from 6am to 10pm
>from Monday to Friday. If the truck is scheduled to arrive outside of
>this time, say 11pm on Friday, the application should model its arrival
>at the next opening time of the next working day (say 6am on Monday).
>As an order of magnitude, the application schedules 300,000 transport
>movements in a year.
>
>The problem that I have is modelling the opening hours with acceptable
>application performance. I have tried 3 different approaches.
>1) FindNext method of a record set. This takes about 20 minutes to
>run.
>2) An array with an indexing function to find the appropriate row of an
>array. This took about 15 minutes to run.
>3) Hard coding using tiered Select Case statements. This takes about 1
>minute to run, but it means that users need to know VBA to change the
>opening hours.
>
>I also considered using DLookup, but felt that it wouldn't be any
>faster than the FindNext method as many records would need to use the
>lookup twice (once to verify that the arrival time was outside of the
>timeslot, and once to find the next arrival time).
>
>I would really appreciate if someone could suggest another appoach. My
>interest is as much from an intellectual point of view as a work point
>of view.
>
>Thanks,
>
>carrieroberts[/color]

re: Scheduling beer trucks into timeslots with sufficient performance

carrieroberts@yahoo.com wrote:[color=blue]
> Guys,
>
> I have developed an Access application using VBA and DAO to schedule
> the filling and transporting of beer kegs around the country. The
> application schedules each day of demand for a year of each of 30
> brands of beer at each of the 30 warehouses. The application backward
> schedules the transfer of full kegs from a brewery to a warehouse (thus
> filling each load with 540 kegs), schedules the amount of time that the
> keg remains at a bar, and then forward schedules the return of empty
> kegs from the warehouse back to the brewery. The objective of the
> application is to calculate how many kegs are required to meet demand
> for every day of every year.
>
> The problem that I have is modelling the opening hours of warehouses
> and breweries. For example, most warehouses are open from 6am to 10pm
> from Monday to Friday. If the truck is scheduled to arrive outside of
> this time, say 11pm on Friday, the application should model its arrival
> at the next opening time of the next working day (say 6am on Monday).
> As an order of magnitude, the application schedules 300,000 transport
> movements in a year.
>
> The problem that I have is modelling the opening hours with acceptable
> application performance. I have tried 3 different approaches.
> 1) FindNext method of a record set. This takes about 20 minutes to
> run.
> 2) An array with an indexing function to find the appropriate row of an
> array. This took about 15 minutes to run.
> 3) Hard coding using tiered Select Case statements. This takes about 1
> minute to run, but it means that users need to know VBA to change the
> opening hours.
>
> I also considered using DLookup, but felt that it wouldn't be any
> faster than the FindNext method as many records would need to use the
> lookup twice (once to verify that the arrival time was outside of the
> timeslot, and once to find the next arrival time).
>
> I would really appreciate if someone could suggest another appoach. My
> interest is as much from an intellectual point of view as a work point
> of view.[/color]

I can't really guarantee a better result because I don't know
anything about your application. All I can say is the last time
I had a similar problem I ended up doing all the necessary lookups
and calculations in the queries that retrieved the data in the first
place. It took creating and combining several saved queries, but it
reduced the run time from 10+ minutes to seconds.

re: Scheduling beer trucks into timeslots with sufficient performance

I'm going to make a couple of assumptions:

1 - Each location you deliver to or pick up from has a unique
LocationID somewhere in the database
2 - You know when each location is and isn't open

I think I'd set up a table with LocationID, day of the week, hours
open. Then, when a truck was scheduled to stop there, I'd loop forward
until I found the next time it opened. You should be able to so it
using DLookUps and the day of the week for today, tomorrow, day after,
etc.

Arrive at 11 pm Friday, check if location is still open onf Fridays.
No, so loop forward to Saturday, closed, loop forward again to Sunday,
closed loop to Monday and open at 10 am.

Hope this helps!

Howard Brody