1 2 Previous Next 24 Replies Latest reply on Nov 21, 2013 9:37 AM by DavidJondreau

    Calc engine questions for optimizations

    fmpvince

      Hi,

       

      We need to get answers from Filemaker Engineers about how works the cal engine to better optimise our calcs fields.

       

       

      Here's some questions (I know that some example don't make sense but that's to illustrate the point)

       

      1. Once the cal engine gets a related field for the first time does it cache it or does it fetch it whenever and as many time he finds it.

       

      So which is faster :

       

      my_unstored_related_table::value+my_unstored_related_table::value+my_unstored_related_table::value+my_unstored_related_table::value

       

      or

       

      Let(value=my_unstored_related_table::value;value+value+value+value)

       

      If the cal engine caches my_unstored_related_table::value performance should be the same

       

       

      2. Considering a List view, where there's 4 fields, 3 of of them referencing the first one which itself gets some values from un unstored calc, is the first field cached ?

       

      field1 : my_unstored_related_table::value*my_unstored_related_table::value+my_unstored_related_table::value

       

      field2 : field1*2/3

      field3 : field1*12

      field4 : field1/15

       

      in other words, would field2,field3,field4 recalculate field1 ecah time (trigerring this my_unstored_related_table::value*my_unstored_related_table::value+my_unstored_related_table::value)

       

      3. In the above example, Is it better, for performance, to reference a field, or to recalculated it completely

       

      so is this below faster than 2.

       

      field1 : my_unstored_related_table::value*my_unstored_related_table::value+my_unstored_related_table::value

       

      field2 : (my_unstored_related_table::value*my_unstored_related_table::value+my_unstored_related_table::value)*2/3

      field3 : (my_unstored_related_table::value*my_unstored_related_table::value+my_unstored_related_table::value)*12

      field4 : (my_unstored_related_table::value*my_unstored_related_table::value+my_unstored_related_table::value)/15

       

      Thanks.

       

      P.S : If it does not cache, then i think it definetly should

        • 1. Re: Calc engine questions for optimizations
          Mike_Mitchell

          Hello fmpvince (I assume you're Vince?).

           

          I'm not a FM engineer, but I can answer the question.

           

          In example 1, yes, you should use the Let function. The related result is not cached.

           

          Example 3, same thing. The unstored calculations are evaluated when they're needed.

           

          Example 2, you've created a dependency chain. field2, field3 and field4 "depend" on the value of field1, so in each case, field1 has to be evaluated prior to field2, field3 or field4 being evaluated. Chances are, your performance will actually be worse than with example 3.

           

          There's a discussion of this in Ray Cologon's FileMaker Pro 9 Bible.

           

          HTH

           

          Mike

          • 2. Re: Calc engine questions for optimizations
            fmpvince

            Hi Mike,

             

            Thanks for your help

             

            Yes, it's Vincent.

             

            You confirm my fears, that's very, bad performance wise, and explains why my lists are slow, and worse why they'll always be slow ! Because there's no way to speed this.

             

            I need the flexibilily of 2, because filed1 is actually much more complex, and is prone to frequent evolution, recopying it's declaration in all fields that depends of it would be a nightmare.

             

            That's why I URGE FM engineers, to cache that on a per record basis and only during the calculation.

            • 3. Re: Calc engine questions for optimizations
              BruceHerbach

              Hi Vincent,

               

              There is a work around to this of sorts.  The key is to be able to change the unstored calculation to a stored calculation.  To do this you have to first understand the rules.

               

              If the calculation is based on stored fields in the same table then you have the option of unchecking "Do Not Store..." and setting indexing.  If the field comes from a related table then the "Do Not Store" check box is checked and grayed out.

               

              The work around can be somewhat complex. Change the field from a calculation to an Auto-Enter Calculation and set it to Replace existing value and Evaluate always.  Next add a field to the table called ~update ( or any other name you like.) 

               

              The change to Auto-Enter Calculation will bring in the calculation formula.  Either add a Let statement or add a line to the let statement the looks like upd = ~update;. 

               

              Now you have an auto enter calculation that is based on both a local field and the data in other tables.  The final trick is to force the field to update.  To do this change the value in the ~update field.  Everytime the field is changed,  the calculation will update and pull in any new information from the other tables.  Unfortunatly this is the most complex part.  You will have to script this and determine when the script will run.  If you need to do this over several tables then the scripting grows and you have to take in to account the structure,  which table has to be updated first etc.  On the other hand,  if these only have to be updated when the record is created,  then the auto-enter calculation will work with out all of this and the data will be stored and can be indexed. 

               

              As as been pointed out by others,  Unstored Calculations can't be indexed because the only time they have a value is when they are displayed.  Changing them to an auto-enter calculation means they have data all the time and so it can be stored and indexed.

               

              Hope this helps. 

              Bruce Herbach

              • 4. Re: Calc engine questions for optimizations
                fmpvince

                Thanks Bruce,

                 

                Your workaround works for the most simple case, but mine is much more complexthan that, because it involves a cascade of unstored calcs, with many tables. One value change in one of those table could impact thousands of records, so the script trigering the update would to figure out all affected records, in maby tables an then update them. That's almost impossible, and completly kills the benefit of filemaker unstored calc.

                 

                Before I duplicated all my important fields with a _cached version, an populated them periodically via a replace, but that prouved to be unreliable, so that's why I went back to the original unstored calc.

                 

                By the way, when I'm refering to caching, I'm not confusing it with indexing. Unstored fileds are ok, what I'm suggestiong FMP engineers, is that durring the evaluation of the fileds of a particular record, unstored value would only be fetched once instead of many time (since the time is the same, there's no need to refetch unstored value several time, because they won't change). That would improved the speed of all fmp solution on earth, and I see no side effects.

                • 5. Re: Calc engine questions for optimizations
                  Mike_Mitchell

                  Vince -

                   

                  Is there a reason why the Let function wouldn't accomplish what you're asking from FileMaker?

                   

                  Mike

                  • 6. Re: Calc engine questions for optimizations
                    BruceHerbach

                    Hi Vincent,

                     

                    As far as caching goes...  I sat through a session at a devcon  that

                    covered this.  It has been a while but this is what I remember.

                    When ever a field is required, the whole record is downloaded from the

                    server and data is cached.  Details about this might be found on the

                    FileMaker website.

                     

                    Unstored calculations are recalculated when they are displayed.  This is

                    what kills performance in portals.   As each record appears on the screen

                    it's value is computed.  So the cached records can help speed up the recalc

                    as the portal row is displayed,  but it is still recalculated as the new record is

                    displayed on the screen... I think.

                     

                    I understand what you mean about the work around I proposed.  I have

                    implemented it for a complex setup to

                    Solve the portal performance issue.  It worked,  but the scripting to

                    update all related records was difficult and has its own performance

                    issues.  Some have been improved by doing a more targeted updates or

                    finding ways to display related fields from the other tables and having those

                    fields be stored calculations.

                     

                     

                     

                    Sent from my mobile device... Please excuse typos.

                    • 7. Re: Calc engine questions for optimizations
                      fmpvince

                      Hi Mike,

                       

                      Absolutely, the let function doesn't help at all for fields2,field3,field4 in case 2 and thats the most usefull/common case.

                      Moreover in real life field1 will use 3 ≠ unstored fields.

                       

                      Plus, it will help any FM Devs, even the most newbies one, using the let function for that is a workaround, we shouldn't do workarounds. So only advanced dev couls use the let function, while most user will experience a slower than possible performances. If you use a RAD, it's not to have to do worakarounds.

                       

                      I there's a reason it's necessary to re-evalutate it whichever the script engine stumbles upon an unstored fields in a calc, then FM dev should tell us.

                       

                      Otherwise that means it's just sloppy programing that kills the performance of Filmemaker, and that needs to be addressed.

                      • 8. Re: Calc engine questions for optimizations
                        Mike_Mitchell

                        Vince -

                         

                        Without seeing your solution, I can't say for certain, but oftentimes, the echoing of related fields in the current table is unnecessary with a little cleverness on your part as a developer.

                         

                        For example, instead of using calculated fields in the current table (as in example 2), you can:

                         

                        1) Use a script trigger to mirror the data at data entry time, similar to Bruce's suggestion. This pushes the data to the current table in a stored fashion and eliminates the runtime updates. It also eliminates the need to even run it as a calculation, since you can imprint the calculated value into the field.

                         

                        2) Put the calculation in the related table instead and reference it in the current table. This would allow it to be stored and eliminates the requirement to update it at runtime. This way, all you're doing is displaying the related field.

                         

                        I used to do a lot of the kinds of calculations you're describing here. I've found in over 20 years as a FileMaker developer that there are usually better ways to do it.

                         

                        If it will make you feel any better, at last year's DevCon, Jon Thatcher made mention that one of the things they are working on is performance improvements. However, I think you should take a serious look at other ways of doing things rather than accusing FlleMaker of "sloppy" coding. We're a community here, and insulting the folks who provide the product on which we build our livelihoods is not generally appreciated.

                         

                        Mike

                        • 9. Re: Calc engine questions for optimizations
                          Malcolm

                          These are good questions and I would like to know how filemaker behaves under the hood too.

                           

                          There are some things that we already know from observation.

                           

                          1. unstored calculations are slow.

                          2. stored calculations are quick.

                           

                          The most obvious response is, don't use unstored calcs. Here are two tried and tested methods and one pie in the sky method to avoid the performance hit from unstored calc fields.

                           

                          1. Move the calculations into a context that allows them to be stored. I've seen unnecessary unstored calcs in almost every file with relationships. Put the calcs in the correct location and let them be stored/indexed. Refer to the related data.

                           

                          2. Unstored calcs are handy but often they aren't part of the primary data. They are used for information/reporting purposes and may not be required often. Use scripts to build data or collect statistics on the fly.

                           

                          This is especially useful when you only want to report on a small subset. Why have an unstored calc reporting on ten years of data when you only ever want to look back twelve months?

                           

                          3. A hidden issue is that an unstored calc has to be evaluated everytime the record is accessed. You may not even want to see the unstored calc but it is being evaluated. That can affect performance at all times.

                           

                          There was a recent question about slimming down table sizes by using 1:1 relationships to move data which is only accessed infrequently to a different table. I think it was noted that for related data (a) only the records linked by the relationship are loaded and (b) only the fields on display are loaded. If that is correct then you might improve performance by putting unstored calc fields into another table and accessing them via a 1:1 relationship. They wouldn't have to be evaluated as a part of the record data set unless they were being displayed.

                           

                           

                          Malcolm

                          • 10. Re: Calc engine questions for optimizations
                            fmpvince

                            First let me say this, the big picture is this :

                             

                            A relational database is meant to be relational, so to gets it's values from a lot of table. So using related fields shouldn't be such a penalty. Workarounds to flatten / mirror the data, make it indexed, is a failure. We're forced to that failure, that destroys the whole point of relation database, because there's no internal caching at execution time, which to me is the responsability of FM Inc.

                             

                            As for your suggestion,

                             

                            1 : this suffers the same problem as bruce suggestion, and as I said one value in one table should update thousands of records, in different tables.

                             

                            2 : would work if all the data needed for the calculation would be in the realted table, but it's not the case.

                             

                            Here's an example : consider the final price, shipping cost included, with a discount according to the customer discount, plus extra table during a particular periode of time.

                             

                            A you have the product table (the main one) with the main price, and it's weight

                            B you've a table of shipping cost according to produc'ts weight

                            C you've a customer table with their usual discount (global filed for customer id)

                            D you've a table of extra discount according to a period of time

                             

                            so final payed price is : (  A.price  * A::C discount ) * A::D extra discount +  A::B throught weight shipping cost

                             

                            Now change the extra discount value, and then with you method and bruce one, you've to recalculate thousands of prices.

                             

                            now imagine that the person that enters the extra discount value, should determine it based on the final margin made by the sum of the margin of all products purchased of the customer lengthty with hundred products invoice. And hence he needs to adjust it and see the result immediately, both as the total margin, and per product.

                             

                            Flattening the data will induce a lot of delays.

                             

                            (don't try to solve this, it's an over simplified example of  my solution).

                             

                            Once priced calculated I need to get the margin of that product, which is 1-product cost/final prices (which is like field1*1/12 in my example 2). Of course product cost is actually the cost of all the parts of the products (because we manufacture it), so it's a related sum. And part prices depends shipments, with each shipment different cost.

                             

                            Moreover, the final price may depend on a new kind of stuff according to the evolution of the business so I need to be able to update it's calculation frequently.

                             

                            So flattening is a daunting task, to the point it gets impossible to maintain. And even if it could, the time to make sure all is perfect is defetaing the purpose of RAD

                             

                            First let me say this, the big picture is this :

                             

                            A relational database is meant to be relational, so to gets it's values from a lot of table. So using related fields shouldn't be such a penalty. Workarounds to flatten / mirror the data, make it indexed, is a failure. We're forced to that failure, that destroys the whole point of relation database, because there's no internal caching at execution time, which to me is the responsability of FM Inc.

                             

                            As for your suggestion,

                             

                            1 : this suffers the same problem as bruce suggestion, and as I said one value in one table should update thousands of records, in different tables.

                             

                            2 : would work if all the data needed for the calculation would be in the realted table, but it's not the case.

                             

                            Here's an example : consider the final price, shipping cost included, with a discount according to the customer discount, plus extra table during a particular periode of time.

                             

                            A you have the product table (the main one) with the main price, and it's weight

                            B you've a table of shipping cost according to produc'ts weight

                            C you've a customer table with their usual discount (global filed for customer id)

                            D you've a table of extra discount according to a period of time

                             

                            so final payed price is : (  A.price  * A::C discount ) * A::D extra discount +  A::B throught weight shipping cost

                             

                            Now change the extra discount value, and then with you method and bruce one, you've to recalculate thousands of prices.

                             

                            now imagine that the person that enters the extra discount value, should determine it based on the final margin made by the sum of the margin of all products purchased of the customer lengthty with hundred products invoice. And hence he needs to adjust it and see the result immediately, both as the total margin, and per product.

                             

                            Flattening the data will induce a lot of delays.

                             

                            (don't try to solve this, it's an over simplified example of  my solution).

                             

                            Once priced calculated I need to get the margin of that product, which is 1-product cost/final prices (which is like field1*1/12 in my example 2). Of course product cost is actually the cost of all the parts of the products (because we manufacture it), so it's a related sum. And part prices depends shipments, with each shipment different cost.

                             

                            Moreover, the final price may depend on a new kind of stuff according to the evolution of the business so I need to be able to update it's calculation frequently.

                             

                            So flattening is a daunting task, to the point it gets impossible to maintain. And even if it could, the time to make sure all is perfect is defetaing the purpose of RAD

                             

                            I've found in over 20 years as a FileMaker developer that there are usually better ways to do it.

                            Thats precisely the problem, you need 20 years of Filemaker experience to workaround, at the price of countless hours to get somewhat decent performance, of a Rapid Development plattform. And those workaround are contrary to the ethic of a realtional database (not duplicate data). So, that's a failure of the platform.

                             

                            If it will make you feel any better, at last year's DevCon, Jon Thatcher made mention that one of the things they are working on is performance

                            improvements.

                             

                            I really appreciate this, It's a welcome news, but it's about time. Till now, we couldn't see a will to care about performance (filtered portals slowers than the old method for instance).

                             

                            There's thousand of very cheap way to get massive speed boost fo FM Inc to implement :

                             

                            - Searching including indexed field and unstored one. Of course the search engine should serach first all the indexed fields and then constraind to the matching unstored ones. Instaed of that it evaluates all the unstored value of all the records in the database. That's crazy and that discourage all the newbie of the Filemaker world, and they may live forever with slower than needed solutions.

                            How on earth could the introduce the quickfind method, with all fields of the layout set to quickfind by default including the unstored one, without addressing this ? That's 2 lines of code. But they introduced this feature anyway, slowing down the solution of all who trusted this new feature without extensive knowledge. That's a shame when your raison d'être is to be a simple database tool for data driven users.

                             

                            - Why no "update" : replace can only process one filed of a record at the time. want to change 10 fields, that's ten replaces that 10 times cycle throught the foundset (yes I knwo about set field, but their'e slower than reaplace most of the time). And yes I'm aware that field 10 can of the replaced value of field 3 and 5. But this is to us to figured it out. There's no reason we need to suffer this multiple replaces when the values don't depend to each other.

                             

                            - Why no TRUNCATE table like function. Deleting all records of a database is ultra slow. Yes I'm aware that there' related delets (but most often there's not), and the truncate engine could do it's regular delette all stuff if there's such related deletes.

                             

                            - Why no fast way to highlight a line in list view (actually FM11 destroyed a faster workaraound and we had to fall back to a slower one). Actully this is the mean to known if the slected record is indeed selected.

                             

                            - Why the absolut need to specify to freeze window to get decent speed. During script of course the window should be frozen by default, and we should have a step to unfreeze it. All my script are littered with freeze script step. To make te insult worse, this freeze step is not compatible with server side processing, and hence all those script breals on the server. So you have to enclose your freeze steps with a test to dertermine if you're on  the server or not. Like if the server couldn' just skip it (as this steps as no efect whatsoever on the script result).

                             

                            - Why do you have to switch to a blank layout in form view to get descet speed ? I duplicated all may layout with blank ones. Ilagine the time it tok me in total. And that's because I'm an experineced dev. All newbie out there don't understand.

                             

                            However, I think you should take a serious look at other ways of doing things rather than accusing FlleMaker of "sloppy" coding. We're a community here, and insulting the folks who provide the product on which we build our livelihoods is not generally appreciated.

                             

                            Mike

                             

                            First I can't see how it is insulting to criticize work, and call a cat a cat. Creating the Mac, SJ said to one of his dev (with color), that the boot time was too slow, the dev said it wasn't possible to improve it, some days later he saved 28 seconds of boot time. People that care about their work, know that can do better and hence accept criticism. And "sloppy" to me means some code done once in an economical fashion for whatewver reason, with known drawbacks, and then left here for decades without upgrade. Myself, if I do sloppy code, and someones tells me it's bad, if that's true I accept it, cause it's my fault, and I see in the reviewer critic the trust he has in me to fix it. But that's maybe just me.

                             

                            But what's interesting is that it's the "community", which are the customers of the paying product Filemaker is, that feel insulted in the first place for criticism of the vendor. That's completly illogical and worse, highly improductive. The comminuty should not excuse (which is the definition of most of the workarounds) the vendor. The community should support it by constructive criticism.

                            That community bad feeling for compagny criticism is beyond me. But there's 1 reason for that for every community, and maybe maybe an 2nd more insidiuous one specifice to the filemaker community.

                             

                            Reason 1 : Community feels ashamed if there get there choosed product is criticized, they take it personnaly because they think that due to their choice, that's being criticized, they're the one that by extend are criticized. You see this in all forums, every nikon/canon, pc/mac flame wars. That's sheer stupidity, but maybe that's human nature.

                             

                            Reason 2 :  The filemaker community. Who are the most vocal members ? Filemaker professional developpers. They're vocal because they are of course the most advanced ones, they want to spread the filemaker love, and to show off their craft which traslates in some advertising. That's fine. But at some point it's better for them if Filemaker has some pitfall, need workaround, has gotchas, because then they can sell their craft to sell cosulting o fix databse. Or to create plugins, or to write books.

                             

                            Let's take the example of the anchor buoy method for managing the unmageale, flawed from the start, relationship graph. People wrote books about this. But, first, the anchor buoy method as been proved to be slower performance wise, and its sole existence is to help you to sort the mess the graph created by it's own existence and sloppy implementation (why no TO search for god's sake). The truth is that the anchor bouy method would be totally useless if there was a desent search in that graph window (and that's a very easy to implement see Dracovenion developper's assistant or Clip Manager from fm buttler). The other truth is that's it's only payed developpers that can waste the time to create anchor buouy method, and that a neat graph is cool way to show the payed devolper supperiority vs the the regular FMP user.

                             

                            So all workarounds for the sloppy feature of filemaker are as many as advertising tools for Filemaker payed developpers. So the "community" has no interest to get the sloppy feature fixed. That's why, you never see the filemaker community echoing criticism to fM Inc, it's better for them to criticize the criticizer and offer they help with their crafty workarounds (lately thankfully due to non fixed bugs there was a slight uproar)

                             

                            But to me this hurts that platform a lot, because FM inc can count on them (FM devs) to excuse its flaws. To me they'd better criticize FM inc for its real flaw, to force them to fix them, and instead of wasting their wit on workaround, they should spend it on the actual features of the database.

                             

                            And don't forget that we're paying big bucks "the folks who provide the product on which we build our livelihoods", in fact maybe not you, but us USERS do.

                             

                            Don't forget that Filemaker payed developpers are an oddity, Filemaker is not meant for them, nor designed for them. Filemaker is meant for regular people that need to built custom solutions themselves and quickly. FM payed Devs are a very tiny part of all non payed devs out there. And even better, a lot of time FM payed devs are only consulted because in the first place, the business owner/employee started to create a FMP solution himself at first and then can't manage it anymore. Without Filemaker those users would have hired a php/mysql dev from the get go, or they wouldn't have been able to run the business. So don't forget who the real FM community is.

                             

                            Nothing personal Mike / Bruce of course.

                            • 11. Re: Calc engine questions for optimizations
                              fmpvince

                              Hi Malcom,

                               

                              1 and 2 are ok in the simplest solutions, but see my product price example and you'll feel the pain.

                               

                              3. I'm not sure, and seriously don't hope that all the fileds of the records are fetched and unstored fields evaluated.

                              But even with this convoluted method, that won't improve my solution because I need to display them (in list view, 15 columns) with list of several hundreds lines.

                               

                              But that's why I called for a FM engineer help about the under the hoods.

                              • 12. Re: Calc engine questions for optimizations
                                Malcolm

                                I saw that example. Scripting data updates is one way to handle these issues. The problem is to have the data kept up-to-date. Robots running scheduled tasks may assist.

                                 

                                Malcolm

                                • 13. Re: Calc engine questions for optimizations
                                  Mike_Mitchell

                                  Vince -

                                   

                                  It's become quite clear you didn't come here for a solution to your problem, but rather to complain about flaws you perceive to be present in FileMaker. Here's a little hint for you:

                                   

                                  None of us here has any power to do anything about changing the design of the product. We're developers who use it, and we've given you several suggestions for how to improve your situation. Hence, you have three choices:

                                   

                                  1) You can try to work with our suggestions and see if it helps.

                                   

                                  2) If you want to make a suggestion for improvement to the product itself, you can do so at http://help.filemaker.com/app/ask.

                                   

                                  3) You can use another database / programming environment.

                                   

                                  Any or all of these is fine with me. I don't have a dog in the fight.

                                   

                                  Mike

                                  • 14. Re: Calc engine questions for optimizations
                                    BruceHerbach

                                    Hi Vincent,

                                     

                                    In a complex database I have been working on for a while this issue of Unstored calcs and performance was a big problem.  The solution was to convert all of the Unstored calculations to auto-enter calculations.  In most cases,  a table would have multiple fields that had to be updated.  Not wanting to run multiple replace statements in a script to update all of the fields,  I added a trigger field to the table.  Then in all of the fields that needed to be updated I added  the trigger field to a let statement in the calculation.  To force the record to update all of the calculations,  I just had to change the value of the update field.  FileMaker's internal dependancy tree took care of the rest.

                                     

                                    The key to all of this is the FileMaker dependancy tree.  Changing any value in the local table that is included in the calculation forces the calculation to re-calculate.  So even if all the key information comes from other tables changing the update trigger field forces the calculation to go out pull in the current information from the other fields/tables and calculate the correct value. 

                                     

                                    Script triggers can be very helpful,  If a displayed field requires that a fields in other tables be updated,  An OnModify trigger and script can go out an update all if the related fields with a single replace statement per table.  In the system I am working on,  at time this can mean 100s or even 1000s of records are updated.  In most cases,  the update takes anywhere from less then a second for a small record count to 10+ seconds for the higher record counts.  When scrolling through the display portals there is no delay since all of the data is stored and key fields are indexed. 

                                     

                                    FileMaker has a great deal of capability here.  A developer who is willing to dig in can take a system with these type of performance issues and make great improvements. 

                                     

                                    Good luck with your system. 

                                    HTH

                                    Bruce Herbach

                                    1 2 Previous Next