1 2 Previous Next 29 Replies Latest reply on Mar 1, 2012 6:17 PM by Patrick

    Unstored Calculations

    Patrick

      I am creating a solution to price windows/doors etc...

      It's my first solution in Filemaker and it's actually coming along quite nice. It is being developped for the iPad.

      I am however running into some kind of snag. I have alot of unstored calculations and the further into the development I get the slower the whole system runs to the point of even bogging down my computer a little bit.

      Do unstored calculations take up alot of computing power?

      I have very little experience in thie field and would like any insight into this please?

       

      The reason I am using unstored calculations versus auto entered calculations is they update automatically on the fly as sizes are changed or as options change. I tried auto enter calculations but it seems I have to use refresh scripts for it to update properly and that is almost as annoying as the slowing down from the unnstored. I hope I've explained this properly.

        • 1. Re: Unstored Calculations
          MarcDolley

          Is there a reason the calculations are unstored and not indexed? Unstored calculations only produce a result when the result needs to be visible to the user or are required by some other process. They take up very little room in the database, but can slow things down.

           

          Perhaps you should provide some examples of the calculations so we can assist you better.

           

          Regards

          Marc

          • 2. Re: Unstored Calculations
            Patrick

            Here's a list of 7 such unstored calculations in my "build" table. I am sure there's an easier and more refined way to do alot of these but as I said I am new and just learning the ropes. In the same table I have another 21 such calculations for a total of 28

             

             

            w_Width * w_Height / 144

            ------------------------------------------------------------

            ((w_Width * 2) + (w_Height * 2))/12

            -------------------------------------------------------------

            Case ( w_Type = "Single Hung"

            or w_Type = "Single Slider"

            or w_Type = "Double Hung"

            or w_Type = "Double Slider"

            or w_Type = "Double End Single Slider"

            or w_Type = "Double End Double Slider";

            "Small Fixed";

            w_Type = "Casement"

            or w_Type = "Awning" ;

            "Large Fixed" )

            ------------------------------------------------

            w_Series &

            " " &

            w_Type &

            " window. " &

            w_Division &

            ", " &

            w_Operation &

            " operation. " &

            "Window comes with the following options... " &

            w_Glass &

            ". " &

            Case (w_BM = "NONE"; ""; w_BM &  ". "  )

            &

            Case (w_Jamb = "NONE"; ""; w_Jamb &  ". "  )

            &

            Case (w_Grill = "NONE"; ""; w_Grill &  ". "  )

            &

            Case (w_Multipoint = "NONE"; ""; w_Multipoint &  ". "  )

            &

            "Window to be installed as " &

            wl_Type &

            " on the "

            & wl_Floor &

            "."

            ------------------------------------------------------------------

            Case (

            w_SQFT. ≥ 0 and w_SQFT. <  12 ;"0-12" ;

            w_SQFT. ≥ 12 and w_SQFT. <  15 ; "12-15" ;

            w_SQFT. ≥ 15 and w_SQFT. <  20 ; "15-20" ;

            w_SQFT. ≥ 20 and w_SQFT. < 30 ; "20-30" ;

            w_SQFT. ≥ 30 and w_SQFT. < 35 ; "30-35" ;

            w_SQFT. ≥ 35 and w_SQFT. < 45 ; "35-45" ;

            w_SQFT. ≥ 45 ; "45-999" ;

            )

            -------------------------------------------------------------

            p_w_BrickmouldLf * w_Perimeter

            -------------------------------------------------------------------

            Case ( w_Type = "Double End Single Slider" or w_Type = "Double End Double Slider" ;

            Case ( w_Op1SQFT  < 12 ; 12 * p_w_Op1 ; w_Op1SQFT * p_w_Op1 ) ;

            w_Op1SQFT   ≥  12 ;  w_Op1SQFT * p_w_Op1  ;

            p_w_Op1)

            • 3. Re: Unstored Calculations
              Patrick

              I've ben using unstored calculations because, as I mentioned earlier, when they are stored, they don't seem to update as efficiently when the values are changed. Maybe I was doing something wrong?

              • 4. Re: Unstored Calculations
                jason.delooze

                Unstored calculations place a burden on the processor; stored calculations are much better.

                 

                Looking at your calculations, there is some room for efficiency improvements - although each would be only slightly faster to evaluate.  For example,

                 

                Case (

                w_SQFT. ≥ 0 and w_SQFT. <  12 ;"0-12" ;

                w_SQFT. ≥ 12 and w_SQFT. <  15 ; "12-15" ;

                w_SQFT. ≥ 15 and w_SQFT. <  20 ; "15-20" ;

                w_SQFT. ≥ 20 and w_SQFT. < 30 ; "20-30" ;

                w_SQFT. ≥ 30 and w_SQFT. < 35 ; "30-35" ;

                w_SQFT. ≥ 35 and w_SQFT. < 45 ; "35-45" ;

                w_SQFT. ≥ 45 ; "45-999" ;

                )

                 

                could be re-written as

                 

                Case (

                w_SQFT. < 0 ; "Error" ;

                w_SQFT. <  12 ;"0-12" ;

                w_SQFT. <  15 ; "12-15" ;

                w_SQFT. <  20 ; "15-20" ;

                w_SQFT. < 30 ; "20-30" ;

                w_SQFT. < 35 ; "30-35" ;

                w_SQFT. < 45 ; "35-45" ;

                "45-999"

                )

                 

                Realizing that an iPad is not nearly as capable as a laptop or desktop in terms of processor speed, memory, or network connection, it should be expected that processing will take more time.  However, you also mentioned that "whole system runs to the point of even bogging down my computer a little bit" - meaning that it runs slow on a laptop as well as the iPad.  Makes me wonder if something else is having a factor in what you're experiencing.

                • 5. Re: Unstored Calculations
                  Patrick

                  I can see stored calculations being faster as they get indexed and easier for the system to find the necessary value. However, as a test, I turned 2 of my calculations into a stored calculation and I I entered the data they did not do the necessary calculation.

                   

                  It's frustrating and challenging at the same time, I've actually created something I am quite proud of and proven to myself I can do it but when I turn it all on it's slow to work with. If there are any other insights out there I would love to hear it.

                  • 6. Re: Unstored Calculations
                    comment

                    Patrick wrote:

                     

                    I've ben using unstored calculations because, as I mentioned earlier, when they are stored, they don't seem to update as efficiently when the values are changed.

                     

                    Stored calculations update when a referenced field in the same record is modified. This is a yes/no proposition - it has no efficiency attribute. I don't see any reference to related fields in your formulae, so there is no reason why they cannot be stored (unless some of those fields are global).

                     

                    An unstored calculation recalculates whenever the screen is refreshed. The more unstored calculations you show, the slower the refresh.

                    • 7. Re: Unstored Calculations
                      jason.delooze

                      Are you saying that, on your computer (not the iPad), you made these 2 fields stored calculations, enetered some data that should have caused the fields' values to change, but they didn't change?  Strange.

                       

                      Can we see the field definitions for the table in this database?  A screenshot like the following would help us.

                       

                       

                      I'm curious how your fields are defined - which of the above field definitions you are using.

                      • 8. Re: Unstored Calculations
                        jason.delooze

                        Trying again ...

                         

                        Are you saying that, on your computer (not the iPad), you made these 2 fields stored calculations, enetered some data that should have caused the fields' values to change, but they didn't change?  Strange.

                         

                        Can we see the field definitions for the table in this database?  A screenshot like the following would help us.

                         

                        Screen shot 2012-02-29 at 10.38.01 AM.png

                         

                        I'm curious how your fields are defined - which of the above field definitions you are using.

                        • 9. Re: Unstored Calculations
                          Patrick

                          I've been playing around with my fields alot so they are no longer what they were when I first originally posted this message. I've managaed to eliminate about half of my unstored calculations thus far and things are running smoother. It's a long process.

                           

                          The field I said which wasn't updating as a stored calculation is now but only after I changed the other unstored calcs into stored calcs. Kind of a strange behavior but things as I am changing them things are running smoother.

                           

                          When I first originally was writing it, things weren't working properly with the stored calcs, now they are. I've probably changed some of the other fields since then o be easier to evaluate. I don't know...

                          • 10. Re: Unstored Calculations
                            Stephen Huston

                            Look into the field definition option for non-calc field types to auto-enter data based on a calculation.

                             

                            This lets the result be stored while allowing it to update based on other fields being edited in the same record. You can also add the Evaluate function (read the tech stuff on this in Help) so that you set which fields will trigger the updating of stored data in the target field.

                             

                            The difference between stored data ato-entered via an Evaluated calculation vs the unstored calc having to resolve on every such field in all records can be absolutely huge. And when its cached across a server network, that difference  will increase by several times. On a WAN connection it can be over 100X !

                             

                            Use unstored calculation ONLY when nothing else will work at all if you want the best performance.

                            • 11. Re: Unstored Calculations
                              comment

                              Stephen Huston wrote:

                               

                              Look into the field definition option for non-calc field types to auto-enter data based on a calculation.

                               

                              This lets the result be stored while allowing it to update based on other fields being edited in the same record. You can also add the Evaluate function (read the tech stuff on this in Help) so that you set which fields will trigger the updating of stored data in the target field.

                               

                               

                              IMHO, you should always use a field of type Calculation to do your calculations - unless you have a really good reason not to. An example of a good reason is when you want users to be able to override the result manually.

                               

                              Also, you should use the Evaluate() function only when the expression to be evaluated itself needs to be calculated.

                              • 12. Re: Unstored Calculations
                                Stephen Huston

                                I prefer the Evaluate function because it allows full control over which fields can trigger a re-entry of the latest data, not just fields referenced in the calculation itself.

                                 

                                I also prefer non-Calc fields when possible because of more reliable updating under the conditions I choose, which can be either scripted or auto-entered via calc.

                                 

                                This is also my OPINION. I do use calcs, but I never choose an unstored calc as my preferred option if there is another way to go.

                                 

                                We may differ in our opinions, which is great. It's one of the joys of this forum, and a source of lots of options to the people learning from these postings.

                                 

                                My first rule of thumb in deciding how to do anything in FileMaker:

                                • If you can only think of one way to do it, think harder until you have found another option, then you can choose the better way.

                                 

                                There is almost nothing that cannot be done more than one way in FileMaker. It gives us choices for almost everything.

                                • 13. Re: Unstored Calculations
                                  comment

                                  Stephen Huston wrote:

                                   

                                  I prefer the Evaluate function because it allows full control over which fields can trigger a re-entry of the latest data, not just fields referenced in the calculation itself.

                                   

                                  I am afraid you are confusing between cause and effect. The Evaluate() function needs the fields parameter because the expression is a text string. As such, no fields referenced in the expression itself are included in the dependency table for that field. It is not necessary to use the Evaluate() function to add fields that will trigger a re-evaluation; you can simply "mention" them in the formula, e.g.:

                                   

                                  Let (

                                  trigger = Somefield

                                  ;

                                  Get ( CurrentDate )

                                  )

                                   

                                  OTOH, when using Evaluate(), the expression parameter must be evaluated first, so =

                                   

                                  Evaluate ( "Get ( CurrentDate )" ; Somefield )

                                   

                                  is first translated into =

                                   

                                  Get ( CurrentDate )

                                   

                                  and only then evaluated "for real". IOW, this is an inefficient use of computing resources.

                                   

                                   

                                  Stephen Huston wrote:

                                   

                                  I also prefer non-Calc fields when possible because of more reliable updating under the conditions I choose, which can be either scripted or auto-entered via calc.

                                   

                                  I don't think "more reliable updating" is a good choice of words here; there's nothing "less reliable" in the way calculation fields update.

                                   

                                   

                                  Stephen Huston wrote:

                                   

                                  There is almost nothing that cannot be done more than one way in FileMaker. It gives us choices for almost everything.

                                   

                                  That's true, but there are pros and cons to each way that should be understood when making that choice.

                                  • 14. Re: Unstored Calculations
                                    Patrick

                                    Alot of useful information here, much appreciated.

                                     

                                    In my 'Window' table and layout, which is the table that, put simply, builds the window for me and adds the pricing for all the various parts to come up with a total cost of that window is now updating and working properly with stored calculations. That part of it has increased performance on that layout by quite a bit. Appreciate the help!

                                     

                                    Now, I have a 'Quote' table and layout which is where the 'windows' I've created are displayed in a portal. When I use an unstored calculation to carry over the total cost of the window from my 'window' table to the 'quote' table it works. When I change it to an unstored calculation it does not.

                                     

                                    Any ideas as to why this would behave this way?

                                    1 2 Previous Next