Model becomes slow

5.16K 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

vpluchet Answered question November 13, 2018

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.

braddo Posted new comment July 20, 2020

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…

You are viewing 1 out of 3 answers, click here to view all answers.