6 Replies Latest reply on Apr 1, 2013 11:22 AM by DaveRawcliffe

    Stored Calc vs. Stored Text/Number Field


      One of the fun features of FileMaker are the calculation fields since they are not apart of most SQL databases. We all know the achiles heel of calculation fields being the unstored calculation field. But I have wondered about optimizations. Would doing a lookup or auto entered and stored normal fields performance be faster than a stored calculation field? Do they perform the same or, if not, which is the faster way to go. Basically, I was trying to figure if to optimize things I should also avoid stored calculation fields or not. Thanks for any input of your experiences.

        • 1. Re: Stored Calc vs. Stored Text/Number Field

          Hi Taylor,


          Stored calculations are stored just like data and in fact changing a stored calculation to data plants the data ( thus 'stored' ).  The only reason auto-enter of data is better than stored calculation is when you need to change the value manually after its calculated value has been entered or you need to use a concatenation validation technique.  Auto-enter can possibly not work as expected, such as an import where 'perform auto-enter' is forgotten where a stored calculation will always be dependable and will update when its referenced ( local ) fields change.  Of course a stored calculation can lose its index, so  ...


          Techniques which plant data are used more often now because triggers provide easier control to ensure completion but they usually replace unstored calculations.  Stored calculations reference local fields only and can't include global or unstored calculations ( as you are aware ) so they will update and remain stored and in sync based upon the fields referenced within them  - they are the silent workhorses.  FMI discussed whether to keep both when 7 came out and, for backward compatibility and their subtle differences, it was decided to keep both.


          Stored calculations can be optimized by controlling their indexing ... most times unchecking 'auto' and checking minimum ( on text fields ) or setting even to None and uncheck auto ( unless needed ).  This will help keep file size down otherwise the index will creep upwards ( not tested in 12 ).  I would focus on replacing the aggregate and other unstored calculations but I would  wouldn't worry at all about stored calcs. 


          Message was edited by: LaRetta - changed 'would' to 'wouldn't' ... big difference.

          • 2. Re: Stored Calc vs. Stored Text/Number Field

            Climbing out on a limb ... as for speed ... it would seem to me that it would take more resource and be a bit slower to use auto-enter, which needs to set the data in the field, over a stored calculation which evaluates and stores the result but does not actually plant the data.  This is only speculation and I would be interested whether anyone has tested.  I would bet that HOnza, Jeremy, Wim and others have probably been there; done it.  It's actually been on my list for over six months ( testing auto-enter from parent versus auto-enter from grand-parent ).


            Great thread - thanks for bringing it up.  Each updater and version of FM seems to present speeds a bit differently and only by retesting after each one, can we know which is best. 

            • 3. Re: Stored Calc vs. Stored Text/Number Field

              Thanks for your input LaRetta.  I'm always interested in learning more about FileMaker under the hood so that I can better design things. 

              • 4. Re: Stored Calc vs. Stored Text/Number Field

                I would add to the question.


                Once a calculation is created it remains as metadata, toggling between auto-enter & calculation uses this calc. If the field is changed to a manual or scripted entry the calc definition is retained though not used and can be reestablished later (years).


                Thus the question, does this retention have any impact on optimization, or …

                • 5. Re: Stored Calc vs. Stored Text/Number Field

                  The short of it, the calculation is stored in the field definition, even though it's not being used if the field is not accessing the calculation engine.

                  David Rawcliffe wrote:


                  Thus the question, does this retention have any impact on optimization, or …

                  • 6. Re: Stored Calc vs. Stored Text/Number Field

                    Thank you Joshua.


                    This has been one of those niggling little items that needed scratching.