Jump to content

This topic is 7981 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

One of the best things I learnt from FM Forums was to use the CASE function instead of nested IF calculations. Recently I have been wondering - their is no reason to use the IF function ever. For simple calculations that would require only one IF function I now just use the CASE function. Logically there is absolutely no difference. As I do not how FM interprets data I was wondering wether IF may be better than CASE in these situations. Any ideas?

Posted

If you have only one condition, both work the same.

CASE(1=1;1;0) is identical to IF(1=1;1;0)

If you have multiple statements, CASE evaluates and returns on the FIRST condition that is true.

with nested IF statements, every condition is checked. But the same is true, if you are using nested CASE statements with only one condition.

Basically, the IF statement is there for compatability with older FileMaker versions.

Posted

Thanks Christian

Now I understand why both are available. I am still curious if for any reason one requires more computing. If I have a database with 50 calculation fields that have simple conditions (like above) and their are 100 000 records in the database it is in effect 5 million small calculations. Being pedantic, Filemaker may use just a little more power for CASE than IF. I want to use calcs that are always the best possible, no matter how slight the difference.

If anyone knows more about how FM would interpret the calculation then please let me know if there is any "power" difference.

Peter

Posted

Being pedantic, Filemaker may use just a little more power for CASE than IF

Hi,

Actually it is the other way around.

As Christian says, in single fork expressions, the two functions are broadly equivalent (including from a procesws efficiency perspective). However in compound expressions, a single Case() function can be more efficient than a nest of If()s.

In many practical applications, however, (esp in smal to moderate sized solutions) the added convenience and friendliness of the Case() syntax provides a more valid incentive for its use than any marginal contributions it may make to improved performance.

HTH

Posted

Hi There

I clearly understand that in compound expressions Case() is more efficient than If().

And continuing to be pedantic (and with absolutely no idea what is going on behind-the-FM scenes) the reason why I thought Case() may require more processing than If() was based on a logical thought process as follows:

In the If() scenario their can only be 3 parts (ie: test1, result1, result2). In the Case() scenario there can be an unlimited number of parts. Yes, in our simple example there were only 3 parts but I assumed Filemakers evaluation process for Case() was "prepared" for the potential of an unlimited number of parts - hence more processing. Sure, whether I was right or wrong the effect is probably so negligible it virtually has no effect.

Christain says FM checks every condition in a nested IF() calculation - this I don't understand. If the first condition is met then why continue assessing the balance of the calculation?

If (test, result1,result2). In a nested If() calculation every If() is part of result2, except the first one. Since the condition has been met FM will return result1. It is futile for FM to assess the multitude of nested If's since they are part of result2.

I hope I m not taking this debate in circles (I may be overtired) - but I need to find resolution before I move on.

Pete

Posted

Hi Peter,

I can see your line of reasoning. However it's my understanding that, prima facie, both the Case( ) and If( ) functions issue a single call to the CPU (which in most operating systems is likely to consume a single processor cycle), plus a further call for each test and a further call for retrieval of each referenced value (whether as part of a test or result). That being the case, as I observed above, with a single test and result pair, the functions would be present an equivalent processor load.

I don't think that anyone would disagree with you about the futility of resolving all the tests in a series of nested If( ) functions - it is self evidently inefficient. If( ) is and always has been fine for single fork expressions, but has flaws of both syntax and efficiency which emerge when the logical thread is extended. Rather than alter the behaviour of the If( ) function (which may have had implications for backward compatibility) the Case( ) function was introduced with the release of FMPv3 to provide a more efficient option for expressions with multiple logical tests.

Posted

Thanks Ray

Although I still have questions regarding this matter the debate has been useful in understanding a little more about Filemakers methodology. I believe my curiousity on such a seemingly pedantic matter does stem from my lack of knowledge about what Filemaker is really doing. In time, when I have a better grip of the application, I do feel similar analysis can be useful.

But for now, Case closed!

Pete

Posted

