7 Replies Latest reply on Nov 4, 2012 1:10 AM by gpupita@sestante.net

    Database slow with calculated fields

    zosodp

      I am having an issue with a database running in FileMaker go 12. The database contains calculated fields and is running very slow.

       

      Has anyone else experienced this problem ?

        • 1. Re: Database slow with calculated fields
          GordonShewach

          Indeed. Much has been posted about a variety of performance issues in FMP 12, several of which have been addressed in bug fix updates. I haven't yet tested v3 for performance enhancements for calculated fields, but the notes released with v3 did not say anything about performance and calculated fields.

           

          There are many workarounds for the problem: auto-enter calcs, script triggers, scripted updates of indexible fields to sync with unindexible calculated fields, etc. But there can be issues with any of these methods, the biggest being that many of us have systems we've been building for years that have many calculated fields that used to perform at acceptable levels. Changing these fields to one of the previously mentioned strategies could involve enormous developer time and huge sums of client money.

           

          My biggest client's database could not be converted and used in FMP 12 because user tests showed the slowdown in the calculated fields engine had a significant negative impact on user production. My client has faithfully paid thousands of dollars to FileMaker every year for "maintenance" upgrades so he could always have latest and greatest. I won't repeat what my client said when he found out this upgrade is not usable by him without his paying me tens of thousands of dollars to rework calculation fields that performed fine from FMP 3 through FMP 11.

           

          Gordon Shewach

          Desktop Services

          Ann Arbor, MI

          • 2. Re: Database slow with calculated fields
            DanielShanahan

            Can you say a little more about your database?

             

            1. Is it local on the iOS or are you accessing it over the WAN?
            2. Is there one layout in particular that is slow or are all of them slow?
            3. Do your iOS layouts pull from multiple tables (i.e. portals)?  If so, how many?
            4. How many fields are in the tables you are accessing?
            5. Are there other calculation or summary fields on the layout you are rendering on FM Go, including conditional formatting, tooltips*, etc.?
            6. Are the calculation fields indexed or unstorred?
            7. How complex are the calculations?

             

            *Tooltips don't render in iOS, but I think they calculations are still process, though I may be wrong.

            • 3. Re: Database slow with calculated fields
              Stephen Huston

              This is a very common problem with unstored calculations. Whenever possible, replace unstored calculations with stored data fields which are either updated via auto-entered calculated values of by scripting. This can reduce performance time by as much as 98% in some cases I have converted. In other words, a process that took 50 seconds now takes 1 second. YMMV, but the concept holds true pretty much across the board, and is especially critical in WAN environments where the data required by the calculation must be cached across the network before the calc can be resolved.

               

              Also, avoid list views with unstored calc results, as these require caching for larger numbers of records before the screen can refresh.

              • 4. Re: Database slow with calculated fields
                petermontague1

                Where are you hosting your database?

                Run a traceroute to test for latency.

                I am based in Dublin. We hosted our database with an American company. After making changes, such as those suggested above, to calculations and stripping down layouts to their minimum, the database was no faster. My helpful hosting company, oditech, trouble shot with me and got me to run a traceroute. I could see that there was huge latency on several of the IP addresses between Dublin and America. I queried it with my broadband provider - I have 100mb per second. They got me to test the latency with other broadband providers. . It was equally bad. So we ran a traceroute on a UK based hosting company - much faster. We switched to them yesterday on a months free trial. It's all going much faster.

                • 5. Re: Database slow with calculated fields
                  zosodp

                  Daniel,

                   

                  Here are the responses to your questions,

                   

                  First some background on the database

                   

                  The database is for collecting statistical information for a collegiate bowling team. We are collecting pin state (which ball knocked down with or standing), mark information, pocket hits, etc..

                   

                  1) The database is local to the iPad

                  2) The layout that is slow is the main layout for collecting the data

                  3) The layout contains portals for current game statistics and a portal showing the data from each frame of the game (12 seperate portals - one for each frame and 2 additional for the 10th frame extra shots). The current game statistics table is all unstored calculations.

                  4) The main scoring table current contains 25 fields. The statistics table contains 21 fields (17 are calculated)

                  5) There is conditional formatting in the layout. The only calculated fields are in the game stats portal

                  6) Calculated fields are all unstored

                  7) The calculations consist of Counts, Sums and ValueCounts.

                   

                  I have noticed that if I remove the relationship to the main table for the statistics table it alleviates the problem. I would hate to have to manually update 21 calculations every time a pin status changes so I am looking for another solution.

                   

                  Thanks for the help

                  • 6. Re: Database slow with calculated fields
                    melastudio

                    Too much things in a single layout...

                    I don't kmow which device you are using but I think also an iPad Retina will be slow...

                     

                    In order of relevance you should act in this area:

                     

                    • unstored calculation (replace with triggered valued; in go the use of trigger, I think, is essential)
                    • conditional formatting (I'll use, very often, but with simpler calc as possible)
                    • List view with only the essential data, no unstored calc (this is valid also in desktop, expecially in LAN or WAN)
                    • More Tab panel or layout: if you semplify the layout showing a portal at a time i think FM loads less data in a single time (with FM11 it was so; in FM12v1 &v2 this doens'n happen - FM loads all the value also if hidden; non tested with v3 and not testes vith the FMGO); if tab panel doen'st solve you may use more layout; at least for data-entry: less time to load data, more space for digit; but a little hard-working...
                    • 7. Re: Database slow with calculated fields
                      gpupita@sestante.net

                      Hello --

                      You've already found the cause of the slowness, you're in fact overloading the layout with unstored calc fields and the resut you get is expected

                       

                      In general (but YMMV) data entry might/should be kept separate from other info, thus resulting in a much more clean and fast user experience ... then you could have the user land on a second layout that would show the other data

                       

                      You might in addition try to replace unstored calc fields with stored indexable fields: I don't like a lot this approach, since it would imply to use procedure(s) to write the result of the calc into the stored fields, and procedure(s) to keep them updated, but it might help a lot from a performance point of view

                       

                      ciao