1 2 3 4 Previous Next 56 Replies Latest reply on Apr 30, 2014 6:18 PM by sanjuan787@gmail.com

    Performance Examples for a DevCon Session

    matthew_odell

      Hello there members of TechNet,

       

      As some of you may (or may not) have seen, I will be doing a session at this year's DevCon entitled "Calculations: TechNet Special". We ran a poll (https://fmdev.filemaker.com/polls/1082) and it helped me formulate an idea of what I should cover.

       

      I'm going to be talking about Calculation performance, and how using Case, Let, Evaluate, and ExecuteSQL can effect performance. I have a few good examples that I've collected over the years, but since this is supposed to be a TechNet special, I figured I'd reach out to the community at large for more examples.

       

      I'm looking for two things:

       

      1. Examples of calculations that you've optimized yourself and/or lessons you've learned over the years. It would be great if you had the before or after calculations, or the technique you're using to help with performance (script triggers, scheduled scripts, etc...). I'll already be covering examples like using Let to only calculate a segment once and using Case to short circuit a calculation, so if you have good examples of calculations you've fixed with those techniques, I'd love to see them.

       

      2. Examples of calculations that you're currently having issues with and would like me to review. I can't promise that I'll provide insight to everyone that replies, but I'll try to give a glance at every example that is sent my way and help if I can. And if it works really well, then you will see your stuff promoted at DevCon during my session.

       

      Thanks in advance to anyone that replies. I hope that we can all learn a little bit together. See you all at DevCon,

       

      Matthew O'Dell

      Technical Marketing Manager

      FileMaker

        • 1. Re: Performance Examples for a DevCon Session
          Malcolm

          1. Examples of calculations that you've optimized yourself and/or lessons you've learned over the years.

           

          1. the use of zero and one as boolean flags

          2. writing formulae which meet all conditions without the need for if statements.

           

          The important rules for zero and one are:

           

          x ^ 1 = x

          x ^ 0 = 1

           

          x * 1 = x

          x * 0 = 0

           

           

          Use the power operator if the data is used for multiplication or division:

           

          old: if ( discount allowed = "Yes" and not isempty(discount) ;  discount; 1 )

           

          new (%): discount ^ discount allowed

           

          An example.

           

          Let (

          [

            tax = (1 + tax rate) ^ tax status ;

            discount = ((1 - discount rate) ^ discount allowed)

          ];

            Price * tax * discount

          )

           

           

          Use the multiplication operator when performing addition and subtraction or when positive and negative values need to be preserved.

           

          old :  IF( discount allowed = "TRUE" ; discount amount ; 0 )

           

          new ($):  discount amount * discount allowed

           

           

          An example.

           

          Let (

          [

          discount = discount amount * discount allowed

          ];

          ( Price * tax ) - discount

          )

           

           

          I know it looks simple but it provides the ability to place a field into any calculation. The boolean value controls the action of the operator and I know that the formula is safe and valid for all values.

           

          It has an additional benefit because there is no logical fork. In these examples the fork is simply one value or another. In real life the forked calculations may become complex and may be nested. 

           

          Malcolm

          • 2. Re: Performance Examples for a DevCon Session
            wimdecorte

            Do you have any metrics on the performance advantage of this approach?

            • 3. Re: Performance Examples for a DevCon Session
              jbante

              With ExecuteSQL, experimenting with the order of joins and match conditions can make a HUGE difference. You can walk blindly through any mid-sized or larger solution and trip over a dozen examples. In one particularly extreme example, I started with an outer join that took 6 seconds to execute, split it into two separate queries and spliced the results together with a custom function instead, which got the performance down to 20 milliseconds.

              • 4. Re: Performance Examples for a DevCon Session
                jbante

                My implementation of the inverse error function uses memoization to skip steps already completed by a previous call to the function if the call is within the same local context.

                • 5. Re: Performance Examples for a DevCon Session
                  matthew_odell

                  Thanks Malcolm. I was actually speaking with one of the engineers about something similar yesterday. He mentioned that any time you can get the calc engine to do math operations vs. comparison or text finding (patterncount, position, etc...), the math operations will always be faster. This looks like a great example of that.

                   

                  If possible, I'd second Wim's message and ask for any metrics you might have between the two approaches.

                   

                  Thanks!

                  • 6. Re: Performance Examples for a DevCon Session
                    matthew_odell

                    Hey Jeremy, any chance you have the before and after examples of this? This is definitely something I plan on mentioning, so any hard evidence would be great to show. Thanks.

                    • 7. Re: Performance Examples for a DevCon Session
                      jbante

                      I'm not at liberty to share the original solution where the outer join example came up, but I'll see if I can distill the salient parts.

                      • 8. Re: Performance Examples for a DevCon Session
                        matthew_odell

                        Even if it's not the solution but the query itself (with field names changed, and a list of which ones are indexed) would be extremely helpful. Just the calc before and after is all I'm after. Let me know.

                        • 9. Re: Performance Examples for a DevCon Session
                          TonyWhite

                          I think that the interaction between calculation performance and calculation

                          readability would be an interesting topic to work in.

                           

                          Can you have both?

                          Example Q1: Can setting a variable to the value of a field or a Get function

                          inside a Let calc optimize both the speed and the readability?

                           

                          Example Q2: does removing GetAsText ( data ), GetAsNumber ( text ) from

                          legacy code based on a rock solid understanding of data casting yield a

                          significant increase in speed to go along with the increase in readably?

                           

                          Are there times where increasing speed requires code that is harder to read?

                          Are there available metrics on speed comparisons to enable developer to

                          choose between optimizing speed vs. readability based on the nature of the

                          project. Big vs. small, etc.

                           

                          This one might be out of scope...how do you use indirection (Evaluate,

                          ExecuteSQL, etc.) and at the same time protect against code breaking from

                          renaming objects?

                           

                          Hope that helps.

                           

                          All the best,

                           

                           

                          Tony White

                          Tony White Designs, Inc.

                          Tel: 646-714-2797 (Google Voice)

                          Tel: 718-797-4175

                          tony_white@twdesigns.com

                          http://www.twdesigns.com

                          • 10. Re: Performance Examples for a DevCon Session
                            Malcolm

                            Execution is faster but I don't have  measurements. Speed of execution wasn't the driving reason. I began making the change to make the programming simpler and to provide a good foundation for large data sets. I can recommend it for that reason alone. I knew that core maths routines would be highly optimised and that these simple formula would take advantage of that.

                             

                            For any single calc the difference won't  be noticed. The difference  becomes apparent when you do summary reports, they can be really quick. The anecdotal evidence is strong, everytime we modify a solution using these methods the clients actually ask us what we did.

                            • 11. Re: Performance Examples for a DevCon Session
                              Malcolm

                              Are there available metrics on speed comparisons to enable developer to

                              choose between optimizing speed vs. readability based on the nature of the

                              project. Big vs. small, etc.

                              The trick is that you have to know how much time you'll save versus how much time you'll spend. There's a ready reckoner linked below that illustrates the problem

                               

                              http://xkcd.com/1205

                              • 12. Re: Performance Examples for a DevCon Session
                                matthew_odell

                                Malcolm, you stole my thunder. I was going to show that at my session!

                                • 13. Re: Performance Examples for a DevCon Session
                                  matthew_odell

                                  Hey Tony, you bring up some good points, and I'm sure that I can fit in a few, but I should warn that this session is currently scheduled as a "Core/Beginner" session. I'm definitely going to be going into some difficult concepts, but some of these are probably a bit off of the reservation.

                                   

                                  That said, you bring up a good point that I'd probably add. Most of the cases I'm showing (using let, using case, etc....) can be much more readable than doing the same as one long statement (which so many new people do). Can you think of specific examples that are more readable but less performant?

                                  • 14. Re: Performance Examples for a DevCon Session
                                    Malcolm

                                    Malcolm, you stole my thunder. I was going to show that at my session!

                                     

                                    That's the internet for you. There are billions of pages to choose from and we end up downloading the same cartoon.

                                     

                                    Malcolm

                                    1 2 3 4 Previous Next