11 Replies Latest reply on Sep 24, 2012 2:48 PM by Hudi

    summary field performance issue

    Hudi

      I have a layout that has no summary fields on it. But when I navigate to that layout it gives me the dreaded dialogue box telling me that FM is summarizing a field. This doesn't happen on other layouts that are based on that same table.

       

      I am thinking of two options

       

      1. There is a field on my layout that is triggering some summary recount.

      2. Its a hidden field the previous developer left to brighten my day.

       

      What am I missing?

       

      Thanks!

        • 1. Re: summary field performance issue
          Stephen Huston

          Check your field definitions for all Summary - Calculation field types. I am almost certain one of them is using a Sum (sorted by field) as part of the calculation, which forces the sorting in the background before the field calculation can resolve.

           

          More often than not in my experience, a former developer has applied this sorting option to the field when it is not even needed at the field level, as it can be applied at the layout-reporting level when required for a report.

           

          If you watch the dialog for this sorting, it may reveal the name of the field involved so you can find it faster in the field definitions.

          1 of 1 people found this helpful
          • 2. Re: summary field performance issue
            Hudi

            Thanks Stephen,

             

            The dialogue box is kind enough to tell me which field it is. There are two fields causing the latency and they are both averages. could this be part of it?

             

            I will check the field definitions now.

            • 3. Re: summary field performance issue
              Stephen Huston

              Sounds like you also may have a relationship involved in the Average, and that relationship might be sorted at the relationship definition level, which could also cause this.

               

              Let us know what you find.

              • 4. Re: summary field performance issue
                beverly

                A calculated field with "GetSummary()"?

                 

                Summary fields don't "trigger" AFAIK, unless on the layout or used in the GetSummary() function in a field on the layout. I suppose Summary fields could be used in other calculations, too. You could have both options.

                 

                Try this calculated field:

                     FieldNames( Get ( FileName ) ; Get ( LayoutName ) )

                to get the names of all the fields on the the layout (including "hidden"). It might be a clue. Place the calculation field on both layouts and see if there's a difference!

                and/or

                If you have FileMaker Advanced, run a DDR (database design report) to tell you more about the layout and fields that might be on it.

                 

                Beverly

                • 5. Re: summary field performance issue
                  Hudi

                  Beverly and Stephen,

                   

                  I used that calculation   FieldNames( Get ( FileName ) ; Get ( LayoutName ) ) to find out for sure that those summary fields are not on the layout. very helpful, thank you. they still are summarizing though.

                   

                  What I did find is that the summary field is an average of an unstored calculation, could this be the cause?

                  • 6. Re: summary field performance issue
                    Stephen Huston

                    The field which has sorting embedded in it does not need to appear on the layout -- if some field that is dependant on that value is on the layout, it can still be triggered.

                     

                    If any of the calculations involved are based on a sum or subsum of related records, either the subsum is sorted by a specific field, or the relationship involves a sorted order in the relationship definition.

                     

                    If any of the source fields being used in a calc or summary are themselves the resulted of values sorted int he calc, that can cause it.

                     

                    It sounds like the "average of an unstored calculation" may have some sorting in either the calculation definition of a summary or in the relationship being used for the average.

                     

                    I would try printing out the field definitions from the define database field definisiton list, for all of the sums and calc fields, then go over those carefully to see if any reference a

                    Sum (field ; whenSortedbyField) in a calc.

                    • 7. Re: summary field performance issue
                      Hudi

                      Stephen,

                       

                      The problem:

                      I had a summary field that was not on a layout that was causing performance issues on that same layout. The summary field was an average of an unstored calculation.

                       

                      you suggested it was based on sorting that was occuring on the relationship level.

                       

                      What I did was I erased the calculation in the field that the summary field was based off. This caused the problem to go away. I am not sure that there was any sorting going on. I believe the problem was that the unsotored calc was referencing two related tables. Removing the code from the calculation dialogue solved teh problem. I need to understand why. Is it because that data needed to be pulled from the related tables? Its not on the layout, why would it need to calculate that field every time I accss data?

                       

                      thanks for your time!

                      • 8. Re: summary field performance issue
                        DavidJondreau

                        Just because the field itself isn't on the layout doesn't mean the field isn't being referenced somehow on the layout. There are lots of ways to reference a field (some others have already mentioned) that field (or a field referencing it with GetSummary()) would cause a "summarizing" dialog if it's: in the current sort order; a portal sort order; a portal filter;  your Data Viewer; conditional formatting; custom menu calc; etc, etc.

                        1 of 1 people found this helpful
                        • 9. Re: summary field performance issue
                          Hudi

                          thanks dave,

                           

                          i had not realized how easy it is for a field to be referenced.

                           

                          looking at the big picture, how do people design so that summaries don't cause performance issues. Do you localize them in one table? Just phishing for ideas if you have a few moments.

                           

                          Thanks!

                          • 10. Re: summary field performance issue
                            Stephen Huston

                            My advice on subsummaries is to implement them on sub-summary parts on local layouts so that they get summarized only when they sub-sum is actually going to be used by sorting the Found Set.

                             

                            [see the Help section on the GetSummary function for examples of how sorting of related records can actually get embedded in a field definition]

                             

                            While there may be times that sorting a relationship is necessary, and a few even scarcer instances where a calculation result needs to be a GetSummary function  sorted on a related break field, each of these are much less common  than the number of times I have encountered them hard-coded into a calc field definition.

                             

                            Don't write them off entirely, but don't use them unless they are actually essential to the working of your system. The performance hit for field-definition-level sorted GetSummary calcs is really big. So if sorting the found set will produce the same result, sort the found set of records rather than defining a field as a sorted GetSummary result.

                            • 11. Re: summary field performance issue
                              Hudi

                              Maybe I should start a new thread for this but...

                               

                              Is there a way to get the average of a found set of records without using a

                              summary field and just a calulation?

                              If so, can you give me an example? trying to stay away from summary fields,

                              as per your advice. Especially since they are not needed on the layout and

                              only perform calculations on the backend.

                               

                              Thanks