Excel 2007 : Excel 2007 CPU Usage

[JonnyB]

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

[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.

[shg]

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

[JonnyB]

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

[shg]

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

[JonnyB]

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.

[shg]

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.

[JonnyB]

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?

[shg]

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.

[JonnyB]

Sounds good - thanks for all your help.

[broro183]

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

[JonnyB]

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?

[broro183]

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

[DonkeyOte]

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

[JonnyB]

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

[shg]

The last full calc of the old sheet ended up taking ~165mins...just timed the new one at 28 seconds!

Whoa! good job!

EDIT: And for our listeners, thats a 300X improvemnt. Take heed.

[broro183]

Fantastic improvement!

If you're happy with the current improvement can you please mark the thread as Solved (see rules for instructions)?

However, I'm still gunning for more improvement - you can't steal the teaser of an optimisation brain teaser that easily

It's a little late in the thread for this..., but, because I haven't dealt with many large data sets, I still think 28 seconds is a long time - although, I stand to be over-ruled!
(see http://www.decisionmodels.com/optspeed.htm).

- Are all "repetitive calculations" (mentioned by DO) split into different columns, so they are only calculated once with the result cell then being referenced in subsequent formulae?
- I may be way off the mark here, but with your use of conditional summing etc, would a pivot table approach (see Jon's site) be possible?
(even if it is just as an intermediate step)

Rob

[DonkeyOte]

Echoing the others - top effort - and good on you for resolving under your own steam (even if we did miss out )

it ain't pretty and uses up a lot more cells than the original, but its lightning quick comparatively

sad but true ... elegance <> efficiency

...as well as many dynamic named range

remember that though they

a) create reduced range references

b) improve readability (shorten syntax)

Dynamic Named Ranges do involve overheads in so far as the calculations used to generate them are recalculated each time they are used.

Note also that if they DNR's are created with OFFSET / INDIRECT etc they make all formulae utilising them Volatile by association.

If we add to the above the fact that the greater majority of formulae use only the Used Range of whatever range is specified [edit: at least that is my understanding], eg:

=SUMIF(A:A,A1,B:B)

will not process all the rows of A & B - only the used range intersect of A:B

then I would say that sometimes (ie where calculation speed is of paramount importance) the performance "cost" of using DNR's can actually outweigh the benefits previously outlined.
The most obvious exceptions to the "Used Range" rule are SUMPRODUCT and Arrays (and I suspect SUMIFS / COUNTIFS though perhaps to a lesser extent) ... in such instances the use of non-volatile Named Ranges is often a no-brainer.

Needless to say it all boils down to the context in which they're being used ie:

1. which formulae they're being used with (might not be necessary)
2. whether they're Volatile (created with OFFSET or INDEX for ex.)

If you feel your model might include some of the above you could perhaps create an alternative version without them (where appropriate) and see if performance improves any further.

[teylyn]

This one should be thread of the month.

... now there's an idea! ... off to the Water Cooler ...

[JonnyB]

- Are all "repetitive calculations" (mentioned by DO) split into different columns, so they are only calculated once with the result cell then being referenced in subsequent formulae?

Yes, I do think I caught all of these. And thanks for the pivot table suggestion, I could potentially use it in the results summary, but I don't think it would apply where the conditional formulas were originally doing the heavy lifting in the sheet.

Dynamic Named Ranges do involve overheads in so far as the calculations used to generate them are recalculated each time they are used.

Note also that if they DNR's are created with OFFSET / INDIRECT etc they make all formulae utilising them Volatile by association.

Yep, this was one part of the reconstruction that gave me pause. The DNRs use OFFSET(). The amount of data input into the sheet will vary wildly...say from 10k rows to 150k or more. So the choice was either to make the named ranges dynamic using OFFSET() or make them static with more rows than I ever think I'll need, say 500k. Actually now that I think about it, I may have made the wrong choice. Just about the only function that is referencing these named ranges is INDEX() so I don't think having extraneous rows included would hurt performance at all (unless it came from increased memory demands). So by going with static named ranges, the sheet would be free of anything volatile. The obvious downside to going static of course is that if i ever exceed 500k rows, I'll need to redo all the ranges (if i remember, that is...will need to put a big red alarm cell somewhere).

Anyway, I need to sleep on this potential change. Thank you all once again. If I don't make the change tomorrow and report back with the performance difference I'll be sure to have this marked as SOLVED.

[DonkeyOte]

So the choice was either to make the named ranges dynamic using OFFSET() or make them static with more rows than I ever think I'll need, say 500k

It is possible to create DNR's without them being Volatile c/o INDEX.

Consider (for sake of example):

Data in A:C
Data type in A is Numeric
Last row of data in Col A dictates the height of the 3 Column Range being created.

Using OFFSET (all functions utilising the below DNR become Volatile by association):

=OFFSET($A$1,,,COUNTA($A:$A),3)

Using INDEX (semi-volatile - ie only on file open not thereafter)

=$A$1:INDEX($C:$C,MATCH(9.99E+307,$A:$A))
the latter will work even with non-contiguous data sets
(if the data type is not consistent there are alternatives to cater for that)

Whether or not a Volatile DNR will have a significant impact depends largely on the functions utilising it - if they are lightweight and efficient then the impact is lessened ... obviously if the opposite is true then avoiding the Volatility in the DNR becomes something of a no-brainer IMO.

To reiterate though - if the formula are relatively efficient then hard wiring a range may not be a bad idea - best way would be to test of course.

Let us know how you get on - I for one am quite interested in the performance impact (if any - and what it amounts to).

[broro183]

hi all,

Originally Posted by JonnyB
So the choice was either to make the named ranges dynamic using OFFSET() or make them static with more rows than I ever think I'll need, say 500k

It is possible to create DNR's without them being Volatile c/o INDEX.
...
Let us know how you get on - I for one am quite interested in the performance impact (if any - and what it amounts to).

I'm interested too :-)

If you have a lot of columns that you'd like to create "index based" DNR's for you may find the below macro useful. Note, the constants will need to be changed to match your file layout & the number of rows in the DNR's on the sheet are all based the last row in one column (Colno). This makes the created DNR's less flexible than DonkeyOte's version, but on the plus side, it is much quicker to set up a lot of them for a specific sheet.

DO/Shg/others,
Feel free to make comments for improving my modified version of Roger's code or if there are other major weaknesses. I've been tempted to put it in its own thread but it works "as is" so I haven't bothered.
(one day I might throw in an inputbox to get user interaction... + change the IIF structure based on comments in another thread & check the contents of the specchararr)

Please Login or Register  to view this content.

hth
Rob

[JonnyB]

Hi Everyone,

My apologies for being unattentive to this thread, I came down with a virus and didn't do much for the better part of a week. I'm finally just getting caught up on my work now.

As far as the changes to the DNRs are concerned (removing OFFSET())...unfortunately, there was no material performance difference. However, I did restructure the sheet and add some VBA code which further cut the recalc time from 28 secs to 18 secs. Which is great, but I'm noticing that of those 18 seconds, 10 of them are coming from one column. So I think I can get it down even further (I'm finally making good on providing an optimization challenge).

