1 2 Previous Next 17 Replies Latest reply on Apr 12, 2014 9:43 AM by philmodjunk

    Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?

    Donovan_1

      Title

      Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?

      Post

           It looks like I have some performance issues. Editing a table containing some heavy calculated fields - some of them using ExecuteSQL() - introduces some noticeable lag.

           I changed the Calculated Fields to STORED and the editing was lightning fast.

           As I do not expect those calculations to be subject to lots of changes, could I let them be STORED and is there a command to force recalculation of these STORED calculations in case I know something changed that influences these fields?

        • 1. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
          philmodjunk

               Stored calculation fields will update automatically when the value in a field referenced by the calculation is changed.

               The key exception to that is that calculations that use a Get ( Function ) won't automatically update in a stored calculation. Thus, if you use Get ( CurrentDate ) the calculation must be unstored if you want the calculation to always use the current system clock date in its calculation.

               Likewise, auto-entered calculations will not automatically update when data in a referenced field from a different record or table is modified.

               And calculation fields that, on the other hand, refer to related records or global fields cannot be defined as a stored calculation in the first place.

          • 2. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
            Donovan_1

                 OK, but suppose I do set a Calculation Field to Stored. Is there a command in to Update the stored values other than setting it to Unstored and back to Stored?

            • 3. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
              philmodjunk

                   To repeat, there's no need to do that. They will update automatically.

              • 4. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                Donovan_1

                     Are you sure? I am looking at the difference between stored calculation fields and unstored calculation fields.

                     I have the feeling, once I store the calculation field by setting that option, they do not change anymore.

                     I was trying to see if there would be a command to force the recalculation so that new values were stored.

                     And will this cascade?

                     When you say the stored calculation fields are also updated automatically, does this mean that the overhead of getting an updated value moves from the usage of the fields with unstirred calculation fields, to the data entry of the fields the calculations are based upon?

                • 5. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                  philmodjunk

                       I am sure. (been using FileMaker since version 2.5 many years ago. wink)

                       Here's the difference: Say that you have 4 fields, A, B, SumStored and SumUnstored in your table. Sumstored and SumUnstored are defined as:

                       A + B, but SumUnstored has "do not store..." specified in storage options.

                       when you modify or enter a value into either field A or B, SumStored calcluates a new value and stores it. SumUnstored does nothing.

                       But when you pull up that record on a layout, Sumstored simply displays the stored value, SumUnstored must first add the two values before it can display the same value. This also affects finds and sorts as a find or sort that specifies an unstored calculation field must first evaluate the field in either every record in the table (Finds) or every record in the found set (sorts). But there is no such delay for the stored calculation field.

                  • 6. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                    Donovan_1

                         OK then I think I've encountered a problem with the usage of ExecuteSQL. I think FM loses track of table/field relationships due to SQL. My SQL based Calculation Field did not recalculate.

                         Can you confirm this?

                         As a matter of fact I have the feeling that SQL based calculation fields are treated as related to everything. Hence if you have a couple of them and perhaps you sum them etc., FM will re-execute the SQL a zillion times while displaying them in the UI. It makes the UI EXTREMELY slow.

                    • 7. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                      philmodjunk

                           ExecuteSQL, is fairly new--introduced in FileMaker 12. Like calculation fields that use a Get function, it does need to be unstored to update automatically in many situations.

                           But I did a simple test with a table that had just two fields: field1, field2 and discovered an interesting trick to get it to be a stored calculation but still update automatically.

                           If I set up this calculation field as a stored calculation:

                           ExecuteSQL ( "Select Field1 From \"ESQLUpdateTest\" WHERE field2 = 'Apple' "; ""; "" )

                           it would not update if I edited the value of Field1 and Field2.

                           But if I enclosed it in a Let function set up to "trigger" a recalculation if field1 or field2 was modified, it would update automatically:

                           Let ( Trigger = Field1 & Field2 ; ExecuteSQL ( "Select Field1 From \"ESQLUpdateTest\" WHERE field2 = 'Apple' "; ""; "" ) )

                           I thought of this trick as it is similar to a trick used to force an auto-enter calculation to update under certain circumstances.

                      • 8. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                        Donovan_1

                             Hi Phil. Yes, that was exactly the solution that I had in mind. But my SQL is complicated. So I probably need to think which fields to use in this dummy assign in the LET statement. Basically you need to involve all fields, also the fields that you use in the WHERE clause of the SQL.

                        • 9. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                          Donovan_1

                               I was thinking, perhaps I could create a Custom Function like this:

                               ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator; listOfSQLArguments; listOfDependentFields)

                               ..., which would call ExecuteSQL and basically ignore the listOfDependentFields parameter.

                               I could then do something like this:

                               ExecuteSQLAutocalc
                               ( "SELECT e.salary + d.bonus FROM Employees e, Departments d WHERE e.empID = ? AND d.deptID = e.deptID";
                                 ""; "";
                                 empID;
                                 List
                                 ( Employees::salary;
                                   Departments::bonus;
                                   Employees::empID;
                                   Departments::deptID;
                                   Employees::deptID
                                 )
                               )

                               Perhaps that is already enough?

                          • 10. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                            Donovan_1

                                 Perhaps it would even be enough to add them to the argument list of Execute SQL:

                                 ExecuteSQL
                                 ( "SELECT e.salary + d.bonus FROM Employees e, Departments d WHERE e.empID = ? AND d.deptID = e.deptID";
                                   ""; "";
                                   empID;
                                   Employees::salary;
                                   Departments::bonus;
                                   Employees::empID;
                                   Departments::deptID;
                                   Employees::deptID
                                 )
                                  
                                 Obviously for this example it would be faster to just use FM relationships. But if you need to do tricky things like UNION or multiple NOT EXIST or aggregate functions on fields from different tables, then this might just work.
                                  
                                 Will test this.
                            • 11. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                              philmodjunk

                                   Both ideas seem very easy to try in a simple test. Please post your findings back here if you choose to test them.

                              • 12. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                                Donovan_1

                                     I just tested a simple setup.

                                     Table1
                                     - Field1 NUMBER
                                     - Field2 NUMBER

                                     Table2
                                     - Field1 NUMBER
                                     - Field2 UNSTORED CALC: ExecuteSQL("SELECT SUM(Field2) FROM Table1"; ""; "")

                                     The numbers of Table2.Field2 did not update when I added records to Table1.

                                     I created a relationship between the two, but that didn't help either.

                                     Then I changed the calculation to: ExecuteSQL("SELECT SUM(Field2) FROM Table1"; ""; ""; Table1::Field2)

                                     And then the values were calculated dynamically.

                                     So this is the trick to have these values update if the calculation is unstored.

                                     UNFORTUNATELY, I could not set the calculation field to STORED, because if I do, FM claims it cannot store calculations that use referenced fields. If I delete the relationship, FM complains that I cannot use the field, because there is no relationship.

                                     So this trick does not work to make the SQL stored?

                                     Could it be that what you said about stored calculations is only valid if the fields are form 1 table? To me it looks like I cannot have stored calculations where the calculation uses referenced fields?

                                • 13. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                                  philmodjunk

                                       What you encountered when you tried to make this a stored calculation has nothing to do with ExecuteSQL. It's standard for all calculation fields you might define in FileMaker. If the field references a field from a related record or a field with global storage specified, it cannot be defined as a stored calculation field. This is the case for any calculation field.

                                       Please note that my original test calculation did not reference field from other tables nor a global field for that reason.

                                       Your options are to leave the calculation as an unstored calculation or replace it with a data field that is updated via scripts performed by script triggers etc.

                                  • 14. Re: Performance issues - how to tackle? Can I force re-calculation of STORED calculated fields?
                                    Donovan_1

                                         Hi Phil, yes you are right. 

                                         What I do now is I keep the calculation field, but before I enter the layout where I use them, to avoid performance issues, I copy the calculation fields to corresponding normal fields that act as cache.

                                    1 2 Previous Next