12 Replies Latest reply on Apr 21, 2017 3:48 PM by Malcolm

    Designing for Performance with Calculated Fields

    Malcolm

      I wonder what generates the greatest load on FMP when using calculation fields. Should we re-use calculation fields within other calculation fields or should we reference the original data fields? Here is an example that I'm borrowing from another thread.

       

      I have  two  fields:

      • c_Concept:  If ( Expense  = "Variable Expenses" ; Concept ; "")
      • c_Amount:   If ( isempty ( c_Concept  ) ; "" ; Amount  )

       

      For ease of maintenance, this method is very good. It is only necessary to update the field c_Concept.

       

      The alternative is to define both fields using the evaluation.

       

      • c_Concept:  If ( Expense  = "Variable Expenses" ; Concept ; "")
      • c_Amount:   If ( Expense  = "Variable Expenses" ; Amount ; "" )

       

      Putting aside the issue of maintenance, which method is best suited for good performance? Especially when we start creating other calculation fields or summary fields based on c_Amount?

       

      Malcolm

        • 1. Re: Designing for Performance with Calculated Fields
          philmodjunk

          It's not whether the calculations reference other calculation fields or not, but whether the calculation is stored or unstored and if unstored, whether it computes an aggregate value from large numbers of related records or not, that makes the biggest difference.

           

          If all the calculation fields are stored values, whether they compute values from the original data fields or from another stored calculation field will make no difference when finding/sorting records nor when computing an aggregate value such as a summary field sub total from such data. If they are unstored, then every record has to re-evaluate every time you pull up that record onto your layout in the found set.

           

          You might see a slightly faster update of values computed within the current record when you modify a value with Calculation fields that calculate from other stored calculation fields as the number of math operations will be slightly different, but more likely, the difference will be too small to see.

           

          The one place where there might be a noticeable difference in performance is when importing large numbers of records into such a table as the stored calculation fields have to evaluate in each newly created record as part of the import process.

          1 of 1 people found this helpful
          • 2. Re: Designing for Performance with Calculated Fields
            Malcolm

            In a chain of stored calculations, you think that referencing the previously stored calculation field may reduce the load.

             

            On the other hand, in a chain of unstored calculations, referencing other unstored calculations does not have that advantage as the calculations all need to be performed.

             

            Presuming my original question was referencing unstored calculations, do you think there are performance differences?

             

            Malcolm

            • 3. Re: Designing for Performance with Calculated Fields
              philmodjunk

              Recall this part of my last reply?

               

              You might see a slightly faster update of values computed within the current record when you modify a value with Calculation fields that calculate from other stored calculation fields as the number of math operations will be slightly different, but more likely, the difference will be too small to see.

               

              That was for a single record given the assumption of stored calculations. Using unstored calculations, the same logic applies, but now if you have a large found set of records or an aggregate value being computed from such a large found set, those small differences might be enough to make a noticeable difference.

               

              Even within a given calculation, you can use the Let function to streamline calculations in complex expression by computing intermediate values once that might then be used more than once in the same calculated expression.

              1 of 1 people found this helpful
              • 4. Re: Designing for Performance with Calculated Fields
                Malcolm

                thanks for your responses @philmodjunk. Often these questions are moot but I've recently started working with a system that was developed by a naive programmer. The record sets are reasonably large and the extent of calculated nesting is considerable.

                 

                Malcolm

                • 5. Re: Designing for Performance with Calculated Fields
                  bigtom

                  I recall somewhere it was mentioned that Case() evaluates faster than If(), but that may have been for multiple If().

                  • 6. Re: Designing for Performance with Calculated Fields
                    user19752

                    Someone wrote unstored calculation field never be cached, so I think the latter may be faster anytime if they are unstored.

                     

                    Tested on FM15 with these fields

                    usc2 return 1, so it looks usc1 in usc2 is re-calculated always.

                    1 of 1 people found this helpful
                    • 7. Re: Designing for Performance with Calculated Fields
                      Malcolm

                      I expect usc2 has to be evaluated everytime but your example is flawed. In your example, the value of $$n is being controlled by usc1. Look at this alternative. When the calculation in usc1 is changed to use $$n, without defining it, the increment in usc2 is displayed in the data viewer. Any activity in the record causes a re-evaluation of $$n causing it to climb quickly.

                      definingUSC.pngUSC.png

                      • 8. Re: Designing for Performance with Calculated Fields
                        user19752

                        I'm not sure this is better than mine. This shows usc1 is updated after usc2 is calculated. But I tried to show when usc2 is calculated, usc1 in calculation is recalculated. I use resetting global variable for it.

                        I expected calculation occurs sequentially usc1, ucs2, then stop (when there is no action in the window) when changing f1, but I agree this is guessing and can't get when usc1 is recalculated.

                         

                        Using Get(CurrentTimestamp) or something could show it clearer, but I'm not sure these function have special effect for always recalculate.

                         

                        Using individual variables for both fields looks better. Then monitor them in data viewer.

                        One click "refresh values" increment all +1 each time. (I want this is not refreshing usc1 itself, at least on screen both fields are not redrawn.)

                         

                        As you wrote, on window action that increment very quickly.

                        2 of 2 people found this helpful
                        • 9. Re: Designing for Performance with Calculated Fields
                          Malcolm

                          I tried to show when usc2 is calculated, usc1 in calculation is recalculated. I use resetting global variable for it.

                          That is clearer to me. What you are showing is that the usc1 is evaluated in usc2 and that it takes place after the evaluation of the let statement, resets the value of $$n to 1 and that is the value taken by usc2.  Very good.

                           

                          Malcolm

                          • 10. Re: Designing for Performance with Calculated Fields
                            wimdecorte

                            There's another area where calculated fields affect performance: record creation with stored calcs.  When the record is created the stored calcs will fire (unstored won't unless they are referenced / visible), so depending on what the calcs need to do the act of creating records may be slow.

                             

                            From a code 'smell' point of view, calcs that reference other calcs are more difficult to track down and leave more room for error.

                            1 of 1 people found this helpful
                            • 11. Re: Designing for Performance with Calculated Fields
                              siplus

                              Calcs depending upon other calcs are to be avoided IMHO if you have an alternative to it.

                               

                              - You prevent cascading calc overhead

                               

                              - You can change the calc of a field / revert its boolean output logic without inciding on anything else

                               

                              - You can delete a field you don't need anymore without breaking functionality of the whole system

                               

                              etc

                              2 of 2 people found this helpful
                              • 12. Re: Designing for Performance with Calculated Fields
                                Malcolm

                                We'll have to watch out for that in this solution. The cascade of field dependency bounces all over the place.