The attached sheet demonstrates the problem (Column C). The signal IDs in column A represent a unique pair (i.e. there are two #1s, two #2s, etc.). On the second occurrence of a signal ID (reading top-to-bottom), Column C aims to return the value from Column B when the signal ID from the current row first occurred (that is rather confusing to read - it should be made clear in the example). As you'll see the calc time is approximately 10 seconds (actually only around 8 in this sample sheet). I could live with this, but the real problem is that filling down and deleting the formulas takes forever.

[I cannot sort the order of signal IDs and have everything running on manual calc]

Would greatly appreciate any ideas -- even if the calc time doesn't improve, something that will delete faster would help a lot.

-JonnyB

[DonkeyOte]

If you can't sort the data you will not really be in a position to improve performance with formulae I'm afraid - sorting the data allows for binary search which on large data sets is hugely beneficial (only a tiny subset of the data is ever reviewed).

You could perhaps adopt a VBA based solution - ie something along the lines of:

Please Login or Register  to view this content.

I'm sure the VBA guys can improve on the above - but on your data set the above would be pretty instantaneous.

In essence it copies the existing data set (Cols A:B) to a new temp table, sorts it, applies a formula (col C) which utilises binary search against the sorted table and then subsequently overrides the formulae with results and removes the temp table.

[JonnyB]

Thanks for taking the time with your suggestion DonkeyOte. That would work nicely as you displayed...the only issue is that Column B is actually formula driven as well, so using VBA code wouldn't quite fit. I was able to come up with a non-VBA solution, and to be honest I'm not quite sure why it works so much better, but it deletes instantly. I know part of it is because the if statement reduces by half the number of times the formula is called, but they still doesn't explain all of it. In case its of interest to anyone, I've attached a sheet with the solution (had to delete a bunch of rows to get the file size down, but the gist of it is there). Thanks again.

[DonkeyOte]

That would work nicely as you displayed...the only issue is that Column B is actually formula driven as well, so using VBA code wouldn't quite fit.

I'm not really sure why the use of formulae in B precludes VBA - you would want to avoid using .Copy in the form presented but you could paste values only to the temp table either via pastespecial or by simply using range.value = range.value approach (avoiding clipboard).

I was able to come up with a non-VBA solution, and to be honest I'm not quite sure why it works so much better, but it deletes instantly.

As you say the pre-emptive IF will have a large impact - esp. with 20k rows.

OFFSET is regarded as one of the quicker functions (albeit Volatile) and INDEX one of slower functions (albeit non-Volatile) though I have to say using your sample file and the following approaches:

=IF(A6=1,"",OFFSET(C5,$D$2-ROW()+MATCH(B6,$B$5:B5,0),))
=IF(A6=1,"",VLOOKUP(B6,$B$5:$C5,2,0))
=IF(A6=1,"",INDEX($C$5:$C5,MATCH($B6,$B$5:$B5,0)))

all calculated in around 2 seconds over the 14k rows... the VLOOKUP was the slowest, INDEX second slowest and OFFSET was quickest (but being Volatile were the formulae to persist they would calculate more often than perhaps necessary).

It might be worth outlining what you're doing exactly - given you mention deleting etc ...

[JonnyB]

DonkeyOte- The problem I face with a VBA solution and sorting the entire range is that there are formula dependencies in Column B on previous values of Column C. So I cannot assume that all Column B values are known and then just sort and lookup for the entirety of Column C. I'm not sure how to get around this...I would guess that a loop would take a while. I'm not as encouraged as I first was about the OFFSET() solution...I tested on 35k rows and it was considerably slower (20 seconds).

[DonkeyOte]

The problem I face with a VBA solution and sorting the entire range is that there are formula dependencies in Column B on previous values of Column C. So I cannot assume that all Column B values are known and then just sort and lookup for the entirety of Column C. I'm not sure how to get around this...

I confess you've lost me now I'm afraid.

Your prior file(s) implied that PrevSize was derived from Size based on first instance of SignalID ... you're now implying that PrevSize is itself derived from Size - ie a circular reference of sorts.
(edit: are you perhaps saying that Size is derived from PriorSize in prior rows ?)

To be clear though - the suggestion I posted previously does not in any way affect the ordering of the original table.

The code:

i) copied the original table to a new temporary location
(given we now know formulae reside in original we would revise to a values based approach)

ii) sorted the temp table

The sorting of the data set thus permits use of Binary Search based Lookups to calculate PrevSize which will obviously have significance gain when compared to the 20k exact match tests.

Once PrevSize was calculated the code removed the formulae (references to the temp table) and the temp table itself was purged.

It would seem however that I'm missing something pretty fundamental in the process so I'll keep quiet until such time as the "penny drops" (if and when).
(edit: perhaps post an example which illustrates the relationship between Size and PrevSize - ie formulae in place re: Size)

[JonnyB]

Okay, I realize I have done a poor job demonstrating this. I hope this sheet will illustrate what I'm saying. Basically, one of the columns that you want to include in the table for binary search depends on previous values from the column that we are aiming to populate. The column titled "Exit Size" in this sheet is the one of interest.

What I have in here is the best solution I was able to come up with. The values and order of signal IDs do not change during the scenario runs, so I initially set up the sheet by having VBA calculate the offset numbers in Column C and then setting them equal to values so that deleting won't be an issue later. From there, the 'Exit Size' column uses those helper cells to do an offset. What do you think? The recalc time is now excellent ~1 second...but the set up of the sheet with VBA takes 2-3 mins...not great, but acceptable.