13 Replies Latest reply on May 19, 2016 10:39 AM by erolst

    Performance cage fight: Stored Calcs vs Static Fields - Who wins?

    jmedema

      A colleague and I are having a performance-related disagreement about the relative merits of two different field types: Stored Calculation fields versus Static Fields.

       

      Here's some of what we know:

      • Fields that have a field type of Calculation can be stored IF the values in the calculation are pulled from functions (i.e. Get ( CurrentDate ) or fields within the current table.
      • If a calculation references data stored in any other tables - related or not - the calculation cannot be stored and is considered an "un-stored" calculation. Unstored calcs take longer to render and can be a performance hit on larger solutions, over WANs, etc.
      • Both Calculation fields and regular Text, Date, Time... fields can be indexed and used in relationships

       

      My preference is to create regular static fields and use the auto-enter feature in field options to populate the field. In a relatively recent FileMaker Talk podcast I heard one of the Matts (Matt Navarre, I think) say that he's eliminating the use of calculations wherever possible, which bolsters my opinion here.

       

      Screen Shot 2016-05-17 at 9.40.49 AM.png

       

      My colleague's preference is to create a Calculation field because he likes seeing the actual calculation in Manage Database > Fields.

       

      SO, the question is this:

      If the two field options listed below were in a cage fight based on performance (rendering speed, etc.), who would win and why? Are there any other benefits to one field type over another aside from performance that should affect our decisions on which one to use? (The fields below have the same resulting value and, no, I wouldn't create a solution with both of the fields since I only need one)

      Screen Shot 2016-05-17 at 9.55.13 AM.png

       

      What do you think?

        • 1. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
          DamianKelly

          I am not sure that there is much in with regard to Auto Enter calc fields and straight calc fields. Auto enter calc obviously let you edit should you wish.

           

          The big performance gain for me is moving away from unstored calcs

          • 2. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
            DamianKelly

            Also if you define a field as a calc then change it to, say, a number then go into Auto Enter calc your original calculation is already there. So I wonder if underneath they are similar?

            • 3. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
              DavidJondreau

              I'd be surprised if there were any difference at all.

               

              "Unstored calcs take longer to render and can be a performance hit on larger solutions, over WANs, etc."

               

              It's a fine point, but this is not accurate. The relationship between storage and performance depends on context and your definition of "performance". For example,

              1) Unstored calcs by themselves do not take any longer to evaluate. It's the act of pulling data from a related table that causes the slowness. If you had an calc = 1 or = $$some.variable, a user would be unable to tell if the calc was stored or not .

              2) Is performance the user's perceived experience when viewing the data? When changing dependent data? When making a new record? There's a lot that goes into it. An unstored calc that references frequently changed related data and is viewed rarely and in a single record view should probably stay unstored for performance reasons, while a calc field that is view in a list, and references rarely changed related data should probably be stored.

               

              And now, with button bars, we now have some more options!

              1 of 1 people found this helpful
              • 4. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                smith7180

                Jim Medema wrote:

                 

                My preference is to create regular static fields and use the auto-enter feature in field options to populate the field ... My colleague's preference is to create a Calculation field because he likes seeing the actual calculation in Manage Database > Fields.

                I must be confused on this point.  It sounds to me like you're saying they're somehow equivalent.  I thought:

                • auto-enter calcs are only calculated on record creation
                • Stored Calcs update when they only reference fields from the same table
                  • IRC, The only time I've manually set stored calcs to unstored is if their value depends on the time (ie of day) or something (am I incorrect in recalling that a stored calc that = current time does not remain current? it's been a while). 
                • Calcs referencing non-local data must be unstored.

                 

                They seem totally different?  I used auto-enter calcs when I want a local calculation to be strictly historical. 

                 

                I use Calcs if the data is local, but should updated if a referenced field changes (for example, a simple mileage to kilometers converter).

                 

                Unstored calcs I use whenever I need them, but I almost never display them.  I always have a corresponding static field that serves as the 'cache' for the unstored calc.  Then, via scripts, I 'cache' the unstored calc.

                • 5. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                  DamianKelly

                  Auto Enter calcs will recalc when 'Do No Replace Field Contents(if any)' is unchecked and a field used with in that calc in the same table as the field changes. I think.....

                   

                  Google 'Shaking the dependency tree' by Darren Terry, its one of those things I would commit to memory if I were not so forgetful......

                  • 8. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                    DamianKelly

                    Spoon feed them Beverley why not!!!

                    • 9. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                      Mike_Mitchell

                      Every calculation imposes a performance penalty. The penalty is assessed whenever the calculation is evaluated. So you can start there: When and how often does each one evaluate?

                       

                      As noted above, if you uncheck the "Do not replace" checkbox, an auto-enter calculation will re-evaluate whenever any of its predicates changes. Otherwise, it only evaluates on record creation or if a predicate changes that causes it to be non-empty. A calculation field will always re-evaluate whenever a predicate changes. So that's one difference.

                       

                      An unstored calculation refreshes whenever it's needed. That can include when a window refreshes (if the result is on screen), or when it's called via script or other reference. So an unstored calculation will evaluate probably the most frequently of all - unless you have a calculation field (stored) whose predicates change frequently.

                       

                      Also, the "distance" of the predicates will affect the performance, because, as noted above, such results are always unstored - but will evaluate more slowly than an unstored calculation based only on local data. This has to do with record fetching; local data are already fetched, but data in related tables may not have been.

                       

                      All that to say: There's no easy answer. The difference between a stored calculation field and an auto-enter calculation, all else being equal, is probably nonexistent or negligible, assuming you're re-evaluating the auto-enter. If you're not re-evaluating, then the auto-enter will be faster because it evaluates only once.

                       

                      HTH

                       

                      Mike

                      1 of 1 people found this helpful
                      • 10. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                        beverly

                        1. I think I'd rather see a Ladders, chairs and tables match.

                        2. I routinely print the table/field definitions (to pdf) to see the auto-enter calcs. My calcs tend to be long enough that just showing in the Manage Database -> Fields makes them truncated to be worthless. So I end up printing to see them anyway. The pdf is searchable and I can quickly see what field needs to be revised.

                        3. I like to use Set Field (buttons and scripts) more than Calculate (including auto-enter). So, fields that are Auto-enter can be overwritten (with permissions, of course) as I deem necessary. Calcs require setting fields to re-trigger the calc and not my favorite cup of tea.

                        4. There may be times when a calc is the bees knees.

                        So, given all the feedback on this topic, there can be variations of the methods that work well.

                        beverly

                        • 11. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                          karina

                          In the Fullname-case it's better to use an auto enter.

                          When the name First or the Name last field changes the full name fill will also change.

                          So no need to use an calculation. When you want to search the full name field an auto enter is also better.

                           

                          When we can or need the history we use an auto enter but when we need a total of for example invoice lines we use an calculation.

                          • 12. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                            ninja

                            beverly wrote:

                             

                            1. I think I'd rather see a Ladders, chairs and tables match.

                            I think it might be a three-way tag...but keep the TLC theme...

                             

                            While autoeneter/replace and unstored are already in the ring...what about triggering scripts to SetField?

                            This can be done so that the re-evaluation (revaluation?) happens even if it isn't on the same table (assuming you triggered it that way)...and triggering over a WAN still only needs to happen if you are changing the data.

                            • 13. Re: Performance cage fight: Stored Calcs vs Static Fields - Who wins?
                              erolst

                              karina wrote:

                              In the Fullname-case it's better to use an auto enter.

                              When the name First or the Name last field changes the full name fill will also change.

                              As far as automatic update or indexing is concerned, it doesn't make a difference; but using an auto-enter for a datum that should only be changed at the source(s) is conceptually wrong, and potentially dangerous – you don't want there to be the slightest chance that fullNames deviates from the result of concatenating the name fields.

                               

                              So IMHO, it's not better – on the contrary.