Excel Performance Question...

[Larry]

I am using an add in that allows me to pull data directly off of my AS400 and format it using all of the functionality that Excel can provide. However, it appears that the performance has dropped drastically when using this add in. For instance, I may have four columns with VLOOKUP's ranging to about 2000 rows and the refresh time of these calcs may take upwards of 20-30 minutes.

Does anyone have any suggestions for increasing my performance levels?

BTW, the addin is by a company called SAMAC. Does anyone have any experience with this tool as well?

Any information will be greatly appreciated.

[Aladin Akyurek]

Consider also using DGET instead of VLOOKUP.

[Jack in the UK]

you are pulling loads on ODBC over as400 will work but heavy work, if all in one go.

also 3 col of vlookup will slow the calculation time tyhe more rows the slower.

suggest vlookup in col by col to power excel in speed, will only save a little but and nmean more work for you..

excel is struggling...

reduce the workload.

fix???

Try to download via as400 ONLY what you need filter in ms Quest the non required data this will help most of all.

[Mark W.]

On 2002-05-07 13:49, Larry wrote:
...I may have four columns with VLOOKUP's ranging to about 2000 rows and the refresh time of these calcs may take upwards of 20-30 minutes.

Does anyone have any suggestions for increasing my performance levels?

What's your VLOOKUP "hit" rate? How is the table_array organized? What type of range_lookup are you performing?

[Jack in the UK]

What's your VLOOKUP "hit" rate? How is the table_array organized? What type of range_lookup are you performing?

does this matter we all know:
I am using an add in that allows me to pull data directly off of my AS400 and format it using all of the functionality that Excel can provide

so the chip and memory power the cal rate, and over as400 ODBC the DNS will allow traffic at max rate but processing will be as the PC will cope with..

regardless... it will snore, lower sopec PC longer it not rocket science.

the addin might not help but if i saw this post without i would still give the set sane answer. OVER COOKED it and so will take time.

fixes:

nove to NT4 SP6 and resources will be reallocated to the correct memory stopping debugs and dirty memory XP will improve the issue a bit but this not the same as NT treagrdless what we are told.

add more memeory will help but over 102-8 in Win95 or 98 and the proformance drops off we all know this

we need to know the PC spec, but regardless the answer is more of less bar a few tweaks gineric..

its slow as over cooked for PC processing power.. we could inprove the formula a bit, but what increas do we expect...

i stand by what i say/// i do ODBC ever day tp rip live data but i rip only what i need...

[Mark O'Brien]

Jack,

The speed of the machine, the operating system and version of Excel will all affect the speed, that is true. However, getting a bigger, better, faster machine/OS/XL is not a good programming solution.

I think Mark W's question is with regards to the variables that affect (effect?) the speed and efficiency of VLOOKUP. Which is really getting to the route of what Larry is asking. (BTW "Hi Larry")

Cheers


EDIT:: ...and Mr Akyurek's (one of 5 non-US MVP's out of eleven ) suggestion, I guess, is an attempt to get away from an inefficient VLOOKUP.
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-05-07 15:50 ]

[Jack in the UK]

true i have said need to look at the formul and tweak, but cal speed is like a formula one car horse power counts or as the US cubis inches count. airodonmanics are the edge.

a lorry with 10 liter wioll hit 155 and slow down 15 liters at 155 it will slow only getther fater airodynomanics count at the top ebd ... this work is not top end, but AS400 odbc need tyo work via bespoke dispatch rate..

sounds like all in one hit....