Hi Dj,

I've seen a number of 'tests' of this type.

However all they show is that FileMaker's internal table of dependencies is working correctly. The opening of a related file is expected behaviour given the role and function of the table of dependencies, which is referenced whenever any calculation is to be executed.

The table of dependencies simply records the fact that a particular calculation is dependent on other calcluations, relationships and/or values and confirms that they are available before submitting the calculation into process. In order to behave differently, the table of dependencies would have to evaluate the calculation first in order to determine the dependencies for each context - which would create a circularity. Instead it simply stores and ratifies all dependencies for each calc.

It is not my understanding that the fact that the table of dependencies is doing its job, has any bearing at all on what tests or results are - or are not - being evaluated. I'm therefore inclined to give greater credence to the various processor load tests which have shown that, for whatever reason, a compound Case( ) expression out-performs a comparable nest of If( ) functions. wink.gif

Posted

I just ran a speed test looping 10000 times, setting a field to an If calc vs. a Case calc.

With a single (non-nested) calc, If(1, "one", "zero"):

both If and Case averaged 18 sec.

With 10 nested Ifs, (see calc below):

If averaged 21 sec., but Case came in at 19 or 20 seconds.

If(1, "one",

If(2, "two",

If(3, "three",

If(4, "four",

If(5, "five",

If(6, "six",

If(7, "seven",

If(8, "eight",

If(9, "nine",

If(10, "ten",

"zero"))))))))))

For those that care: FileMaker 6.0.4/OS X 10.2.5/Dual 867 G4

Posted

On a related note, I believe that when using the If script step, you can optimize speed by nesting multiple If/End If script steps, as opposed to putting a nested If calc in a single script step (e.g. Set Field). Of course, the difference will be negligible outside of a loop, in which case I'd vote for the method you find easiest to read.

Posted

Hi Tom,

The load tests I was referring to were broadly of the type you've described, but included many more tests and nests - with each test involving referential calculation rather than straight comparison with fixed values. As I recall, they were then run through millions of iterations. Moreover the tests were designed to minimize the impact of indexing and table of dependency processes, to focus primarily on the behaviour of the functions themselves.

I no longer have the results to hand but as I recall, running exactly the same nest of tests with Case( ) shaved minutes off the execution time for some of the tests - which equates to *many* millions of cycles.

In many (perhaps most) real world scenarios the difference may not be perceptible, but in complex solutions with multiple layers of dependent calcuations and complex branching logical theads, appropriate use of the Case( ) and If( ) expressions will be one of the factors impacting overall solution efficiency.

Posted

Hi Ray,

yes I was not so clear in my post and I have commited few errors:

1) I should have said that case is faster than nested if else structure.

2)I gave missleading name to example file> It is not comparision between nested If-else structure and case structure

3)The example I gave is not the proof of what I was trying to say

Any way, what I was saying is that all terms in case structure are evaluated and than the right result is returned.

Now, the explanation for the fact that case structure is faster than nested if statement is in the difference on how data are returned from the function.

Nested ifs have an single return point while case function has multi return points.

Basicaly the case function could be interpreted in this manner:

If (condition1=true)

return res1

end if

If (condition2=true)

return res2

end if

...

..

If (conditionN=true)

return resN

end if

If

return res0

In fact, as Tom stated, this is the optimized way to use Ifs in place of (missing) case statement in scripts.

As you have noticed the table of dependencies has done it

Posted

Hi Dj,

I agree that your revised test file makes a stronger 'case' than the previous one. wink.gifwink.gif

Notwithstanding that, it seems that we are agreed on the substantive point, that Case( ) is more efficient than nested If( ) expressions for multi-forked logic.

Posted

I use "If" when I have multiple positive-result or negative-result tests. For instance:

If(x=1, If(y=1, "A", If(z=1, "B", "C")), "")

I find it easier to follow the nesting this way than with "Case", especially as I use returns and spaces to separate them.

This topic is 7981 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.