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: https://www.dropbox.com/s/scqc6ho6tl75qbb/KSA%20Budget%20example.model?dl=
Thanks a lot in advance
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
- 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).
- 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.
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
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.