1 2 Previous Next 19 Replies Latest reply on Mar 9, 2017 10:30 AM by William-Porter

    Which is faster: Calculation Field or Script-calculated value entered into Field?

    user28222

      Hi everyone,

       

      Assuming the layout is complex with many conditional formatting going on, and wanting to reduce the time it takes to render the layout, I want to know which option is faster(and if so, if magnitude of the speed difference is significant or negligible ):

       

      Option A:  Field is defined as unstored calculation field in the data table.

      Option B: Field is defined as numeric/text field in the data table, and I use scripts to calculate desired value and then use Set Field command.

      (Assume I have to a lot of the above)

       

      Any advise would be greatly appreciated - FM rocks, but I am kinda stuck with these speed bumps which make an impact on the client experience.

       

      Thank you.

        • 1. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
          alecgregory

          It's not really possible to say, caching and a load of other factors will come in to play. An unstored calculation may be cached, the result of a Set Field calculation in a script step will never be cached, so you may end up needlessly re-running the calculation everytime you visit a particular record. The time it takes to run the calculation in a field definition would be pretty similar to running it in a script step in one-off terms.

           

          FileMaker layout optimization doesn't really come down to one thing and it's difficult to retroactively apply.

           

          Some guidelines are:

           

          1) Try to avoid conditional formatting where possible, it slows down layout rendering as the formatting can't be re-used once initially loaded in the way styles in a theme can.

          2) Avoid local formatting (ensure every object uses a saved style that is part of a saved theme)

          3) Avoid rendering unstored calculations when the layout loads if possible. You can do this by putting unstored calculations in popovers or non-default tabs / panels. That way they are only loaded when the user implicitly requests them by clicking the tab, panel or popover

          4) Avoid unstored calculation fields in list and table views, FileMaker will calculate many of them at once which can slow things down significantly.

          5) Consider having more layouts with fewer objects on each layout

           

          This is very general and barely scratches the surface but might be enough to get you started. Layout Optimization is a big topic. There may be some useful DevCon sessions up about it.

          • 2. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
            siplus

            I assume you are talking about a layout showing the data of a single record.

             

            The calculation approach is IMHO always better than a script. Basically what you are asking is whether the calculation engine is faster than the script engine, all else being equal (like cond formatting acting on field changes, which might need to be refreshed when value is calc'ed via script).

             

            However the fact that you reached such a critical point is something that needs further pondering per se.

            • 3. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
              jbante

              The important difference between unstored calculation fields and stored fields set by scripts is less about how fast the calculation is than about when the calculation happens and control of when it happens:

              • An unstored calculation re-evaluates whenever it's needed. FileMaker decides when that is, not you or your users. You can only control evaluation by learning what events will trigger re-evaluation of a calculation and working around those events. Maintaining that knowledge and designing an application to work around it can be a pain in the butt, but you don't have to design for the updating of unstored calculations yourself.
              • A script runs when you tell it to, and is less likely to accidentally run (as with a script trigger you forgot about) when you don't tell it to. Displaying a stored field on a layout or referencing it in a conditional formatting calculation do not trigger re-execution of the script that set the field. Keeping track of what fields set by script need to be updated when can be a pain in the butt, but you don't have to worry about inadvertently triggering the re-evaluation of unstored calculation fields.

              In general, any calculations that can evaluate practically instantly are reasonably safe as unstored calculation fields. The more complicated the logic is for a calculation (especially if it draws on data in related records), the more reasonable it is to start with a script instead.

               

              To get more specific to your situation, what do your conditional formatting calculations look like? Having fewer of them certainly may improve the render times for your layouts. But sometimes a lot of conditional formatting calculations are really nice to have. If the conditional formatting calculation is complicated, it may not matter if the referenced fields are unstored calculations or stored fields set by scripts. When I need to have lots of conditional formatting and eek out every bit of speed I can, I'm inclined to make a field (stored, set by script) containing just True (1) or False (0) for each condition, which minimizes how much calculation effort needs to happen at render-time.

              • 4. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                wimdecorte

                siplus wrote:

                 

                I assume you are talking about a layout showing the data of a single record.

                 

                The calculation approach is IMHO always better than a script.

                 

                I lean the other way and will prefer a scripted approach to setting values instead of unstored calcs.

                 

                But as stated they both have plusses and minuses so there is no dogma here and there is no catch-all answer.  Always consider both approaches.

                • 5. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                  siplus

                  I do consider both approaches, but if I have to run a script on every RecordLoad to calc values pertaining to that specific record and related records + trigger refresh cond formattings individually or with a refresh window  then there's something wrong about the whole design.

                   

                  Unfortunately we don't know what OP's users ask from him, but his coming here with a layout rendering speed problem has to be evaluated at a level deeper than "what's faster", as far as I'm concerned.

                   

                  We know exactly when unstored calcs are triggered, and we - not Filemaker - decide if and when they impact performance. It happens only when they are referenced - and that includes being in the visible part of a layout.

                  You can have 1000 unstoreds in your table, it's fine as long as you don't use them.

                  • 6. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                    keywords

                    Apart from the useful discussion so far, there is a third approach you possibly should consider—auto-enter calculations. This is neither script nor unstored calculation; it is just numbers or text, which can be stored. If the fields need constant re-evaluation it is possible to set script triggers to force this to occur as and when needed. This is closer to your option B, except that it only requires a short script, or something similar, if re-evaluate if needed.

                    • 7. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                      wimdecorte

                      siplus wrote:

                       

                      I do consider both approaches, but if I have to run a script on every RecordLoad to calc values pertaining to that specific record and related records + trigger refresh cond formattings individually or with a refresh window then there's something wrong about the whole design.

                       

                      Agreed; that's not a good design.  What I'd be looking for are opportunities to set the data statically in the workflow of entering data so that I would not have to rely on triggers etc.

                       

                      In essence the design decisions revolve around:

                      - without calcs you need to make sure that the data refresh does trigger when needed

                      - with calcs you need to make sure the data refresh does not trigger when not needed

                      • 8. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                        taylorsharpe

                        Unstored fields will frequently be recalculated in a layout and updated.  I'm not sure FM's frequency, but there are times that is nice.  Other times, it makes your layout really sluggish, especially if you have a number of them.  So if you have a sluggish layout, look for a script calculation that happens once as you load the record or layout over an unstored calc.  Also, saving info into fields and updating indexing can take time.  If you can make these calculations global variables and display them on the layout that way, you will also save some file write times because you are only writing those values to variable cache. 

                        • 9. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                          Stephen Huston

                          Unstored calcs in list view will force record caching from the server worse than almost anything else (except maybe webviewers in list view using different sites for each record).

                          Try opening to a simpler layout view if possible, otherwise much of that loading time is unavoidable.

                          • 10. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                            jbante

                            siplus wrote:

                             

                            We know exactly when unstored calcs are triggered, and we - not Filemaker - decide if and when they impact performance. It happens only when they are referenced - and that includes being in the visible part of a layout.

                            You can have 1000 unstoreds in your table, it's fine as long as you don't use them.

                            Some of us understand what operations will and won't trigger the re-evaluation of an unstored calculation field, and we can design our solutions to trigger these re-evaluations when we want as an effect of the design. That's "deciding" when a calculation impacts performance like crossing the street only while the cars have a red light is "deciding" not to get run-over.

                             

                            Many of us do not know what operations do and don't trigger re-evaluation of unstored calculations. Learning is always good, but designing systems to be robust to a lack of learning is also good. Even for those of us who do know, it's easy to trigger an unstored calculation accidentally, and it can take significant effort to diagnose a performance bottleneck caused by such an accident in a complicated application. Finding the worst offenders in a slow-rendering layout using many unstored calculation fields is a messy process.

                             

                            You can have 1000 unused unstored calculations in a table with negligible performance impact, but who has 1000 unused fields in a table? Presumably a field is created because someone intends to use it.

                             

                            Writing similar logic into scripts comes with it's own challenges. For example, if I'm working on an application I'm not familiar with, it could take some doing to find all the scripts and other processes that set a stored field I'm troubleshooting. But scripts are less likely to have the kinds of performance problems that are difficult to isolate that unstored calculation fields are susceptible to.

                            • 11. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                              CarlSchwarz

                              user28222 wrote:

                               

                              Hi everyone,

                               

                               

                              Option A: Field is defined as unstored calculation field in the data table.

                              Option B: Field is defined as numeric/text field in the data table, and I use scripts to calculate desired value and then use Set Field command.

                               

                               

                              Thank you.

                              If you need to recalculate the fields every time you update the layout then don't bother storing the data.

                              If the data doesn't change often then store the fields when required.  That will prevent the unstored calcs from recalculating when the layout is being rendered and after any trigger that makes FileMaker want to recalculate those fields.

                              • 12. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                                siplus

                                IMHO it's a good idea to use the comment: in the field definition to store info like "calculated by script xxx", if you go the script calc way. It's a PITA to see a text/number field, maybe with a hard to understand name, and have no idea who and when modifies its value. And make sure those fields are not on layouts where they are not updated, risking to give wrong info to the users.

                                • 13. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                                  user28222

                                  Wow.  I sincerely appreciate all the answers from the experts here - the quality of learning I am getting here is such that it is like getting a high-quality course for free!

                                   

                                  I was away from the forums due to client requests for small fixes to the application I mentioned, but I want to respond to all the suggestions made here.

                                   

                                  As the client's demands are ever-changing and iterative, I didn't want to share the specifics of the design to bias the answers; this is why my questions were couched in very high-level general terms.  However, if I may add a little more specificity, it is essentially a list with roughly 600 rows and 100 columns.  There are a lot of labels and lines that depend on the computed values in the 600x100 grid, for example lines are drawn only when the cells within the grid belong to the same group; likewise for labels.  The 100 columns are stored with repetitions (e.g. column[1]-column[100]) as this is the only way I can manage how they are referenced in my script.

                                   

                                  Please pardon any errors I make in the following summary of learning:

                                   

                                  1a.  Unstored calculations will not be triggered if layout does not include the field directly.  This is one way to control the rendering speed(by avoiding triggering.) 

                                   

                                  1b.  True or False?  Unstored calculations will not be triggered if underlying data used for the calculation does not change and even if the field is included in the layout?

                                   

                                  2.  Scripted calculations offer the most control on when to trigger or not to trigger - as it is most directly/explicitly specified in the code script, but not knowing how Filemaker operates under the hood, I certainly was open to the possibility that unstored calculations may have faster speed - it is sometimes 'nice,' as Taylor pointed out, since it is very convenient and auto-magically done provided the relationship is clearly specified (parent/child keys)- so I went out on a limb hoping maybe Filemaker built in performance enhancements as well...which leads to my next point(#3)

                                   

                                  3.  One unit of computation for unstored calculation is roughly equal in speed to the same unit of computation done via scripts, but as Taylor pointed out, there may be additional overhead associated with time it takes to save a value from script into the table, as well as any subsequent indexing.

                                   

                                  4.  Conditional formatting and hiding rules are very time-consuming in general but here we are at the mercy of the client's request to draw reports with very specifically outlined layout (by the way the layout does look beautiful when fully rendered. )  I appreciate jbante's sympathetic understanding of this aspect!  I also love his idea of a 'pre-computed' set of True/False values to be used for the conditional formatting during render time.  This might be the key boost I was looking for - I will report back to everyone on whether this leads to significant improvement.

                                   

                                  5a.  Record caching for list views with unstored calcs is likely the biggest culprit(maybe more so than conditional formatting mentioned in #4) - thanks to Stephen Huston for this!

                                  5b.  If my solution was in a LAN setting, would record caching be less of performance degrader - my understanding is that it is just data dump from the server to the client so if the LAN ethernet connection is 'fast enough' it would not be so bad from a user experience point-of-view?

                                   

                                  6.  Keywords' advise on using auto-create fields is pretty cool - which would only calculate 'once'.  BTW how does one re-trigger the auto-create calculation via script if needed?

                                   

                                  7. I will heed siplus's advise to always document what is being one- things can easily get convoluted and hard to track when project grows in complexity.

                                   

                                  8.  Finally, I acknowledge wimcorte's guidance that there is no one-best-way to implement a FM feature - as always, it takes creativity and effort (and in my case more learning) to solve the problem.  It is not out of the realm of possibility that I might have to start the whole thing from scratch if I really have to solve the lag issue but I am grateful I can make tangible improvements as a FM developer!

                                   

                                  Thanks to the great teachers - you all should have online courses or books written.

                                  • 14. Re: Which is faster: Calculation Field or Script-calculated value entered into Field?
                                    jbante

                                    user28222 wrote:

                                     

                                    1b. True or False? Unstored calculations will not be triggered if underlying data used for the calculation does not change and even if the field is included in the layout?

                                    False. Since the last result isn't stored anywhere to reference if the data haven't changed, the computer still has to go back to the source data to re-evaluate.

                                     

                                    user28222 wrote:

                                     

                                    3. One unit of computation for unstored calculation is roughly equal in speed to the same unit of computation done via scripts, but as Taylor pointed out, there may be additional overhead associated with time it takes to save a value from script into the table, as well as any subsequent indexing.

                                    Unstored calculation fields cannot be indexed. Otherwise this is a reasonable way to understand it.

                                     

                                    user28222 wrote:

                                     

                                    6. Keywords' advise on using auto-create fields is pretty cool - which would only calculate 'once'. BTW how does one re-trigger the auto-create calculation via script if needed?

                                    Auto-enter field calculations set to over-write existing contents will re-evaluate when a referenced source data field (in the same table occurrence) is modified, or when the field itself is modified. Presuming that the field doesn't reference itself, you can just set it to an empty string "". Please include a comment saying that triggering the auto-enter calculation is why you're setting it to an empty string when you do this. This behavior is a little paradoxical the first time it's seen by a developer unfamiliar with your system.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next