9 Replies Latest reply on May 31, 2012 5:22 PM by steve_ssh

    Reducing calculation over head.

    rp2524

      I have a solution that tracks billing / invoicing. I have a calculation that tracks Billables and it’s slowing down my solution. Total Bilable is a calculation field :

      If ( DateJob ≥ Date ( 5; 1; 2011) and PickType = "Worked" and T15c_products_INVOICE_LINE_ITEMS||id_products|::Status_Billable "1"; (PayTotal + CalM_Bill_Payroll_Town + CarTotal)). I also Summarize the field, which is where I see it slowing down the solution. Can someone give me alternative way to write the calculation to reduce the over head? This is my first attemt at posting, so please excuse any error in etiquette.

        • 1. Re: Reducing calculation over head.
          Vaughan

          FileMaker needs time time to perform calculations, there is no way around this problem. There are a couple of ideas:

           

          get faster hardware to perform the calculations in less time

          compute the calculations and remember the result so it never gets calculated again

           

          The second option is only possible if the data never changes, or changes infrequently. If it changes there needs to be a process to re-calculate else inaccurate data gets presented to the user.

          1 of 1 people found this helpful
          • 2. Re: Reducing calculation over head.
            rp2524

            Thank You,

            • 3. Re: Reducing calculation over head.
              keywords

              If ( DateJob ≥ Date ( 5; 1; 2011)  and PickType =  "Worked" and T15c_products_INVOICE_LINE_ITEMS||id_products|::Status_Billable   "1"; (PayTotal + CalM_Bill_Payroll_Town + CarTotal))

               

              Your calculation is reasonably complex. It references three different fields in the "test", one of which is from a related table, for three conditions which must be met. Because of the related field the calculation is non-indexable. Then it references a further three fields to deliver the result. If any/all of these are also calculations then the effect compounds. When you attempt to summarise the result, FileMaker has to calculate the result for each record in the summary before it can compile the summary result. If you have more than a few records in your found set it's going to be slow.

               

              You could try testing each side of the calculation separately (the "test" by posting a simpler result such as a 1; and the "result" portion as a calculation on its own) to see which is slowing things down more, then trying other ways to get to where you want to go.

               

              Hope that's of some help.

              • 4. Re: Reducing calculation over head.
                rp2524

                I will try what you suggest.  Currently I have about 22,000 records and it continues to grow, so see my concern. Thank you for your input.

                 

                 

                Sent from my iPad

                • 5. Re: Reducing calculation over head.
                  BruceHerbach

                  Is it possible to script the calculation and use Set field to update the field? Use a Script trigger or tie the field update to an add record process?

                   

                  The set up for this may be a bit complex and require some testing to be sure that you have everything working correctly.  The advantage would be that the field would become a number field with stored data and could be indexed.  This could greatly speed up display of information and scrolling through portals.

                   

                  HTH

                  Bruce

                  • 6. Re: Reducing calculation over head.
                    rp2524

                    This is an interesting approach. I will do some testing.

                     

                    Bob Perez

                    Tel# (908)752-7620

                    Sent from my iPhone

                    • 7. Re: Reducing calculation over head.
                      steve_ssh

                      Hello Bob,

                       

                      Starting with your current calculation implementation of:

                       

                      If ( DateJob ≥ Date ( 5; 1; 2011)  and PickType =  "Worked" and T15c_products_INVOICE_LINE_ITEMS||id_products|::Status_Billable ≠  "1"; (PayTotal + CalM_Bill_Payroll_Town + CarTotal))

                       

                      I would consider re-working it as follows:

                       

                      Case(

                       

                          DateJob < Date( 5; 1; 2011 ); "";

                       

                          PickType ≠ "Worked"; "";

                       

                          T15c_products_INVOICE_LINE_ITEMS||id_products|::Status_Billable ≠  "1";  PayTotal + CalM_Bill_Payroll_Town + CarTotal;

                       

                          ""

                      )

                       

                       

                      The above is based on a couple of premises:

                       

                      Premise 1:

                       

                      The CASE function will "bow out" and quit evaluating further conditions down the line when it reaches a matching condition, whereas using the AND conjunction in your test statement will require FM to evaluate all three conditions joined by the AND.

                       

                      Premise 2:

                       

                      Getting the related Status_Billable value is the most costly condition to evaluate with respect to time overhead, and we should avoid evaluating that condition whenever possible.

                       

                       

                      Given Premises 1 and 2:


                      We strive to evaluate our conditions in an order where we only find ourselves having to evaluate the "heavy" condition in the case where we weren't able to use the "lighter" conditions alone to determine our answer.  More specific to your example: If we can determine the final value without having to evaluate the Status_Billable value, we do so.  We can do so in such cases as the first two conditions tested in the CASE statement above.

                       

                       

                      Notes:

                       

                      1) My presuming that Status_Billable is the heaviest is simply a hunch -- I'm completely guessing that DateJob and PickType are likely to be "harmless" fields which are local and stored. If this is not the case, and these fields are unstored, then the concept based on premise #1 may still be worth considering, but the implementation that I suggest above may need to be re-worked.

                       

                      2) Even if my assumption about DateJob and PickType being harmless is true, the amount of performance increase that my suggestion offers will depend on how many times the calculation restructuring was able to save you from having to evaluate the heaviest condition:

                       

                         - If you have oodles of records, and most of them have a DateJob value ≥ 2011 May 1, as well as a PickType value of "Worked", then the restructure may not save you much overhead.

                       

                         - If, on the other hand, you have oodles and oodles of records where DateJob < 2011 May 1, or PickType ≠ "Worked", you might notice some peformance increase.

                       

                      3) While I think it's worth pursuing the change that I suggest, I have to acknowledge what others have already said: If you are performing summaries on an unstored calc, your overhead situation isn't going to get any happier as time goes on.

                       

                      Please let me know if I haven't been clear enough about this.

                       

                      Best,

                       

                      -steve

                      • 8. Re: Reducing calculation over head.
                        rp2524

                        Steve,

                         

                        Your suggestion is spot on.  I have been testing it and had a notable performance improvement.  I appreciate you taking the time to analyze my calculation. As an added bonus, you have given me a new perspective on calculations in general.

                         

                         

                        Again, thank you. 

                         

                        Bob

                        • 9. Re: Reducing calculation over head.
                          steve_ssh

                          Hi Bob,

                           

                          Thanks for letting me know how it worked out.  I'm very pleased to hear that this was of some help.

                           

                          Very best,

                           

                          -steve