I am trying to eek a little more performance out of my database and am curious what others opinion on this is. The following is a very simplified version of my table called SCHEDULE, but it gets the point across.
SCHEDULE
[TABLE="width: 500"]
01/01/17
[/td]06/01/17
[/td]MWF
[/td]12:00PM - 1:20PM
[/td][code that computes total number of hours spent doing this activity on Mondays, Wednesdays, and Fridays in date range MINUS holidays occurring on those days]
[/td]03/14/17
[/td]06/01/17
[/td]TF
[/td]3:45PM - 5:25PM
[/td][code that computes total number of hours spent doing this activity on Tuesdays and Fridays in date range MINUS holidays occurring on those days]
[/td]04/01/17
[/td]05/23/17
[/td]WSa
[/td]7:20AM - 8:50AM
[/td][code that computes total number of hours spent doing this activity on Wednesdays and Saturdays in date range MINUS holidays occurring on those days]
[/td]
[/TABLE]
There are actually only 8 concrete date ranges, so that's something I can play with. You can think of it more like this
SCHEDULE
[TABLE="width: 500"]
R1
[/td]MWF
[/td]12:00PM - 1:20PM
[/td][code that computes total number of hours spent doing this activity on Mondays, Wednesdays, and Fridays in date range MINUS holidays occurring on those days]
[/td]R2
[/td]TF
[/td]3:45PM - 5:25PM
[/td][code that computes total number of hours spent doing this activity on Tuesdays and Fridays in date range MINUS holidays occurring on those days]
[/td]R3
[/td]WSa
[/td]7:20AM - 8:50AM
[/td][code that computes total number of hours spent doing this activity on Wednesdays and Saturdays in date range MINUS holidays occurring on those days]
[/td]
[/TABLE]
These 8 ranges are stored in a separate worksheet called MASTER for database purposes.
Okay, so here is the challenge: There are about 2000 rows in SCHEDULE. I could (and actually have already done it) have code in that fourth column which does all the counting of days, but it's quite bulky. It looks like this:
=IF(ISNUMBER(FIND("M",[@DAYS])),1,0) * INT((WEEKDAY([@[START DATE]]-IF(ISNUMBER(FIND("M",[@DAYS])),2,0))-[@[START DATE]]+[@[END DATE]])/7)* [@[LEC HOURS/SESSION]]+IF(ISNUMBER(FIND("T",[@DAYS])),1,0) * INT((WEEKDAY([@[START DATE]]-IF(ISNUMBER(FIND("T",[@DAYS])),3,0))-[@[START DATE]]+[@[END DATE]])/7)* [@[LEC HOURS/SESSION]]+IF(ISNUMBER(FIND("W",[@DAYS])),1,0) * INT((WEEKDAY([@[START DATE]]-IF(ISNUMBER(FIND("W",[@DAYS])),4,0))-[@[START DATE]]+[@[END DATE]])/7)* [@[LEC HOURS/SESSION]]+IF(ISNUMBER(FIND("R",[@DAYS])),1,0) * INT((WEEKDAY([@[START DATE]]-IF(ISNUMBER(FIND("R",[@DAYS])),5,0))-[@[START DATE]]+[@[END DATE]])/7)* [@[LEC HOURS/SESSION]]+IF(ISNUMBER(FIND("F",[@DAYS])),1,0) * INT((WEEKDAY([@[START DATE]]-IF(ISNUMBER(FIND("F",[@DAYS])),6,0))-[@[START DATE]]+[@[END DATE]])/7)* [@[LEC HOURS/SESSION]]+IF(ISNUMBER(FIND("Sa",[@DAYS])),1,0) * INT((WEEKDAY([@[START DATE]]-IF(ISNUMBER(FIND("Sa",[@DAYS])),7,0))-[@[START DATE]]+[@[END DATE]])/7)* [@[LEC HOURS/SESSION]]
You get the point... it's a lot of computation going on for 2000 or so rows.
Another option is to precompute most of this in the MASTER sheet like so
[TABLE="width: 500"]
M
[/td]T
[/td]W
[/td]R
[/td]F
[/td]Sa
[/td]R1
[/td]code
[/td]code
[/td]code
[/td]code
[/td]code
[/td]code
[/td]R2
[/td]code
[/td]...
[/td]R3
[/td]R4
[/td]R5
[/td]R6
[/td]R7
[/td]R8
[/td]
[/TABLE]
where "code" just computes the number of Mondays in R1, then number of Tuesdays in R1, and so on.
Obviously, this saves a ton of computation; however, now my SCHEDULE table will have to perform LOOKUP (V or H) over 2000 rows. Plus, it still has to do a little math to multiply the number of hours against the number of days.
BASE QUESTION: Which do you think is the faster of the two methods? VLOOKUP or straight computation per row?
Thank you,
Roy