PC upgrade advice

G

grantx

Senior member
Messages
2,331
Likes
223
Excel performance is really sluggish on some sheets due, presuambly, to size and number of calculations. To improve this I could split the sheets but this is not ideal.
Please refer to the specs of my pc below, and suggest compatible options for upgrade(s) which would give a significant boost.

I suspect there are tweaking/overclocking possibilities but I'm wary (don't know what I'm doing).

Whether relevant, I've also attached a shot of Task Manager showing the number of processes running. Note that Ace-WINscreen.exe is not usually running – it was used to capture the image.

Thank you.

Grant.

AMD Athlon 64 X2 Dual Core 4800 processor, 2.41 Ghz
Asus A8N-SLI Deluxe motherboard
Sapphire Radeon X800 GTO 512 MB DDR2 graphics card
2 x 1GB Kingston KVR400X64 DDR RAM
Enermax Liberty 500wv2.2 psu
Value Antec P180 Super Midi case
Samsung SP2504C hard drive
Running on Windows XP Pro x64, version3, SP 1

I also have a spare (new) Western Digital Caviar SE16 SATA 2 which may be used to form an array.
 

Attachments

  • y.doc
    40 KB · Views: 396
grantx said:
Excel performance is really sluggish on some sheets due, presuambly, to size and number of calculations. To improve this I could split the sheets but this is not ideal.
Please refer to the specs of my pc below, and suggest compatible options for upgrade(s) which would give a significant boost.
Click to expand...

Can't see you should have any probs with a dual core 4800 (I use one as the back-bone of a hard-core gaming rig) + you're running 32bit excel which, iirc is multi-threaded + it's only using 42,072k of mem (heavy, but certainly NOT excessive, i got one using 126k as we speak)

Strongly recommends you recheck the spreaddy particuarly wrt the use of volatile functions
 
agreed, that shouldn't have any problems with the CPU and the RAM you've got plumbed in there, so I'd look to Excel as the culprit. Can you run the sheet on another machine and see if it gives similar crappy performance?
 
Profitaker said:
Grant

Any iteration used in your sheets ?
Click to expand...

Good advice so far - they only thing I can add is that if you do have a lot of data in your spreadsheets then perhaps adding more memory would speed things up a little. But I would look at the source of the problem before spending money as I'm not sure how excel works. I believe XP places limits on the amount of memory a program can allocate so adding more memory might not help at all....
 
Gentlemen,

Performance is probably worse on Excel 07 compared to Excel 02.

A sheet is 8,135k, consisting of 13,500 rows x 40 columns containing tick data of DOM to 10 levels on bid/ask – bid and ask price, bid and ask size.

From this I construct 1-minute data of all the fields producing 845 rows. From the 1-minute data I use basic calcs - change from previous, total bid/ask size, etc. I’ll also construct various frequency/distribution tables consisting of array formulas, containing around 8 columns.

Functions used are =COUNTIF and =SUMIF.

I’ve actually separated the 1-minute into an separate file out of necessity rather than choice and will use this for initial analysis.

I know there are various freeware prog’s that can examine various aspects of a pc. But is there any that can identify specific areas of weakness.

I don’t know if this relevant but from System Properties, Advanced, Performance, Settings, Advanced, Virtual Memory, Memory usage (best performance) is Program (as opposed to System cache); Total paging file equals 4093 mb (Visual Effects is selected for Adjust for best performance).

I’ve also attached a shot of Performance from Task Manager.

PT,

Could you be more specific re iteration?

Grant.
 
Grant

Any function that is using For / Next / loop as part of it's code, in other words any continuous counting process. I have a DDE Option price feed into Excel and use an iteration process to solve for implied Vol. It uses an awful lot of CPU, such that I have to severely limit the number of strikes I can evaluate.

In Excel go to > Tools > Options > Calculation > and uncheck the Iteration box. See if that makes any difference to your speed ? If it does then try reducing the Iteration maxima.
 
PT,

See what you mean. No, there are none. Also, Iteration is unchecked.

Grant.
 
Have you got any RAND() functions in the spreadsheet?

I run a 25MB, 30,000-row model on a PC far, far lower spec than that and it's fine. I've noticed though that large numbers of RAND()s causes Excel to slow right down beyond a certain point.
 
Black,

There’s nothing like rand(). The calcs are basic maths (addition, subtraction division) plus =COUNTIF and =SUMIF, some contained within arrays.

This is an example of a cell:

=IF($AZ6<MIN($B$4:$B$20001),"",VLOOKUP($AZ6,$B$4:$AP$20000,22))

In this particular case this is from a table 800 rows x 40 columns (32,000 cells).

The following are example cells from a Price, volume distribution table (1000 rows):

