1 2 Previous Next 15 Replies Latest reply on Feb 8, 2010 3:50 PM by user14360

    Improving the speed of loading a layout with lots of calculation fields

    user14360

      Title

      Improving the speed of loading a layout with lots of calculation fields

      Post

      Hi.  I'm working on Filemaker 10 Advanced, Mac OS X.

       

      I have run into trouble with an important layout that has lots of calculation fields, and that is now loading too slowly for use. 

       

      There are 403 fields on the layout.

       

      About 26 of them are individual fields on a 1 row portal (specifying a particular row of the portal to retrieve) referencing a number field (sales) in another related table.

       

      About 26 of them are individual fields on a 1 field portal from a particular relationship (there are 26 individual relationships - one for each of these fields), referencing the name of a promotion that might be occuring during the week this field is reflecting.

       

      The rest of the fields (so that leaves 351) are some kind of calculation field in a 1 row portal (again, specifying a particular row of the portal to retrieve). 

       

      By the end of the fiscal quarter, all of these fields will have something in them (except for some of the 26 promotion fields).

       

      About half of the calculations need to update when a field that is referenced in the calculation is updated, so I guess that means they need to be unstored, which I presume slows it down a bit.  The rest of them could do with being updated just overnight.

       

      I had previously had a lot of the calculations being direct calculations in the main table that the layout is based upon, rather than a portal into the other related table.  But I moved away from this because it was too slow as I added more fields to the layout, and tried the approach I described above.  It started off well, but as I added more fields to the layout, it again became slower and slower.  This approach however does seem to be a little bit faster than the direct calculations within the main table, but it is still basically too slow to use, and this is still while the file is local - it will be eventually hosted on Filemaker Server, which I presume will make it even slower.

       

      So my question is, is there a way to make all of these fields load more quickly?  I can experiment with having different portions of the layout on different layouts, rather than all at once, but the client REALLY wants them all together, as that is what they're used to seeing on an excel spreadsheet.  It's a bit of a deal-breaker - if this page isn't able to load properly, then they will probably be averse to taking the database on board.

       

       

      Any suggestions? 

       

      I have also experimented with:

      Taking a screenshot of the part of the layout which could get away with having the values updated just overnight, and then loading that image into a container field on another layout that has only the other fields that need to be refreshed immediately.  This could possibly be a solution, but only if I knew how to automate the screenshot/image production of the layout and adding it into the container field.  (The 2nd part I can do, but not the automation of the screenshot/image.) 

        • 1. Re: Improving the speed of loading a layout with lots of calculation fields
          kapitaen_1
            

          if you cant reduce the number of calculated fields in the layout,

           

          try stored instead of unstored (with a refresh button and a script that recalculates all)

           

          try playing around with indexing related table (no completed index, try minimal), more index fields

           

          try giving filemaker more ram for table caching, increase the standard 32mb to 100 or 200 mb. if you increase more, be cautious, there soon will come a point where time win will turn to time loose.

           

          greetings from germany

          chris

           

           

          ps.: too slow for use, that means how many seconds / minutes / hours?

          • 2. Re: Improving the speed of loading a layout with lots of calculation fields
            Steve Wright
              

            We also use a similar technique to above, whereby unstored calculations are used when doing data entry.

            When the record is saved / closed, these figures are copied into stored fields in another table and indexed.

             

            Any reports are run using the stored figures.

             

            You just have to ensure that anything which may change the figures, also updates the stored copies.

             

            What would normally take 20 minutes to calculate, now happens almost instant.

             

            • 3. Re: Improving the speed of loading a layout with lots of calculation fields
              user14360
                

              Kapitaen - it takes about 13 seconds to load.  That's on a local copy, not the hosted one, so presumably when it's hosted it might be more like 20, 25 seconds.  

               

              If it were just the loading of the layout that took 13 seconds, that might be ok, but the user needs to be able to move forwards and backwards quarters (by way of changing a global field that is the basis of the relationships to the data in the fields being viewed), and every time they click the forward or backwards button, it has to reload all the calculations, and it takes another 13 seconds.  This won't be acceptable to them.

               

              If I were to make the calculations unstored, what kind of script steps do you need to get them all to recalculate? 

               

              SW - that sounds like a smart idea.  Would this work in the scenario that the user needs to be able to change quarters and load the data quickly?  Presumably ALL the data from all quarters gets copied into the stored fields, and then that is what the user sees, so when they more forward or backwards through the quarter, the fields update based on the relationship of the global quarter selected.

               

              Could you please provide a little more detail about your suggestion SW?  For example, does your system mean that you effectively have the same set of data in two tables, one with the stored fields and all the calculations, and the other with just the basic data, unstored, and this is the data that gets edited?  And when it is edited, do you run a search in the other table for the appropriate records and then replace the field contents?

               

              Thanks for the help. 

              • 4. Re: Improving the speed of loading a layout with lots of calculation fields
                user14360
                  

                The other complication is that a lot of my calculations are done via relationships.  So for example, you might have a sales record for Q1, 2009, which is a particular date and week of that quarter.  There is a field in that record that calculates that the last quarter is Q4 and the year for that last quarter is 2008.  Then a self join relationship based on year = last year and quarter = last quarter goes and works out what the sales amount was for that week of the quarter in the last quarter.  I just went and tried to change that field to being stored, but it tells me "The calculation cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage."

                 

                Most of my calculations are based on relationships that work out previous years sales data and previous quarter sales data. 

                 

                Will I need to alter my structure for this technique to work? 

                • 5. Re: Improving the speed of loading a layout with lots of calculation fields
                  deltatango
                    

                  Hildy,

                   

                  You're probably better of making a script that weekly or monthly copies to values into a table of regular text or number fields and uses those for the report. Once a week or month closes of for example, run a script to gather the summed or calculated values and insert them into a table for using in reports. 

                  • 6. Re: Improving the speed of loading a layout with lots of calculation fields
                    deltatango
                       sw, this is a great solution. the only drawback i see is double the amount of info in the file but i think it would make it run faster for sure.
                    • 7. Re: Improving the speed of loading a layout with lots of calculation fields
                      user14360
                        

                      Thanks Deltatango.  I'd need to do it daily, as there will be daily sales data coming in, but that can happen overnight I guess.

                       

                      So do you just mean a looping script where I would set a whole bunch of variables from one record, go to the other table, insert those variables into the appropriate fields, then come back to the original table, find the next record, set the variables etc... until they've all been copied? 

                      • 8. Re: Improving the speed of loading a layout with lots of calculation fields
                        user14360
                           And another question - would it be just as good to make the table into which the data is going to go and be stored to have lookup fields (with the relationship based on a SalesEntry ID), and I can re-lookup the field contents each night?  (Rather than running a looping script?)
                        • 9. Re: Improving the speed of loading a layout with lots of calculation fields
                          deltatango
                            

                          Sorry for the broken english of my earlier post...that happens when I've been staring at my computer screen for more than 10 hours...

                           

                          :P

                           

                          Yes, but you can shortcut it by using Set Field by Name and a globalField with a value list of fieldnames.

                           

                          That way you don't have to use a billion set variable script steps. 

                           

                          So for example:

                          Set variable: $fieldList = "field1field2field3field4"; 

                          Set Variable: $fieldCount = ValueCount($fieldList); 

                          Freeze Window 

                          Go to Record First

                          Loop 

                          Set Variable: $counter = 1

                          Loop

                          Set Variable: $record[$counter] = Evaluate("Table1Name::" & GetValue($fieldList);$counter);

                          Set Variable: $counter = $counter + 1

                          Exit Loop if $counter > $fieldCount

                          End Loop 

                          Go to Layout CopyTable

                          New Record

                          Set Variable: $counter = 1 

                          Loop 

                          Set Field By Name: Evaluate("Table2Name::" & GetValue($fieldList;$counter)), Value: $record[$counter]

                          Set Variable $counter = $counter + 1 

                          Exit Loop If $counter > $fieldCount

                          End Loop 

                          Go to Layout [Original]

                           Go To Record Next[Exit After Last]

                          End Loop 

                           

                          This way, in the future, if you change field names or add fields or delete fields, just update the value list of the first script step and you are all set!

                           

                          Note the field names are identical on both tables 

                          • 10. Re: Improving the speed of loading a layout with lots of calculation fields
                            deltatango
                               Wow...I didn't even think of that. Yes you could. Just enter the record ID of the main table and copy the data over. BUT, you'd still have to program each field to do a lookup. With my way, you just add the field names and it will update.
                            • 11. Re: Improving the speed of loading a layout with lots of calculation fields
                              user14360
                                 Thanks for taking the time to write out the script.  I might try the lookup thing first, only because it seems a bit simpler to get my head around and get it going quicker.
                              • 12. Re: Improving the speed of loading a layout with lots of calculation fields
                                user14360
                                   One more q - presumably I turn on indexing to "all" for all of the fields in the stored table?  I don't know much about indexing as mostly I've left it up to filemaker, until now, and am wondering when it does the indexing?  Will it do it after I run the 're-lookup field contents' script during the night, so the users won't notice any slow down at any point?
                                • 13. Re: Improving the speed of loading a layout with lots of calculation fields
                                  user14360
                                    

                                  So exciting!  I've just created the duplicate sales table, set each field up as a lookup to the appropriate field from the original table with all the calculations, run the re-lookup field contents, and put these fields on my layout now, and it works brilliantly. 

                                   

                                  Thanks you so much for everyone's help.  This is the difference between the database being taken on board and being scratched.

                                   

                                  Currently the fields have no indexing - should I turn on indexing?  Will this make it even quicker?  

                                    

                                  • 14. Re: Improving the speed of loading a layout with lots of calculation fields
                                    Steve Wright
                                      

                                    Sorry, I lost track of this post.  Seems like its all sorted now ;-)

                                     

                                    I Just wanted to clarify why I perform the update of the secondary table on a one-by-one basis and not as a batch.

                                     

                                    Firstly... It takes a few seconds to update one record as you go, rather than wait for it to update 100 in a batch, so its never noticed and doesnt require any other interaction. 

                                     

                                    Secondly... If an older document is later edited, for any reason, it ensures the stored figures remain valid and up to date.

                                    Basically, it keeps itself in sync automatically and prevents running reports with incorrect figures.

                                     

                                    I use a single relationship matching the 'sales_id' in both tables with allow creation of related records turned on.

                                    Then (as mentioned) I simply use a set field loop to update the figures.

                                     

                                    Also, we do have quite a few calculations within the 'stored figures' table, but because all referenced fields are stored and not calculated, these calculations evaluate quick also.

                                     

                                    1 2 Previous Next