11 Replies Latest reply on Dec 13, 2016 5:14 AM by taylorsharpe

    Speed of Case function vs sequence of logical operators

    FrankvanderMost

      Let's say, I need a function that delivers 1 when a number of tests A, B, C, ... L have been passed and otherwise 0

       

      Would it be faster to use a Case function like this

       

      Case (

           not A ; 0 ;

           not B ; 0 ;

           ...

           not L ; 0 ;

       

           1)

       

      or to use a logical formula like:  A and B and ... L ?

       

      I am assuming that the Case function will stop evaluating as soon as one of the tests 'not X' have been met.

       

      Is the calculation engine sophisticated enough to stop evaluating the logical formula as soon as one of the tests A to L results in 0 ?

       

      Which of the two would be faster with a given set of outcomes for A to L?

        • 1. Re: Speed of Case function vs sequence of logical operators
          Johan Hedman

          Use Let( ) Function so that you only have to ask your database for your field information once

           

          Let(

          a = YourField

          ;

          Case(

          a = 1; "Do this";

          a = 2; "Do that";

          "Nothing"

          )

          )

          • 2. Re: Speed of Case function vs sequence of logical operators
            BowdenData

            Go with Case function because it will stop evaluating when it has a match as you suggest. Also, arrange the "tests" in order of most common occurrence to match. Put the scenario that will most often evaluate to True as the first Case test, the second most like scenario next, and so on.

             

            I recall that one of the larger FileMaker developer shops in the community tested this and found that the Case test order could make a measurable performance difference.

             

            Doug

            • 3. Re: Speed of Case function vs sequence of logical operators
              FrankvanderMost

              Thank you for the suggestion Johan, but the tests use many different fields.

              • 4. Re: Speed of Case function vs sequence of logical operators
                FrankvanderMost

                Thank you for your quick answer, Doug.

                 

                Somehow, I was hoping that the calculation machine would be more sophisticated. If it were, it should be a lot faster, since the Case statement allows for a far wider use (outcomes can be of multiple types, not just logical) than the logical operators, it probably also takes more time to process it.

                 

                Anyways, I'll stick with the Case function.

                • 5. Re: Speed of Case function vs sequence of logical operators
                  Johan Hedman

                  As soon as you ask the database field for a second time you have lost speed if you do not use Let .

                  Like BowdenData writes use most common match first. The combination of this two will create the best performance for you

                  • 6. Re: Speed of Case function vs sequence of logical operators
                    gdurniak

                    Test it for yourself

                     

                    Loop a few thousand times,  and use  Get(CurrentTimeUTCMilliseconds)  as a timer

                     

                    Let us know what you find

                     

                    greg

                     

                    > Which of the two would be faster ?

                    • 7. Re: Speed of Case function vs sequence of logical operators
                      erolst

                      Frank van der Most wrote:

                      Is the calculation engine sophisticated enough to stop evaluating the logical formula as soon as one of the tests A to L results in 0 ?

                      You can test this quite easily with the expression

                       

                      0 and Let ( $$noShortCircuit = 1 ; 1 )

                       

                      After evaluating it, does $$noShortCircuit exist?

                      • 8. Re: Speed of Case function vs sequence of logical operators
                        FrankvanderMost

                        Well, why not ...

                         

                         

                        See the attached file.

                         

                         

                        First, I did three tests, one with no calculation result (as in current script in the file), one with logical operators and one with a case statement. 100000 loops. 10 logical tests. I ran on a client FMP 13 on a MacBook Pro 2.6 Ghz Intel Cori i5. No other application were running besides TextEdit for copy-pasting of the different calculations

                         

                         

                        I noticed that I didn't get the same result in milliseconds each time with the very same script so each test involved 10 test runs.

                         

                         

                        Here are the averages when all logical tests A to J have value 1. It means that both types of calculations would have to test all 10 to reach the end result.

                         

                         

                        No calculation: 5212

                         

                        Logical operators: 5723

                         

                        Case function: 5992

                         

                         

                        Let's subtract the no-calculation time from both the other two:

                         

                        Logical operators: 511

                         

                        Case function: 780

                         

                         

                        That would make the Case statement about 50% (269/511 ≈ 0,53) slower than the logical operators.

                         

                         

                         

                         

                        Next, I did three more tests. This time with A = 0 and B to J are 1. According to our theory, the Case function should show an increase in performance.

                         

                        Here are the averages

                         

                         

                        No calculation: 5496

                         

                        Logical operators: 5737 - 5496 = 241

                         

                        Case function: 5906 - 5496 = 410

                         

                        Here the Case statement is about 70% (169/241 ≈ 0,70) slower

                         

                         

                        That is unexpected. Both the logical operators calculation and the case function have sped up by about 50%. So, perhaps the calculation engine is not that simple when it comes to logical operators. If so, then logical operators are the way to go, rather than the Case function.

                         

                        Did I overlook something, or made a mistake? I also attached an XL file with my measurements

                         

                        The weirdest bit seems to me that the 'no calculations' test is almost 300 millisecs slower because one variable was set to 0 instead of 1.

                        • 9. Re: Speed of Case function vs sequence of logical operators
                          FrankvanderMost

                          Why did I not think of that? That would have saved me the past hour on testing!

                           

                          Well, at least I learned that the processing of logical operators is as sophisticated as I hoped.

                          • 10. Re: Speed of Case function vs sequence of logical operators
                            gdurniak

                            It's tricky when the difference is so small

                             

                            Since scripts are interpreted,  not compiled,  just the fact that the Case Statement has more text may make a difference

                             

                            e.g.  if you put a comment in the loop,  it will run slower as well

                             

                             

                            > Here the Case statement is about 70% slower

                            • 11. Re: Speed of Case function vs sequence of logical operators
                              taylorsharpe

                              You have to be careful with drawing conclusions on very small time differences.  But it is good to look at such data and try to make inferences on what calculations are preferred.  Note that there are other variables too such as whether you are comparing text verses numbers which can make a big difference, or the number of comparisons, etc.  However, i have so noted that the conclusion you have drawn is not what I would have expected that the Case statement is slower and it is worth some more investigation.  So thank you for sharing that with us and it is good "food for thought!".