How to profile? How to find performance bottlenecks in your model?
Hi,
In the real world Monte-Carlo-Modell, the model is growing. Still relatively simple, but a full recalc takes about 1.5 seconds. This seems not very long, but when you want to do 10’000 recalcs, it starts to matter.
The model was much faster, before I added some strange formulas, joining data from different matrices. I’m sure there will be more efficient ways to do this. But before starting to optimise, it would be good to know where exactly time is spent in the recalculation of the model. In most programming language, there is a way to find out where the calculation spends its time.
My question: is there any technique to profile the model, e.g. to find out where my model spends time to recalculate?
I found some 4 year old discussions on that maybe “eclipses” cost time, as well as summary items. Is there any progress on this, e.g. is there a list of “expensive” techniques / errors. E.g. use multiple selects in the same formula with nested “and” clauses (as discussed in this post), so I would be interested to know if this might be an expensive technique.
Thanks, Gilbert
Thank you very much Brian!
Ben gave me some hints where I might lose a lot of time. I tried to restructure my model accordingly, and it now runs significantly faster. My findings:
- Do not use INDIRECT. (yes, I knew. But I still had INDIRECTs at some places for convenience).
- Do remove all your INDIRECT statements. (If you think I already said that: true. I just wanted to make it absolutely clear that this is of uttermost importance)
- The more dimensions the better. If you can collapse different matrices in a single matrices using several dimensions, do it!
- All other things did not seem to matter! I could not find a difference using helper matrices or not, nor did I feel that complicated SELECT statements do influence performance.
- I’m still not sure if “eclipses” harm performance, but these should be cleaned anyway as keeping your model clean.
With this modifications, I could reduce my calculation time from about 2 hours down to 10 minutes, which was quite a nice increase!
Hope this helps others in my situation!
Gilbert