11 Replies Latest reply on Sep 23, 2015 11:08 PM by taylorsharpe

    Speed of calculated field.

    malachydevlin

      Hello All,

      I'm struggling with a calculated field on a list layout (50,000 records).

      Basically its grinding the list view to a halt.

      Unfortunately the client must have this field, its a field from a related table (via another table).

      I've tried execute SQL in the calculation field and that's even slower.

       

      I have the list view layout showing table a which has "id, reg_id, customer"

      the reg_id is related to table b to its ID.

      table b: id,brand_id.

      table b is simply related to table c to pick up the brand_name (table c: id, brand)

       

      So when i get the "brand" to display on table A it just crashes out.

        • 1. Re: Speed of calculated field.
          malachydevlin

          Just an update to say that the only viable option I have so far is to put the problem calc field on a popover, it seems this doesnt affect performance and only processes when clicked.

          Solves the problem but not an elegant solution.

          • 2. Re: Speed of calculated field.
            dtcgnet

            Can you post details of the calculated field?

            • 3. Re: Speed of calculated field.
              coherentkris

              Why not just put an edit box or merge field to Table C::Brand on the list layout? You dont need the table a calc to show brand if the relationships from table a>table b>table c are set up right

              • 4. Re: Speed of calculated field.
                malachydevlin

                sorry yes both options are equally devastatingly slow.

                A merge or normal field pointed to the "brand" field across a relationship.

                To be honest its a simple relationship and well indexed so it just seems that filemaker is slow at displaying related information when there are so many records. especially as this is across a WAN.

                • 5. Re: Speed of calculated field.
                  coherentkris

                  Then i would look at eliminating unstored calculations, from proximal table to distal table, that could be slowing things down. Putting a distal field control on a layout should not appreciably slow things down.

                   

                  What are you seeing that makes you say "devastatingly slow" and "crashes out"?

                  • 6. Re: Speed of calculated field.
                    dtcgnet

                    I agree that the distal field by itself shouldn't slow things down to "devastatingly slow", but unstored calcs that aren't on a layout take up neither time nor space. Have you got calculations involved in any conditional formatting? Have you got calculations involved in any hide conditions?

                     

                    Over the WAN, the speed of your internet connection will have a large impact on speed, too.

                    • 7. Re: Speed of calculated field.
                      electon

                      Unstored calcs should not have an impact unless they're somehow referenced to on the layout. ( empty space rights side counts as well ).

                      The size of the tables can play a role here. Are there many fields in those tables? Especially the "heavy" ones.

                      Filemaker can only download the entire record, not just a field. So you're pulling all data from 3 tables.

                      On WAN the performance drop will be noticeable, depending on data size and connection.

                       

                      After dealing with unstored calcs, conditional formatting, hiding etc. ( there are posts where that solved the problem ), maybe it would be worth trying dropping all but the necessary fields and see if that makes a difference.

                      • 8. Re: Speed of calculated field.
                        taylorsharpe

                        Depending on the scenario, one solution is to have an periodic script run (e.g., nightly) that calculates the data and stores it in a field and your report would just show the stored result.  Granted it is only as up-to-date as the last run, but it can make the report much more livable and if they need current numbers, they can just run the script that updates the stored numbers.  As a stored number, it will be much more manageable. 

                         

                        Also, if you post the calc, we may have some suggestions on ways to optimize it. 

                        • 9. Re: Speed of calculated field.
                          Mike_Mitchell

                          What you're seeing is likely the result of record caching. You have 50,000 records in List view. FileMaker will load as many records as will display, then load more as the user scrolls to reveal them. However, in order to display secondary and tertiary records, the related records must load as well - every field - and thus for each record being displayed, you're actually loading three.

                           

                          One way to test this theory is, does it remain slow after you scroll back up? IOW, is it only slow on initially loading the records?

                          • 10. Re: Speed of calculated field.
                            Vincent_L

                            What you see is "normal" in the filemaker world, related fields are pathetically slow that's a fact.

                            To me filemaker is a relational database that you can't use relationally if you want some speed on list view of more than few hundreds records. And this is on LAN.

                            The fix, is to make the relational data flat, destroying the benefits of relational data.

                             

                            And as far as for wan, it does not work in list view, that's it. It's much too slow, the issue as been for years with some little improvements between 6 and 7. Filemaker networking protocol is too much chatty, sending gazillions of small packets instead of bigger chunks. Sadly, FMOI never addressed the root of those problems.

                            • 11. Re: Speed of calculated field.
                              taylorsharpe

                              Vincent_L wrote:

                               

                              What you see is "normal" in the filemaker world, related fields are pathetically slow that's a fact.

                              To me filemaker is a relational database that you can't use relationally if you want some speed on list view of more than few hundreds records. And this is on LAN.

                              The fix, is to make the relational data flat, destroying the benefits of relational data.

                               

                              And as far as for wan, it does not work in list view, that's it. It's much too slow, the issue as been for years with some little improvements between 6 and 7. Filemaker networking protocol is too much chatty, sending gazillions of small packets instead of bigger chunks. Sadly, FMOI never addressed the root of those problems.

                               

                              I disagree with your conclusion.  I have investigated and optimized many databases and the reason most people find FileMaker relationships "pathetically slow" is because of the feature that FileMaker allows relationship looking into unstored calculation fields in other tables.  This truly can be useful sometimes, but it increases the amount of calculations significantly and would hinder any other database engine too.  If you use relational data that is stored, I've had no problems with millions of records. 

                               

                              There are some things about FileMaker that are disappointingly slow such as sorts.  And there are issues about doing things like imports and exports where FM literally updates indexes before each record is deleted or exported that makes it really slow.  But relationships of stored records really is fast.  And while I think these items could be improved upon in the near future, the unstored calculation feature of FileMaker is one that is simply limited by the horsepower of the cpu and a single relationship with unstored calculations literally can be doing an exponential number of calculations from the original table.  And that is why big data stores run batch jobs to store calculated results that can be called upon quickly, and FileMaker is no different. 

                               

                              FileMaker made a big technology jump when going from .fp7 to .fmp12 and 64 bit server.  It really handles large databases and more users a lot better than it used to.  But even a small database poorly designed can bring FileMaker to its knees.  Maybe you should get a discussion going on ways to improve FM performance.  There are a lot of tips out there such as when to use SQL and that it is better to use more tables with fewer fields than one big table, etc.