Comparing VLOOKUP speeds versus straight calculations

  • 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"]

    [tr]


    [td]

    01/01/17

    [/td]


    [td]

    06/01/17

    [/td]


    [td]

    MWF

    [/td]


    [td]

    12:00PM - 1:20PM

    [/td]


    [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]


    [/tr]


    [tr]


    [td]

    03/14/17

    [/td]


    [td]

    06/01/17

    [/td]


    [td]

    TF

    [/td]


    [td]

    3:45PM - 5:25PM

    [/td]


    [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]


    [/tr]


    [tr]


    [td]

    04/01/17

    [/td]


    [td]

    05/23/17

    [/td]


    [td]

    WSa

    [/td]


    [td]

    7:20AM - 8:50AM

    [/td]


    [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]


    [/tr]


    [/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"]

    [tr]


    [td]

    R1

    [/td]


    [td]

    MWF

    [/td]


    [td]

    12:00PM - 1:20PM

    [/td]


    [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]


    [/tr]


    [tr]


    [td]

    R2

    [/td]


    [td]

    TF

    [/td]


    [td]

    3:45PM - 5:25PM

    [/td]


    [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]


    [/tr]


    [tr]


    [td]

    R3

    [/td]


    [td]

    WSa

    [/td]


    [td]

    7:20AM - 8:50AM

    [/td]


    [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]


    [/tr]


    [/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"]

    [tr]


    [td][/td]


    [td]

    M

    [/td]


    [td]

    T

    [/td]


    [td]

    W

    [/td]


    [td]

    R

    [/td]


    [td]

    F

    [/td]


    [td]

    Sa

    [/td]


    [/tr]


    [tr]


    [td]

    R1

    [/td]


    [td]

    code

    [/td]


    [td]

    code

    [/td]


    [td]

    code

    [/td]


    [td]

    code

    [/td]


    [td]

    code

    [/td]


    [td]

    code

    [/td]


    [/tr]


    [tr]


    [td]

    R2

    [/td]


    [td]

    code

    [/td]


    [td]

    ...

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R4

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R5

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R6

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R7

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    R8

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/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

  • Re: Comparing VLOOKUP speeds versus straight calculations


    VLOOKUP with a pre-computed table is going to be massively quicker than a than that number of dynamic calculations. You may not notice this on two thousand rows, but as your rows grow it will definitely slow down by going with the straight computation approach. VLOOKUP wont slow down at all. It will also help VLOOKUP if the data is sorted. And there are circumstances also where an INDEX/MATCH combo might be quicker, but in tests that I have done on 100000 rows for looking up integers, VLOOKUP is blazing quick.


    ALso, less formulas = easier to maintain


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Comparing VLOOKUP speeds versus straight calculations


    I just wanted to say thanks, Ger, for responding. I just returned from a vacation and have finally had a chance to re-check this thread.