Use Let( ) Function so that you only have to ask your database for your field information once
a = YourField
a = 1; "Do this";
a = 2; "Do that";
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.
Thank you for the suggestion Johan, but the tests use many different fields.
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.
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
Test it for yourself
Loop a few thousand times, and use Get(CurrentTimeUTCMilliseconds) as a timer
Let us know what you find
> Which of the two would be faster ?
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?
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.
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.
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
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!".