Performance scenario analysis -IF early exist

edited May 29 in Modeling

Hi all ,

I am trying to make model not to (re)calculate in some scenarios a variable and (re)calculate only when a boolean is ticked . I did some performance scenarios analysis of calculations (counting seconds on new input ) using boolean and if statement and what i do not understand is why the calculation time after an input is not reducing when the boolean is not ticked for all records?

Example two scenarios comparing calculation time :

a) E = B+D - when i want the model to apply always solution 1, so always B

b) E=C+D - when i want the model to calculate B only when i want solution 1 to be applied

C= if "Do not Apply solution 1 ? " then A else B

A, D = functions independent of input - i expect will not be recalculated on new input?

B = function dependent of input - i expect will not be recalculated on new input when "Do not Apply solution 1 ? " is ticked for all records

C - I expect will not be recalculated on new input when "Do not Apply solution 1 ? " is ticked for all records?

E - I expect will not be recalculated on new input when "Do not Apply solution 1 ? " is ticked for all records?

This E is used in the module afterwards to get to a final calculation.

What i do not understand: calculation after new input takes almost same number of seconds when i apply formula a) (6.7 sec) as when i apply formula b) (6 seconds) with "Do not apply solution "? ticked for all records . I would have expected half of the time in b) with "Do not apply solution ?" , but this is not happening



Best Answer

  • rob_marshall
    Answer ✓


    What I think you are missing is the logic is kicked off for every cell, not just at the line item level, but every cell within that line item. There is a term we use internally that I think you are getting at, it is called redundant calcs where even if the cell value is the same as it was before the calculation, the DAG (Directed Acyclic Graph) should stop and not continue on. Engineering has been working on this and it should be released soon, not sure of the exact timeline, but it should be soon.


  • Every line item and property is populated and recalculated when the data it depends on is changed - even if it is not referenced by another line item it could still be viewed. An IF construct in a formula does calculate selectively, but only within that formula - likewise AND/OR. On-demand calculation is being worked on for Polaris.

  • @AndreeaCepoi

    It depends on the DAG, the directed Acyclic Graph, basically what is being calculated as a result of this formula (the downstream calcs), if you create a new line item for input that has nothing referencing it, the rendering time will be much faster because nothing is referencing it. I would look at the downstream calcs to see if the can be optimized.

  • Thanks alot Ben and Rob ,

    I have tested if i remove the input in B and leave it only in E and indeed the time is reduced to half. Requirement is that input should be the same for E and B, but i will try to add a process of copying into another input line item that should reference B only.

    What i still have as question mark is for point 2 below:

    1. When boolean is FALSE (let's say for all records for simplicity reasons) then the input (I) change (A does not change) generates a first recalculation in F: I→F and a second recalculation in F via I→B→C→E →F, hence understandable more calculation time.

    2. When boolean is TRUE (let's say for all records for simplicity reasons ) then the input (I) change generates only once recalculation in F, that is I→F , because due to early exist B is recalculated, but not impacting F anymore to recalculate again, nor C should not generate a recalculation in F as C =A and A does not change. Calculation time is not reduced unfortunate by keeping same input (source) I for B and F, and having the boolean ticked for all records, so how early exist works in this case?

  • Thanks @rob_marshall , yes that i was expecting , the cycle in DAG to stop on boolean defaulted to false for all cells within the line item and start re calculating C via B when was ticked by user for its selection of data assigned on his user. Happy to hear new releases will stop the cycle in DAG basis booleans.

    Thanks again, already the change of input helped me alot, i have implemented and performs very well as it decreased time of calculation to half on new user input