excel - Surprising nested loops duration -

excel - Surprising nested loops duration -

i have inner , outer loop in vba code. think execution time in total estimated outer loop count * inner loop count, doesn't seem case performing 10 * 10^8 operations takes 5 seconds, performing 10^8 * 10 operations takes 24 seconds. seems many repetitions of outer loop making total execution time longer compared many repetitions of inner loop, although total count of operations remains same in both examples. supposed work this? why that? doing wrong?

sub test() dim long dim j long dim t single maxi = 0 '1 maxj = 9 '1 bilion while maxj >= 0 t = timer = 1 10 ^ maxi j = 1 10 ^ maxj next j next debug.print maxi + maxj & " " & maxi & " " & maxj & " " & timer - t maxi = maxi + 1 maxj = maxj - 1 loop end sub

the resulting dataset starting bilion repetitions in outer loop , 1 repetition in inner 1 looks this:

+-------------------+-----------------+-----------------+----------+ | total_operations | outer_loop_cnt | inner_loop_cnt | time | +-------------------+-----------------+-----------------+----------+ | 9 | 9 | 0 | 222,2305 | | 9 | 8 | 1 | 24,52734 | | 9 | 7 | 2 | 8,300781 | | 9 | 6 | 3 | 5,683594 | | 9 | 5 | 4 | 5,070313 | | 9 | 4 | 5 | 5,109375 | | 9 | 3 | 6 | 5,167969 | | 9 | 2 | 7 | 4,933594 | | 9 | 1 | 8 | 4,898438 | | 9 | 0 | 9 | 5,109375 | +-------------------+-----------------+-----------------+----------+

the resulting dataset starting bilion repetitions in inner loop , 1 repetition in outer 1 looks this:

+---+---+---+----------+ | 9 | 0 | 9 | 4,800781 | | 9 | 1 | 8 | 4,890625 | | 9 | 2 | 7 | 4,808594 | | 9 | 3 | 6 | 4,800781 | | 9 | 4 | 5 | 4,757813 | | 9 | 5 | 4 | 4,972656 | | 9 | 6 | 3 | 5,308594 | | 9 | 7 | 2 | 6,980469 | | 9 | 8 | 1 | 24,54297 | | 9 | 9 | 0 | 222,3828 | +---+---+---+----------+

update: if alter code, upper bounds not computed each loop this:

do while maxj >= 0 upper_i = 10 ^ maxi upper_j = 10 ^ maxj t = timer = 1 upper_i j = 1 upper_j ...

i improve results bigger outer loops. still seems true, best have equal count of inner , outer loop runs best performance. me, still surprising. ideas?

9 9 0 60,07031 9 8 1 14,8125 9 7 2 10,36719 9 6 3 9,414063 9 5 4 9,507813 9 4 5 9,40625 9 3 6 9,523438 9 2 7 9,835938 9 1 8 12,875 9 0 9 11,02344 9 0 9 9,75 9 1 8 9,257813 9 2 7 9,265625 9 3 6 9,273438 9 4 5 9,1875 9 5 4 9,164063 9 6 3 9,21875 9 7 2 10,42969 9 8 1 14,96094 9 9 0 59,66406

btw, how can table formatting?

yes, makes sense. for loop bounds calculated @ origin of loop. if run inner loop billion times, you're calculating 10 ^ maxi once, 10 ^ maxj once, billion repetitions of j = j + 1. if run outer loop billion times, you're doing 10 ^ maxi once, , 10 ^ maxj , j = j + 1 billion times.

excel excel-vba