Degradation of performance : upgrading from Excel 2000 to Excel 20

[Guest]

Hi all,

I know everyone is looking for latest version, but I work as a developer and
my customer use Excel 2000. He asked me to evaluate the cost of an upgrade to
Excel 2003, so here I am.

My program must fill 5000*255 cells.
I use the VBA method "Resize" to select all the range, then "Value" and set
them with an array containing all the datas.
AFAIK, this is the most effective way to do this.


The problem I have is about the performances :
this code works both on Excel 2000 and on Excel 2003 but on the same
computer respectively need 3 sec and 9 sec.
This difference is not acceptable by my customer.


Could anyone tell me if this lack of perf is a known issue when upgrading
and if there is a workaround ?
I see on the net that this is a computer problem, but I'm afraid that they
wouldn't change their hardware for more competitive ones.....


Thanks for giving me any pointers and have a nice day !


Manu, hope I made myself clear enough, I'm not a good english speaker !

 

[NickHK]

If you were talking about the difference between XL2007 and earlier
versions, then you would probably see marked reduction in speed as numerous
posts here have related.
Without knowing exactly what you are doing, the test code below does run
faster in XL2K:
You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
overhead, but with the code below, this reduced all results only by about 1
second.

XL2K XL2002
38 sec 53sec

Private Sub CommandButton1_Click()
Dim arr(1 To 5001, 1 To 256) As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim OutRange As Range
Dim StartTime As Date

Const RUNS As Long = 10

StartTime = Now()

For i = 1 To RUNS
For j = LBound(arr, 1) To UBound(arr, 1)
For k = LBound(arr, 2) To UBound(arr, 2)
arr(j, k) = Rnd()
Next
Next

Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
UBound(arr, 2) - LBound(arr, 2))
With OutRange
.ClearContents
.Value = arr()
End With

Next

Debug.Print "**** With Excel " & Application.Version
Debug.Print , "Execution of " & RUNS & " runs of " & OutRange.Cells.Count &
" cells"
Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & " seconds"
Debug.Print "****"
End Sub

NickHK

 

[Bob Phillips]

That's odd Nick.

I have just run your tests and I got

2K: 23 secs
XP/2002: 16 secs

AT first I thought you had said 2003 so I was going to post that oddly 2002
was faster, but when I saw that you said 2002 I repeated it, and got 17 secs
this time.

?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[NickHK]

Bob,
That is strange, unless it has something to do with the maths processor/CPU
stuff.
As you see, my system is relatively old and slow, compared to yours, but
(this code anyway) certainly runs faster in XL2K compared to XLXP/2002.
Don't have higher versions to test.

So it would seems XL version is not the only consideration.

NickHK

 

[Guest]

Thanks for replying so fast !
I tested your code (on excel2003).
I'm afraid I don't understand, the printed time is very good (<3s), but the
real time (after refresh of the grid) is much different (>24s).
I noticed that you also used the Value property, as I do.

The calculation time isn't the problem, but the populating of each cell is.

 

[NickHK]

Not sure what you mean by the difference between the 3 sec and 24 sec, using
that code I posted.
The printed time will when everything has finished, assuming you do not have
anything else running.
Note that there is no calculation of dependent cells on the XL sheet
involved in this, which may not be the your case.

Yes, the time consuming part is dumping the large array to the WS. If you
can avoid using such a large array, by splitting it up and only dumping what
is need/changed, you will speed up the routine.

NickHK

 

[Peter T]

As you see, my system is relatively old and slow, compared to yours

Or maybe (?) Bob's is slower than yours with one or both tests stretching
into minutes (like mine). If so would need to change -
Format "s" to Format "hh:mm:ss"

Regards,
Peter T

 

[Bob Phillips]

I just tried it with 2003 and 2007 as well. Unfortunately these are both on
my laptop, so I repeated the 2K test as well. I don't have XP on there, so I
was unable to make that comparison (which is probably the most interesting).

I ran each three times and got these results in secs

2K: 11, 12, 11
2003: 18, 18, 18
2007: 20, 20, 20

It would seem that 2003 is considerably slower than 2K, and 2007 is slower
again.

Maybe the OP should at least check-out 2002/XP.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

LOL! I couldn't sit and wait that long Peter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I just add some Win32GetTickCounter to evaluate the
With OutRange
.ClearContents
.Value = arr()
End With
instruction

And where the Now() method tells 3sec, my GetTickCounter says 24s.....


Manu.

 

[NickHK]

Peter,
Good catch, but I did not expect anyone's system to be that slow <g>.

NickHK

 

[NickHK]

Sorry I don't understand how you get that much difference in timing.
Post the code that you are using.

NickHK

 

[Peter T]

I ran tests in different versions concurrently whilst doing some image
processing in another app, perhaps that's why one of my tests straddled a
minute <g>

Regards,
Peter T

 

[Roger Govier]

Hi Nick

FYI
I ran the test on all the versions I have
97 25 secs
2000 26
2002 28
2003 37
2007 45

so much for progress, eh!!!

Interestingly, I tried switching off multithreaded calculation mode in
XL2007 (mine is only a single core machine) and the times in XL2007 went
up to 48 secs.
This is contrary to a suggestion that Nick Hodge has made a couple of
times, that switching off Multithreading helps with speed issues.

I tried XL2007 both as a straight new file, and loading an older file
thereby forcing compatibility mode.
There was no difference in speed.

Bob - what magic turbo have you got built into your 2002?<vbg>
I know our coal fired machines here in Wales are slow, but for XL2000
I'm just a tad behind you, but for XL2002 you are out of sight<g>

 

[Bob Phillips]

That's just perverse <bg>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Aah, well we are nuclear powered here Roger with Winfrith just down the road
(actually, I think they may have shut that, so it must be a candidate for a
new generation reactor - anyway I digress).

Just repeated it again, 17 secs.

I must load 97 and 2003 on this machine and test them all.

I think two things are (probably clear)

- in general, Excel gets slower

- with performance measuring, nothing is absolute.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Charles Williams]

Restricting the timing to just the .value=varr() I get similar results to
Roger:

XL97 14
XL2K 12
XLXP 12
XL2003 18
Xl2007 21

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[NickHK]

Well, you have various responses; make of them what you will.

It would seem though, that upgrading for functionality may be worthwhile,
but not for performance.

NickHK