Odd macro performance. Explanation?

[joeu2004]

I am measuring the performance of certain operations. I stumbled onto
an anomaly that surprises me. I wonder if anyone has a reasonable
explanation.

The structure of the macro is (pseudo-code):

for cnt = 1 to maxCnt
startTime = getTickCount()
for n = 1 to maxN
perform operation
next n
deltaTime = getTickCount() - startTime
debug.print cnt & ": n=" & maxN & format(deltaTime, "0 msec")
next cnt

The first time after I modify the macro, the deltaTime for each
iteration (not just the first deltaTime) is about 430 msec. Oddly,
for subsequent executions (nothing changed), the deltaTime for each
iteration is about 730 msec.

Why would the n-loop consistently take longer after the first macro
execution?

(Note: The deltaTime for cnt=1 is always longer. I presume that is
due to the incremental compilation feature of VBA. In any case, I
ignore the first deltaTime.)

In case the details of the operations might make a difference, the
following code is more complete (without declarations):

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

for cnt = 1 to maxCnt
startTime = GetTickCount()
for n = 1 to maxN
Range("b1:b262").Calculate ' formulas with RAND()
Range("d1:d9").Calculate ' depend on B1:B262
Range("f1:f9").Calculate ' depend on D19
y = Range("f1:f9")
x = Range("d1:d9")
next n
deltaTime = GetTickCount() - startTime
debug.print cnt & ": n=" & maxN & format(deltaTime, " 0 msec")
next cnt

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[joeu2004]

Why would the n-loop consistently take longer after the first macro
execution?

I guess this has stumped everyone else in these NGs, as well.

Can anyone suggest a way for to put this problem to a group of VBA/
Excel experts who might have sufficient internal knowledge to offer
some insight, ideally MS developers?

I believe there are closed NGs or mailing lists that are available to
people who are certified in VBA/Excel. I am not. Can someone who is
certified post this inquiry in an appropriate closed NG and summarize
the credible responses in this thread?

Thanks.


----- original posting -----

 

[ilia]

I'm trying to replicate your results. Here is what I'm getting.
Results 45 through 49 were when I switched to the browser window =]

1: n=1000 1016 msec
2: n=1000 688 msec
3: n=1000 703 msec
4: n=1000 672 msec
5: n=1000 672 msec
6: n=1000 687 msec
7: n=1000 672 msec
8: n=1000 672 msec
9: n=1000 687 msec
10: n=1000 672 msec
11: n=1000 672 msec
12: n=1000 672 msec
13: n=1000 672 msec
14: n=1000 687 msec
15: n=1000 672 msec
16: n=1000 672 msec
17: n=1000 672 msec
18: n=1000 688 msec
19: n=1000 671 msec
20: n=1000 672 msec
21: n=1000 672 msec
22: n=1000 672 msec
23: n=1000 672 msec
24: n=1000 672 msec
25: n=1000 672 msec
26: n=1000 672 msec
27: n=1000 671 msec
28: n=1000 766 msec
29: n=1000 672 msec
30: n=1000 687 msec
31: n=1000 672 msec
32: n=1000 672 msec
33: n=1000 672 msec
34: n=1000 688 msec
35: n=1000 671 msec
36: n=1000 672 msec
37: n=1000 672 msec
38: n=1000 672 msec
39: n=1000 687 msec
40: n=1000 688 msec
41: n=1000 6922 msec
42: n=1000 688 msec
43: n=1000 687 msec
44: n=1000 735 msec
45: n=1000 10609 msec
46: n=1000 9828 msec
47: n=1000 10094 msec
48: n=1000 10422 msec
49: n=1000 8687 msec
50: n=1000 719 msec
51: n=1000 688 msec
52: n=1000 687 msec
53: n=1000 672 msec
54: n=1000 672 msec
55: n=1000 672 msec
56: n=1000 687 msec
57: n=1000 672 msec
58: n=1000 688 msec
59: n=1000 672 msec
60: n=1000 672 msec
61: n=1000 703 msec
62: n=1000 672 msec
63: n=1000 672 msec

 

[ilia]

FYI: here is what I'm using.

B1:B262 = RAND()
E1:E9 = {ROW()/10}
D19 = FREQUENCY(B1:B262,E1:E9)
F1:F9 = D19*E1:E9

Ranges D19 and E1:E9 are array-entered.

I'm trying to replicate your results. Here is what I'm getting.
Results 45 through 49 were when I switched to the browser window =]

1: n=1000 1016 msec
2: n=1000 688 msec
3: n=1000 703 msec
4: n=1000 672 msec
5: n=1000 672 msec
6: n=1000 687 msec
7: n=1000 672 msec
8: n=1000 672 msec
9: n=1000 687 msec
10: n=1000 672 msec
11: n=1000 672 msec
12: n=1000 672 msec
13: n=1000 672 msec
14: n=1000 687 msec
15: n=1000 672 msec
16: n=1000 672 msec
17: n=1000 672 msec
18: n=1000 688 msec
19: n=1000 671 msec
20: n=1000 672 msec
21: n=1000 672 msec
22: n=1000 672 msec
23: n=1000 672 msec
24: n=1000 672 msec
25: n=1000 672 msec
26: n=1000 672 msec
27: n=1000 671 msec
28: n=1000 766 msec
29: n=1000 672 msec
30: n=1000 687 msec
31: n=1000 672 msec
32: n=1000 672 msec
33: n=1000 672 msec
34: n=1000 688 msec
35: n=1000 671 msec
36: n=1000 672 msec
37: n=1000 672 msec
38: n=1000 672 msec
39: n=1000 687 msec
40: n=1000 688 msec
41: n=1000 6922 msec
42: n=1000 688 msec
43: n=1000 687 msec
44: n=1000 735 msec
45: n=1000 10609 msec
46: n=1000 9828 msec
47: n=1000 10094 msec
48: n=1000 10422 msec
49: n=1000 8687 msec
50: n=1000 719 msec
51: n=1000 688 msec
52: n=1000 687 msec
53: n=1000 672 msec
54: n=1000 672 msec
55: n=1000 672 msec
56: n=1000 687 msec
57: n=1000 672 msec
58: n=1000 688 msec
59: n=1000 672 msec
60: n=1000 672 msec
61: n=1000 703 msec
62: n=1000 672 msec
63: n=1000 672 msec

I guess this has stumped everyone else in these NGs, as well.

Can anyone suggest a way for to put this problem to a group of VBA/
Excel experts who might have sufficient internal knowledge to offer
some insight, ideally MS developers?

I believe there are closed NGs or mailing lists that are available to
people who are certified in VBA/Excel. I am not. Can someone who is
certified post this inquiry in an appropriate closed NG and summarize
the credible responses in this thread?

----- original posting -----

 

[joeu2004]

I'm trying to replicate your results. Here is what I'm getting.
Results 45 through 49 were when I switched to the browser window =]

1: n=1000 1016 msec
2: n=1000 688 msec
[....]
63: n=1000 672 msec

This appears to be the result of a single run with maxCnt=63 and
maxN=1000. If so, you are missing the point. Run it one time after
modifying the macro, then run it a second time (or more) without
changing the macro. If you are duplicating my observation, you will
see a significantly higher average. FYI, it should not be necessary
to have such a large maxCnt. Typically, my maxCnt is 10 or 20.

PS: I should have mentioned that I am using Office Excel 2003 with VB
6.3, although the About box also says VBA 6.4.8869.

 

[ilia]

Using VBA 6.5

First run, maxCnt = 10
1: n=1000 828 msec
2: n=1000 719 msec
3: n=1000 734 msec
4: n=1000 703 msec
5: n=1000 719 msec
6: n=1000 703 msec
7: n=1000 719 msec
8: n=1000 719 msec
9: n=1000 703 msec
10: n=1000 719 msec

Modified:
1: n=1000 703 msec
2: n=1000 719 msec
3: n=1000 719 msec
4: n=1000 703 msec
5: n=1000 719 msec
6: n=1000 719 msec
7: n=1000 718 msec
8: n=1000 703 msec
9: n=1000 719 msec
10: n=1000 766 msec

Run again, without modifying:
1: n=1000 766 msec
2: n=1000 719 msec
3: n=1000 718 msec
4: n=1000 719 msec
5: n=1000 719 msec
6: n=1000 703 msec
7: n=1000 719 msec
8: n=1000 718 msec
9: n=1000 704 msec
10: n=1000 718 msec

Again, cannot replicate your results. I could run it all day and
average 719 ms, with the programs I currently have running.

I'm trying to replicate your results. Here is what I'm getting.
Results 45 through 49 were when I switched to the browser window =]

1: n=1000 1016 msec
2: n=1000 688 msec
[....]
63: n=1000 672 msec

This appears to be the result of a single run with maxCnt=63 and
maxN=1000. If so, you are missing the point. Run it one time after
modifying the macro, then run it a second time (or more) without
changing the macro. If you are duplicating my observation, you will
see a significantly higher average. FYI, it should not be necessary
to have such a large maxCnt. Typically, my maxCnt is 10 or 20.

PS: I should have mentioned that I am using Office Excel 2003 with VB
6.3, although the About box also says VBA 6.4.8869.