Apologies if this is in the wrong forum...
I currently use a Core2 Duo PC and often max out the both CPU for extended periods when recalculating large (30MB+) spreadsheets.
Wondering if anyone has experience with Quad Core desktop PC's and large files and if there is a noticeable / significant performance improvement with more cores..
Ram doesn't seem to be an issue.
Any feedback welcome.
Last edited by Jbentley; 12-20-2009 at 08:28 PM.
A 30 mb workbook seems very large, is there a reason it is so large, usually such a large workbook means there are other issues.
hi,
I agree with Dave, there are probably other issues & here are some links about potential issues which make interesting reading...
For general speed issues have a look at www.mvps.org/dmcritchie/excel/slowresp.htm
I suspect that it's the actual formulae within your file that is slowing things down. Do you use any/many Sumproduct, [ctrl + shift + enter] Array formulae, or Indirect formulae etc...?
I suggest reading the below & all the pages that are listed in the menu across the top of each of the below pages on Charles' site:
http://www.decisionmodels.com/calcsecrets.htm
http://www.decisionmodels.com/optspeed.htm
hth
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Hi,
Thanks Dave & Rob - will take have a good read of Charles' site.
One of the main reasons the files are so large is due to dealing with a couple hundred thousand rows of customer details & marrying up behavoural metrics to form customer classes / clusters.
I do use a lot of Sumproduct, & more recently (since moving to '07) a lot of SumIFs, AvgIfs etc. Guilty of DSUM, DAverage too - but don't use these that often.
I avoid inter file formulaic references, but do use a lot of intra file references.
Sometimes the file size is unaviodable - and am thinking it may ultimately be a question of sheer CPU grunt.
Thanks for the advice,
Justin
Have you considered using a database to store the customer info and then pulling parts of it into excel when needed?
Hi jrdnoland,
Yeah, I do use db, forgot to mention that up front.
The dataset is a small subset of customers in a datawarehouse (+1M unique customers)
Generally mining the warehouse is Ok, but sometimes I need to work around the referential intergrity of the various tables to build new, temporary connections between customers, customer data and other less tangibly connected datasources. Excel has the greatest flexibility for this.
Cheers,
JB
hi Justin,
I haven't used them at all myself, but I think I've read somewhere that Dsum & Daverage may be "better" than other approaches in some situations.Originally Posted by Jbentley
Which ever formulae you do use, always (?) try to limit the range that the functions have to refer to (ie only the used range rather than full column references).
hth
Rob
Apparently Excel 2007 is the first version to support multithreading. I can't find any benchmarks to compare, though, in general, quad core processors are very noticeably faster than duo cores in multithread applications. I'm not saying twice as fast, but maybe 60-70% faster?
Found one,
\1
from Multi-Core Confrontation: Core 2 Quad Q6600 vs. Core 2 Duo E6850
Last edited by jevery; 12-20-2009 at 12:58 PM.
hi all,
Jevery, here are some links discussing multi-threading in Excel...
http://www.pcreview.co.uk/forums/thread-3731633.php
http://www.mrexcel.com/forum/showthread.php?t=297987
Probably the most useful below & note that some Excel functionality doesn't support multi-threading...
http://www.decisionmodels.com/calcsecretsc.htm
(I recommend reading the links on this site too)
hth
Rob
Last edited by broro183; 12-20-2009 at 07:17 PM.
Hi,
Thanks Rob & Jevery,
Rob: I used to use Dimensional Aggregates a lot (before I wrapped my head around Sumproduct) but found that, for some reason I couldn't fathom, that string criteria wasn't being assessed correctly. eg. I had "Act" & "Actx" as criteria then results for "Actx" were also included in results for "Act".
Jevery: Cheers! - hoping to build a case for IT dept to spring for a Quad core for me - this will add to conversation.
Thanks to all that have added comment and advice to this thread.
I think I've enough to go on now - so am marking as SOLVED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks