Hi-
I am working with a relatively large sheet (100,000 rows) and have multithreading turned to use both of my processors. I've noticed something odd when checking the resource monitor while trying to determine why it was so slow. The early part of the calculations go rather quickly and Excel's CPU usage goes up to near 100%. I believe this is good, since my understanding is that this means both processors are being utilized. However, approximately halfway through the calculations, Excel's CPU usage will not go above 50%.
Could there be something in my formulas that prevents multithreading at some point? I am only using one simple user defined function in the sheet (bindirect() for referencing a named cell using another cell's contents).
Also, with only this sheet open, I noticed that my system memory is stuck just above 2GB (i have 4gb ram)...but I've read that excel's limit is 2GB. Could this also be slowing down the calculations? If so, are there some ways to reduce this figure?
Thanks in advance,
JonnyB
As an update to the above, I've found an article that states that INDIRECT() (which my bindirect() UDF is based on) is not a 'thread-safe' function....which i believe means that it won't allow multithreading. So I have removed that UDF and replaced it with a vlookup thinking it may solve the CPU problem, but unfortunately it made no difference. It gets 47% of the way through calculations and then CPU drops to 50% and stays there until its finished.
If you're concerned about calculation time, you should rid the worksheet of all volatile functions -- INDIRECT, OFFSET, TODAY, NOW, RAND, RANDBETWEEN, CELL, and INFO
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks shg. I have done that much to try and improve things, however, I'm more concerned why I'm not using half my computer's processing capability. While one processor is at 100% the other is at 0% (or somewhere in between but the total usage is always 50% for Excel). Anyone know why that would be? Seems strange to only use half of the computing power that available
I can't help you with maxing out your CPUs, but there's not much leverage there, either; the best you could do is is a factor of two improvement.
More efficient formulas might give you a factor of 10 or 100
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Fair point. I just don't know how else to clean up the formulas...fact is that I need to use many, many sumifs() on 100k rows in order to do the job. Without a way around them, the only thing I could come up with was the CPU performance. Even a factor of just 2 improvement would save about 15-20mins per recalc of the sheet.
You may be right, you workbook may already be optimized. But we have some very good formula guys -- Daddylonglegs, DonkeyOte, Ron Coderre, NBVC, others ... -- if you can post a sanitized but meaningful sample, you might benefit.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks for the suggestion - I will give it a shot...just going to take me a bit of time to put together something reasonably sized but representative. I'm relatively new to the board so would appreciate some direction on proper etiquette - would it be appropriate to keep the example in this thread or should I start another?
In the interest of having a thread with zero replies, start a new one. We haven't covered any useful ground.
When you post an example, make sure there's enough data that it still takes a noticeable amount of time to calculate, so people can compare.
You may yet get a response in this thread on your CPU question.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Sounds good - thanks for all your help.
hi Jonny,
I'm on a really slow connection so I haven't checked the below link to Charles Williams' site but from memory it may have some useful info about UDF performance: http://www.decisionmodels.com/calcsecretsj.htm
I suggest checking out the other pages on his site as I think (?) he mentions multi-threading too.
Also, when you start a new thread, I'd put a link in each thread in case people who read one of them, may be able to help with the other aspect.
hth
Rob
Last edited by broro183; 03-11-2010 at 06:25 AM. Reason: corrected spelling
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Thanks Rob. That's a good site...haven't found anything that's made a difference for this particular project yet, but certainly learned a few things already, so well worth the read. CPU question aside, I'm starting to think that something strange is going on unrelated to the complexity of my formulas. I say this because if I try and do a simple cut and paste of a non-formula text value from one unreferenced cell to another in this sheet it takes over a minute to complete. Anyone ever experienced something like this?
hi Jonny,
Ummmm...
"Complexity of your formulae" could cause a whole lot of strife. For example, once a threshold of calculation cells are reached, Excel will try to recalculate everything more often than you expect. Does your Status Bar say "Calculate" at the bottom left of your screen?
I suspect your issue will be related to the complexity but it may not be, other possible issues could relate to...
- Do you have a lot of formatting (conditional or normal) in your spreadsheet?
- Do you have Event driven macros?
(if so, you can test if any of them are being initiated (by the cut & paste) by typing "Stop" as the first line of code in all your subs/udf's, and then trying the cut & paste. If the vbe opens up with a "Stop" line highlighted, you know something unexpected is happening.)
- Is there anything useful on Dave Mcritchie's slow response page?
hth
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
I just stumbled across this on a Vanity Search ...
I like shg and others definitely like a good optimisation challenge!
So, if you are able to put together that representative sample I'll gladly take a look as will Rob & shg - between us all we might help you improve performance.
Echoing the others and the sites recommended the rules I try and follow are:
1 - avoid Volatility (might not be an issue to begin with but you never know where things will end up)
2 - sort your data wherever possible
(this can have huge benefits - allows for binary search on matches, small subset calculations, avoidance of repetitive calcs etc)
3 - avoid reliance on Sumproduct / Arrays by virtue of "helper" calculations (and subsequent wildcard based calcs if nec.)
adding more formula to improve performance may seem counter intuitive but is often the key - more lightweight formulae will generally perform better than a much smaller no. of "elegant" formulae
Last edited by DonkeyOte; 03-11-2010 at 09:10 AM. Reason: typo
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
shg / Rob / DonkeyOte,
Thank you all very much for your suggestions. As I looked at altering the original sheet for sharing (had some business data I couldn't post), it quickly became clear that getting it into a representative but shareable format was going to be quite a task in and of itself. So I read up on the ideas you all threw out there and had a go at starting over with those concepts in mind. It took about 12 hours to do but was well worth it. The last full calc of the old sheet ended up taking ~165mins...just timed the new one at 28 seconds! From an eloquence/aesthetics standpoint it can't hold a candle to the original, but what an incredible performance difference.
As far as something valuable that others can learn from this...the sumifs/countifs were the main culprit (i still use two columns with countifs() in the new sheet and its by far the slowest section of the sheet). The new sheet gets around the conditional functions with a little sorting and then copious use of INDEX() with some helper/pointer sells...as well as many dynamic named ranges. As I said, it ain't pretty and uses up a lot more cells than the original, but its lightning quick comparatively.
Its funny how you invest all kinds of time learning how to get creative with advanced functions only to come back to the most basic (albeit with some tricks). Anyway, thanks again for your interest in this...and sorry I can't offer anything in terms of an optimization brainteaser
Last edited by JonnyB; 03-11-2010 at 10:31 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks