7 Replies Latest reply on Jun 18, 2017 1:30 PM by smith7180

    Performance implications of referencing or not referencing other fields in calcs.

    smith7180

      Say I have to three numeric fields:

      • Field_A
      • Field_B
      • Field_C 

      I have 2 unstored calculation fields:

      • Calc_1 = Field_A + Field_B
      • Calc_2 = Calc_1 + Field_C

      Is their any impact on performance based on these two equivalent ways of defining Calc_2:

      • Calc_2 = Calc_1 + Field_C
      • Calc_2 = Field_A + Field_B + Field_C

       

      Is it more efficient to reference calc_1 instead of spelling out the whole equation in again in Calc_2?

       

      The problem for me that is motivating this question obviously has much more complex calculations, but the issue at hand relates to currency formatting.  Long story short, if Calc_1 is formatted as a currency that uses commas for decimals and vice versa (i.e. €12.000.000,51) as is common in certain parts of the world, then my understanding from reading posts here and elsewhere is that this could cause problems if Calc_2 is definied as Calc_1 + Field_C.  I've read that FileMaker has no issues dealing with numbers like €12.000.000,51 as user input, but does have issues with this as output of custom functions.

       

      The most obvious way for me to avoid this issue is to spell out all calculationss and not reference calculations that output formatted currency.  My concern is that this will slow down the database the vast majority of my client base that does not use commas as decimals.

       

      Thanks.

        • 1. Re: Performance implications of referencing or not referencing other fields in calcs.
          philmodjunk

          "I've read that FileMaker has no issues dealing with numbers like €12.000.000,51 as user input, but does have issues with this as output of custom functions."

           

          For one thing, you don't have custom functions here. And there aren't any issues that I know of. You do however, have to respect data types. When you type a number with currency symbol into a number system, the currency symbol is pretty much ignored. But a calculation to format a number as currency often is set up to produce a result of type text instead of number and trying to treat text as though it is a number can cause problems as text cannot be sorted, evaluated or formatted as a number can be.

           

          I doubt that you will see much if any difference in performance so go with the option that is easiest to work with.

          1 of 1 people found this helpful
          • 2. Re: Performance implications of referencing or not referencing other fields in calcs.
            smith7180

            Thanks Phil.

             

            For one thing, you don't have custom functions here.

            philmodjunk wrote:

             

            For one thing, you don't have custom functions here.

            I use a custom function to format the number according to the user's currency.

             

            FileMaker basically treats 12.000.000.001,01 as ~12 when the calculation result is defined as a number on American/British machines.  If the system settings use a comma as the decimal delimiter, then it works fine with the exception (or so I have read) of calculations.

             

            If in such a locale a user inputs 12.000,01 into field a, and Field B is a calculation that results in a number that is defined as Field A*2, then they will see 24.000,02 in Field B for that record.  My concern arose out of several reports I've read of 24.000,02 being treated as 24,00002 (i.e. in American notation 24.00002) in that region as a result of a series of calculations.

             

            I'm getting that info second hand, so I don't know if it's true, but I wanted to create a system that could nonetheless handle that issue if it arose.  I believe avoiding references to my calcs that have been formatted using the Currency custom function.  However that would result in an average increase of about 50 lines of 'code' per calculation.

            • 3. Re: Performance implications of referencing or not referencing other fields in calcs.
              bigtom

              I recall ibrahim_bittar might have some experience with currency formatting. I have dealt with currencies that do not have decimals, but the ones that do I force the use of "." and so not allow ",". Might not be possible for you.

              • 4. Re: Performance implications of referencing or not referencing other fields in calcs.
                bigtom

                But to answer your original general question, I think evaluating one calc is better than two.

                1 of 1 people found this helpful
                • 5. Re: Performance implications of referencing or not referencing other fields in calcs.
                  wimdecorte

                  And is more transparent and thus easier to maintain dependencies.  I've had to troubleshoot systems where there were 8 levels deep of referenced calculations and you end up in paralysis both from pure performance but also in not being able to readily see where what is used and referenced.

                  1 of 1 people found this helpful
                  • 6. Re: Performance implications of referencing or not referencing other fields in calcs.
                    philmodjunk

                    Yet the real question is whether to use a custom function or to "spell out" the details of a calculation such that the CF is not used.

                     

                    I'm getting that info second hand,

                    Thus, before considering options, it would be nice to confirm the actual facts. What I read here seems to mix together issues of precision and issues of numeric and currency formatting.

                     

                    And if you need to not use the CF, then this could be a case where the negative side of a dependent calculation is offset by the creation of calculated expressions that are much easier to "read" and work with.

                     

                    It also makes a big difference whether your dependent calculation is stored or unstored. If it can be defined as a stored calculation, the performance issues, in terms of day to day editing of data, are minimal. If unstored, it can be quite significant.

                    1 of 1 people found this helpful
                    • 7. Re: Performance implications of referencing or not referencing other fields in calcs.
                      smith7180

                      philmodjunk wrote:

                       

                      It also makes a big difference whether your dependent calculation is stored or unstored. If it can be defined as a stored calculation, the performance issues, in terms of day to day editing of data, are minimal. If unstored, it can be quite significant.

                      I should have mentioned this originally.  The calculations are unstored, otherwise I wouldn't even worry about this issue.  I avoid unstored calcs like the plague, but when they must be used I try and follow all best practices guidelines like not showing them in list views or searching them. It cannot be avoided in this scenario.

                       

                      Assume I have 2 unstored calcs and one field from a related table "Field_1"

                       

                      Calc_1 = Sum (Field_1)

                      Calc_2 = Calc_1 + a

                       

                      Both calcs are displayed on a layout.  If I instead define Calc_2 as Sum (field_1) + a, is Sum (field_1) now being evaluated by FileMaker more times than would be the case if Calc_2 was defined as Calc_1 + a?

                       

                      Thanks everyone for your feedback, it's much appreciated.