Community Tip - If community subscription notifications are filling up your inbox you can set up a daily digest and get all your notifications in a single email. X
Hi,
I have a MCP sheet that pulls from some matrices to perform basic calculations that takes over an hour to complete. I would like to improve this significantly if possible. I tried to rely on built-in functions (e.g., "lookup") when possible for calculation efficiency.
The main calculation (image below) uses data from three matrices (36177x9, 2412x9, 1206x12) to perform basic +-x/ calculations resulting in a 216619x8 matrix. At the end of the sheet, there is some red text that helps point this out. File attached.
If anyone has tips for how to improve calculation speed or is able to help me optimize this process, I would much appreciate it!
Thanks!
Andrew
Mathcad Prime v9
NO multithreading
11th Gen Intel(R) Core(TM) i5-1145G7 @ 2.60GHz 2.61 GHz
24.0 GB
64-bit
Solved! Go to Solution.
Sorry, had a quick look at the sheet but did not find the time to study in more detail what the nested loops actually are doing to see if it could be achieved with less effort.
EDIT:
Got rid of the "matrix" function calls (and also of stack3 and stack5). Speed gain is about 50% or more (depends on the size of the input matrix LL)
See the speed comparison here for one call with the full matrix LL.Des:
Have not tested the new RATING2 function in the section you disabled which you say took more than one hour, but I guess it should help at least a little bit to cut down calculation time.
Prime 9 file attached
Would it help if you take the embedded Excel sheets out of the Prime worksheet into external Excel files, and just read the required data in from those external files?
Success!
Luc
Hi, @LucMeekes
That is how I have my actual MCP sheet setup, so, no that doesn't help all that much. All of the Excel components in the file I uploaded are READ into my working sheet. I embedded the data to simplify the file I was attaching.
Thanks anyway!
Andrew
Hi @Werner_E , I am curious if you see anything off or inefficient in the above calculation method?
Sorry, had a quick look at the sheet but did not find the time to study in more detail what the nested loops actually are doing to see if it could be achieved with less effort.
EDIT:
Got rid of the "matrix" function calls (and also of stack3 and stack5). Speed gain is about 50% or more (depends on the size of the input matrix LL)
See the speed comparison here for one call with the full matrix LL.Des:
Have not tested the new RATING2 function in the section you disabled which you say took more than one hour, but I guess it should help at least a little bit to cut down calculation time.
Prime 9 file attached
@Andy_C wrote:
No worries @Werner_E , thank you for chiming in!
Had just edited my reply above as I found an easy way to get rid of the (sure time consuming) calls to the "matrix" function.
The modification has at least more than halved the calculation time.
EDIT:
I turned your local function DW into a simple variable as the first argument of your function was not used at all and the second simply selected the appropriate matrix element.
However the speed gain is minimal if at all. Guess the main problem now are the various calls to the "lookup" function. Not sure if they can be optimized.
I wonder why you are using DW at all. All values are zero so it seems not to make much sense to subtract gamma.DW*0 so you could omit it at all.
Prime 9 file attached
Yes, the DW seems useless, and in this case, it is. It is in there as a place holder. It needs to be built into the function because it will be used in a future analysis. BUT, for this optimizing, it can be modified as you've shown for time savings.
Now, I will begin updating the RF function in my overall MCP sheet and begin testing there.
Thank you again!!
Thank you @Werner_E , you've done it again!!
I still have more testing to do, but when I ran the full section (disabled one), it completed with a 92% reduction in time 😂. Awesome!
I'm am still investigating, but thank you again!
Glad to hear so.
I wouldn't have expected that omitting the "matrix" calls would save that much time, though.
I agree, I was surprised as well. I am still investigating (and trying to find time).
I am not sure if and how PTC has implemented short cut evaluation of if .. else... statements.
It may be worth a try to exchange the two branches putting the most likely one (which may not be the one with result NaN) in the "yes" branch.
If the program benefits from this, depends heavily on how this if-else-statement is implemented internally by PTC, though.
I did some testing, but for this case there was only a ~1% difference one way or the other. Anyway, for the inputs I have, NaN is the least likely branch.