number of trades {=FREQUENCY(AQ4:AQ20000,BO5:BO10000} (this is part of an array)

total volume =SUMIF(AQ4:AQ20000,BO5, AR4:AR20000

buys =IF(BT5=0,0,IF(BT5<0,ABS(BT5),IF(BT5>0,BQ5+BS5)))

sales =IF(BT5=0,0,IF(BT5<0,BR5-BQ5,IF(BT5>0,-((BQ5/2)-BT5))))

net =SUMIF(AQ4:AQ20000,BO5,AT4:AT20000)

buy/sell ratio =IF(BT5=0,0,IF(BS5=0,0,BR5/ABS(BS5)))

buys % =BR5/BO$3

sales % =BS5/BO$3.

If it is the case the sheet is over-burdened, I suppose we have three options: translate all formulas into a vba module; upgrade; do both.

Grant.
 
grantx said:
Black,

There’s nothing like rand(). The calcs are basic maths (addition, subtraction division) plus =COUNTIF and =SUMIF, some contained within arrays.

This is an example of a cell:

=IF($AZ6<MIN($B$4:$B$20001),"",VLOOKUP($AZ6,$B$4:$AP$20000,22))

In this particular case this is from a table 800 rows x 40 columns (32,000 cells).

The following are example cells from a Price, volume distribution table (1000 rows):

number of trades {=FREQUENCY(AQ4:AQ20000,BO5:BO10000} (this is part of an array)

total volume =SUMIF(AQ4:AQ20000,BO5, AR4:AR20000

buys =IF(BT5=0,0,IF(BT5<0,ABS(BT5),IF(BT5>0,BQ5+BS5)))

sales =IF(BT5=0,0,IF(BT5<0,BR5-BQ5,IF(BT5>0,-((BQ5/2)-BT5))))

net =SUMIF(AQ4:AQ20000,BO5,AT4:AT20000)

buy/sell ratio =IF(BT5=0,0,IF(BS5=0,0,BR5/ABS(BS5)))

buys % =BR5/BO$3

sales % =BS5/BO$3.

If it is the case the sheet is over-burdened, I suppose we have three options: translate all formulas into a vba module; upgrade; do both.

Grant.
Click to expand...

Hi Grantx,

Go to TOOLS / OPTIONS / CALCULATIONS tab and disable auto calculations.

You can press F9 to carry out manual calculations.

See if your CPU and ram usage goes up then. Should do.

Then you'll know if it's your formulas that's the prob.:idea:
 
Actually, maybe ,as it happens.....

Start -run -type in msconfig select start up, click disable all.

reboot. manually open programs you want running... and lets face it, stay off them porn sites...... :LOL:





note, check it out, ive no idea whats required to be enabled but ive clicked mine off and its ok, and you can always click enable all......... if you dont like it...... :)

ohh, ohh look at me daddy I'm an I.T. engineer, yeeee haaaaa....
 
Last edited:
Hello Atilla,

I’ve already disabled the auto calc.

Did as you suggested and the CPU indicator on Task Manager went to around 50-52%. So why doesn’t it use more of the resources? CPU indicator returns to 0% so I presume the draw from other processes is negligible.

Grant.
 
grantx said:
Hello Atilla,

I’ve already disabled the auto calc.

Did as you suggested and the CPU indicator on Task Manager went to around 50-52%. So why doesn’t it use more of the resources? CPU indicator returns to 0% so I presume the draw from other processes is negligible.

Grant.
Click to expand...

Could be an interrupt request. Even clicking your mouse repeatedly will slow CPU down.

However in Task Manager if you click on the CPU column header it will list your services in order of top users.

Do you have many devices connected to your PC like printer/scanner/fax cameras mp3 players etc etc?

It is normal for CPU to peak at 100% and maybe stay there for a few seconds.

The problem is if it stays there constantly.

Try disabling Automatic Updates.

For some reason on my machine I realise it really puts an over head and slows internet and market maker down. Without it I can run mulitple tasks and listen to mp3 or watch movies all at the same time.

Having said that once a month I enable it get the updates whilst doing nothing else and then disable it again.
 
CB,

Before I do as you suggest, I copied the Services contents from the System Configuration Utility. Please see attachment (1 page).There's got to be something here that I don't need - telephony, anything network related (I don't run a network), etc.

Grant.
 

Attachments

  • System configuration utility.doc
    45.5 KB · Views: 262
hi Grant, when you get to the start up tab and select disable all, then reboot, and your machine will, but it wont load all the junk, you might want to boot up your anti virus or if you can see it in the start up list after you have diabled everything, then just re enable that one program to load up.

Again , you can always do the same function and re select enable all, all those programs you disable will still be in the tab waiting to be enabled...
 
grantx said:
CB,

Before I do as you suggest, I copied the Services contents from the System Configuration Utility. Please see attachment (1 page).There's got to be something here that I don't need - telephony, anything network related (I don't run a network), etc.

Grant.
Click to expand...

Suspects IMO

ATI HotKey Poller - Suspect, probably uses interrupts for keyboard requests
.NET Runtime Optimization - I only have this once. Do you have 2 diff versions?
NBService for Nero AG - NetBios very chatty MS nonsense

Start by disabling - unchecking them.
 
the funny thing is when you check disable all, and reboot, and it reboots quicker because it loads less, therefore less running etc, is, you wonder what those 30 items were running in the first place ? :)
 
DB, Atilla,

I ran an experiment. On the Excel sheet, I selected an area and set a Find and Replace, changing 1 cell reference (20001 to 20000). With DB’s Disable All, and subsequent Enable All, the time for both, changing 67,280 cells was 45 seconds. CPU Usage was c 50% until completion.

Automatic updates is already disabled. There is a printer connected but it’s turned off.

Atilla,

Just read your latest. Will do as you suggest (after I’ve eaten). And Presumably there will be no harm if I maintain DB’s Disable All.

.NET Runtime Optimization. My 2 your 1, what’s it for anyway?

Seeing as the only 64-bit prog I have (but don't use) is Excel 07, maybe a change to 32-bit windows also?

Grant.
 
You must log in or register to reply here.

Similar threads

R
New PC or upgrade?
Replies
14
Views
4K
zambuck
Z
Top