Model becomes slow

3.13K viewsFormulas and Functionsfunction Slow USING AS

I have a model which is not big (the main data table has around 10,000 records) but which is already a bit slow. For instance if I add a row into the “Adjustments GWP Assumptions” matrix and enter some data, there is a definite latency. Likewise if I change the number format in the GWP PCL table, it takes 1-2 seconds.
I suspect the problem is due to the heavy usage of the function Sum(Summary….) USING… AS. In the main calculation matrix, called “GWP Calculations”, the item “Adjustments” is fed from the “Adjustments GWP Assumptions” matrix through such a function. The role of “Adjustments GWP Assumptions” is to define a number of exceptions to the general growth formula used in the “GWP Calculations” matrix. There are currently 44 such adjustments, which is not many, but all the cells in the “GWP Calculations” are looking for the existence (or not) of an adjustment and I believe this is what is slowing down the model.
So I believe I need to use a different logic and discontinue the use of Sum(Summary….) USING… AS in this case. However, I do not know how else I can feed the adjustments into the “GWP Calculations matrix”. Would anybody have a suggestion?
Due to size 5MB, the model is in Dropbox:
Thanks a lot in advance

Answered question

Your model size does not look all that large to me.  I’ve much larger models and they work just as fast if not faster than what you have here.  To improve your model I would recommend

  1. Cleaning up your eclipses in your model.  Use Tools –> Problems to help you identify where these eclipses are.  You have over 80 of these.  I have seen in other models that eclipses appeared to cause a lag in performance (someone from Quantrix could chime in and tell me if indeed eclipses slow performance, but generally I have had such an experience).
  2. You have several summary items.  I would suggest you use summary items sparingly.  They are a pain–especially as your models grow in complexity.  If you can get away with it, I recommend creating summary matrices that aggregate the data in place of all of your summary items.

Using As functionality really is the power of Quantrix along with the select function;therefore, I do not think it  a performance inhibitor. All in all clean up your eclipses and reduce your summary items.

Posted new comment

Just curious – would you advise users to generally avoid using summary items in their details matrix, period? As in, no Sum of Month & Sum of Years in timelines, etc.,? It seems like a waste of a core Quantrix feature. What about if I actually *do* want to show my detail together w/ my summaries in a single view?

I’m also appreciate if Quantrix developers could weigh in and clarify whether eclipses and summary items actually impact performance significantly. It would be good to know if that were the case.

Rich, regarding avoiding summary items – is it for the reasons you & I have discussed before, their usability problems when combined with e.g. valueat() and timeline? Or are there other reasons you dislike summary items being mixed in among detail items?

As for eclipses and performance – I’d be interested to hear from Quantrix directly whether they do in fact impact performance negatively. If so, that would be good to know…


Dear Rich, I have done what you suggested – removed the Summary Items. This greatly reduced the number of eclipses. Not only has the speed returned to normal, but also the model size has reduced by one third. Impressive.

Edited answer

Dear Rich,
Many thanks for your answer, these are very helpful tips. And I’ll get rid of the annoying summaries indeed.
I’ll try to think of a better way to input the adjustments as well.
Many thanks for your help

Edited answer