1 2 Previous Next 16 Replies Latest reply on Feb 8, 2015 5:24 PM by CarlSchwarz

    Calculations - best practices?

    flybynight

      Just looking to gather some feedback from the seasoned devs out there. To calc or not to calc? To store or not to store? Indexing?

       

      From some of the reading I have been doing, I get the impression that "calculation fields are the devil!" and should be avoided at all cost.

      It seems to be an issue of convenience (plain old calc field) vs performance with dependency and other considerations with auto-enter calcs, scripted calc routines, etc. I prefer to handle most things in a way that isn't script or layout specific, although there are times when the workflow calls for that with script triggers, etc. I just don't want to create a situation where it can easily be forgotten to copy a script trigger to a different layout. I'd rather it be handled in schema. Seems more bullet proof to me, but correct me if I'm wrong, please!

       

      Is it a matter of how big a database gets, for choosing your method? Or are calc fields to be avoided at all costs, no matter what? Do you only use auto-enter calcs? And why?

       

      Just trying to learn from the pros and spread knowledge. If you have any other resources you would point me to (I'm sure this discussion has already happened numerous times), that works too. Or if you have a DevCon session that you would recommend I check out, I'll be there too. I would check out the "SHAKING THE DEPENDENCY TREE" session, but I have watched the video from last year, and that is one of those blocks with 4 or 5 sessions that look good to me, so I might do a different one.

       

      Thanks!

      -Shawn

        • 1. Re: Calculations - best practices?
          flybynight

          Another thing that I thought of…

          With complex calcs involving a lot of fields, and possibly a lot of Case statements…

           

          In the past, I had broken the calc up into "parts" using different calc fields (back in the v6 days, before I knew about auto-enter… or any optimizing at all really), then I would have 1 final calc field that pulled it all together. This made it more "readable" for me and made trouble-shooting easier, because I could easily see which part of the calc was functioning correctly. I didn't have Advanced back then, so I didn't have the data viewer.

           

          From the seasoned vets, I see heavy use of the the let statement in calcs (including auto-enter calcs). That seems to take care of the readability issue, and also I'm told it reduces the amount of work the calc engine has to do if some parts need to be calculated/referenced more than once. You just don't necessarily have the feedback for troubleshooting the parts, but you can use the data viewer for that.

           

          Anyway, sorry I'm rambling… but would welcome any thoughts on methodology there are welcome.

           

          Thanks!

          -Shawn

          • 2. Re: Calculations - best practices?
            Mike_Mitchell

            Shawn -

             

            I would say the "never use calcs" folks are probably off the mark. Like most everything else in FileMaker, there's more than one way to accomplish the task, and rarely is there only one "right" way.

             

            For example, Bruce Robertson's excellent Virtual List technique is now part of the standard training program. It won't work without calculation fields. Would that be an example of "never" using calculations? Probably not.

             

            Of course, like anything else, there's a "better" way and a "not so good" way. You allude to the "old" practice of using multiple calculations that each feed into each other in a chain for readability (and a version of modularity) in the "old days" before Let. Now that you've read up on the dependency tree, you know that's really not a good way to do things. In addition, it increases the bandwidth needed by FileMaker when it loads a record from the server, which hurts performance. So it's not a question so much of "calculation fields are bad", but rather, optimization.

             

            And, of course, there are appropriate times to use Script Triggers or Conditional Formatting instead of calculations. A good example might be from, in the old days, we used to use a calculation field to change the color of a field label (using TextColor, or, before that, use a calculated container field). That would add weight to the schema that wasn't needed unless you were on that layout, slowing things down, bloating the database, and generally cluttering things up. Now, you would use Conditional Formatting.

             

            With regard to some of your other questions:

             

            No, I don't only use auto-enter calculations, though I do use them heavily. Unstored calculations I use a fair amount (such as for the aforementioned Virtual List). Stored calculation fields I probably use the least, simply because there aren't that many situations where I need them. However, I'll use a stored calculation without qualm if it fits the situation. Keeping in mind, of course, that every stored field you add to a table consumes bandwidth and hurts you on performance, especially on a slow network connection (see: 3G).   

             

            Hope that's helpful in some way.

             

            Mike

            • 3. Re: Calculations - best practices?
              mikebeargie

              With the advent of "Perform Script on Server", we will most likely be seeing a shift away from "realtime" calcs as much as possible as the next step for performance gains on the FileMaker platform. Given it's infancy, there's not too many breakthroughs yet, but once you start using it on your own, you'll get a lot of "aha" moments pretty quickly.

               

              The "cost" of calculation fields has become more apparent as FileMaker Go and WebDirect were introduced.

               

              There's an excellent devcon session that Wim Decorte has held in the past about WAN performance that has been excellent in techniques to use for performance.

               

              Then again, if you have a file that lives on a device or inside of a LAN, then you can probably ignore most considerations with calculation performance (but you are not exempt, as poor development will show through on large datasets or even small datasets with too many calculations).

               

              If you'll be at devcon, I highly recommend the following sessions:

              weds 9am - greg lane's session for perform script on server.

              weds. 3:45pm - darren terry's calculation dependency session

              • 4. Re: Calculations - best practices?
                Order_from_Chaos

                Is there also a performance hit for relatively simple stored calculations that only reference fields in the same table? I do use these not infrequently but try not to use calculations with more complex references. I have not personally created a database with > 350,000 records though and I have yet to try and create a project that tried to operate on GO over a cellular network but have considered this.

                • 5. Re: Calculations - best practices?
                  Mike_Mitchell

                  Every stored field in a table represents data that have to be downloaded to the client in order to load the record. Depending on the network speed, and the amount of data included in each field, you can definitely impact performance by adding fields to a table.

                   

                  Further, the processing overhead involved in updating calculations can, in some cases, result in performance degradation. This can happen if you create a dependency chain where one calculation depends on the results of another calc, which depends on the results of another, and so forth. You can inadvertently do this without realizing it, and your performance will suffer. Or, of course, if your calculation is complex and requires multiple evaluations to complete.

                   

                  So under normal circumstances, the performance impact of simple stored calculations will be slight. However, it can add up quickly, so try not to use too many and use them only where there's a benefit versus some other method.

                  • 6. Re: Calculations - best practices?
                    wimdecorte

                    All calculation types carry a penalty.  Stored calcs typically have it at record creation and when one of the dependency fields gets updated.

                    Unstored calculations are re-calculated whenever they are drawn on a layout/tab panel/popup...

                     

                    The rule of thum is to only calculate once if you can.  Having the same thing being calculated over and over does not make sense.  Take historical sales data for instance.  As soon as a sales period is closed, any aggregations of that can be calculated (server-side nightly script,...) and the aggregated data becomes real static data.

                    • 7. Re: Calculations - best practices?
                      wimdecorte

                      Mike Beargie wrote:

                       

                      With the advent of "Perform Script on Server", we will most likely be seeing a shift away from "realtime" calcs

                       

                      With the big caveat that mroe so than ever before, the server has to be chosen and "dimentionsed" with this extra load in mind.  And the projected extra load in added features / added users.

                      The deployment can no longer be an afterthought but has to be factored in the early design of a solution.

                      • 8. Re: Calculations - best practices?
                        beverly

                        + 1

                        Screen redraws with calculated fields and summary fields can be painfully slow for reports.

                         

                        SQL dbs use raw data (for the most part) and only calculate if you query as such explicitly or use an app that does the calculations just prior to presentation. I try to keep that in mind when determining good practices for data as stored in FM, too! I do a lot of db-served websites and this just seems to speed things up, there as well.

                         

                        Beverly

                        • 9. Re: Calculations - best practices?
                          beverly

                          "use" is the question here. It depends on how they are used...

                          • 10. Re: Calculations - best practices?
                            Stephen Huston

                            I think it depends mainly on what the calc is used for. I will give a real world example from a business solution which has 60,000 invoice records, averaging around 8 line items, plus tax rate info.

                             

                            When viewing any one invoice, there is no meaningful delay in the result of the unstored calc which totals the related line item records at the invoice record level. Even the unstored tax amount, based on the unstored line item total is seamingly instantaneous.

                             

                            Now switch to a year-to-date summary report sorted by Invoiced Total Amount per Customer ID, reporting 20,000 invoices for the current year, and watch the report fail to redraw for most of a minute on a LAN connection, or maybe 20 to 30 minutes over a WAN connection.

                             

                            If the same Invoice Total and Tax Amount fields are stored numbers set via script triggers when each invoice is edited (OnRecordCommit), those same reports have no calcs, just summary fields based on stored numbers at the Invoice table level, and all reports are very fast.

                             

                            My rule of thumb is, if you can store the results, do so. Yes, there are exceptions, but your reporting experience will be better with stored results.

                            • 11. Re: Calculations - best practices?
                              Mike_Mitchell

                              It seems as though the discussion has gotten a bit sidetracked from the original question (should you use calculation fields) to a more specific discussion of summarizing results in found or related sets of records for reporting. While this is certainly a valuable and valid topic (and everyone who has commented is correct in recommending the use of stored data where possible / practical), that's not the only purpose or use for calculation fields.

                               

                              Other uses for calculation fields include:

                               

                              1) The aforementioned Virtual List technique.

                               

                              2) Concatenation of fields into a single field (either for display or for concatenated key fields).

                               

                              3) Transfer of field data from a related table to the current table (using an auto-enter calculation, sometimes with modification, as a substitute for a lookup).

                               

                              4) Logging of field changes without invoking the script engine (e.g., Ray Cologon's UltraLog).

                               

                              5) Echoing of a related field into a portal row (obsolete, but still needed on rare occasion).

                               

                              In some cases, these techniques can be replaced by scripting (and should). However, should we at least discuss them to answer Shawn's question more fully? For example, I've seen some claims (not personally verified) that relational performance is improved by using concatenated key fields instead of multi-predicate joins. Is that true, and, if so, should they be considered? Or, along a similar line, do Cartesian joins slow a solution down compared to using a constant calculation field (such as a "1") in all records and using that for a similar purpose?

                               

                              It just seems to me the question is a little broader than just summarizing results for a report. Or am I missing something?

                               

                              Mike

                              • 12. Re: Calculations - best practices?
                                Stephen Huston

                                That's why I started with:

                                I think it depends mainly on what the calc is used for.

                                Until we know what specific need the field fills in the overall solution, trying to decide on a general rule about whether a calc is the best way to go is just a shot in the dark without a target.

                                 

                                My examples were meant to point out that some things that used to only be possible with calcs are better handled differently now that we have a good range of script triggers. I still use calcs, and, even more often, auto-enter by calculation fields regularly. It depends mainly on what the calc is used for.

                                 

                                My general rule of thumb is to avoid unstored calcs if there is a risk that field will need to resolve to run a report.

                                1 of 1 people found this helpful
                                • 13. Re: Calculations - best practices?
                                  flybynight

                                  Great stuff, everyone. I was purposefully keeping the purpose of the field generic.  I'm not looking to solve a specific problem… Looking for multiple use-case scenarios, examples of when one technique would be favored over another.

                                   

                                  Also love hearing how the thinking has changed. How and why methods that "we used to do X" have changed to "we now do Y" - either due to trial and error optimizing, or due to new methods available in recent versions.

                                   

                                  A big THANK YOU to all who have contributed!

                                  -Shawn

                                  • 14. Re: Calculations - best practices?
                                    timwhisenant

                                    To a hammer every problem looks like a nail.

                                     

                                    The real answer must stem from the problem at hand and the calculation engine is simply one tool in the box.

                                     

                                    The more tools in the box, the more angles from which to attack the problem.

                                     

                                     

                                     

                                    This exercise, tool selection, makes design/development an art as-well-as a science.

                                     

                                     

                                     

                                    No tool should be totally avoided, experience and judgment determines tool selection.

                                     

                                    A good example is repeating fields. Before FM was relational they made line items possible, but now this use is less than desirable. However, as Mike has referred to the virtual list technique, it relies on this tool for its flexibility and power. So repeating fields have good uses and not so good uses.

                                     

                                     

                                     

                                    My2cents,

                                     

                                    Tim

                                    1 2 Previous Next