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

# Speed of Case function vs sequence of logical operators

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

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

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

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

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

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

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

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

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

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

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

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

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!".