Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Case(In) Efficiency


LaRetta

Recommended Posts

Hi Café smile.gif

 

It is my understanding that Case will stop evaluating after the first true condition. Is it beneficial to structure Case() calcs with that in mind? In other words, which would be the most efficient if I knew the majority of records would have empty Text fields?

 

Case(not IsEmpty(Text), “Approved”, “Not Approved”)

or ...

Case(IsEmpty(Text), “Not Approved”, “Approved”)

 

Would it speed things up to use the second calc, assuming it will hit more empty fields (stop evaluating after first test) and jump to the next record, than having to evaluate the second condition (or third or fourth)?

 

This is, of course, a simple example ... Case() statements can become quite complex. But should one consider what category the ‘largest’ group of records would fall into when they structure the calc? Many calcs can be written either way - as above.

 

As Developers, we may not always know how data will be disbursed in the fields, but many times we have a pretty good idea. For instance ... An error. Most records wouldn’t have an error flag so wouldn't it make sense to test for that first (not Error) to eliminate the majority of records right off the bat?

 

Every time I write a Case() statement I wonder about it and I end up eliminating the majority of records first (whenever possible) but I'm unclear on it. confused.giflaugh.gif

 

LaRetta

Link to comment
Share on other sites

Howdy! I believe FileMaker actually evaluates every test in the case statement, but returns the result for the first true condition. So, it doesn't matter what order the tests are placed in the case - they'll all be evaluated.

 

I tested this once with a count() function inside a case statement. Something like:

Case(
    1 = 1,
    "no count",

    count(self.constant::my_constant) > 0,
    "count"
)

With a very large record count, this took a long time to execute, even though the first condition evaluated to true, and the result was "no count". With a very small record count, the calc was noticeably faster. Bummer. :rolleyes:

Link to comment
Share on other sites

Thanks Mariano. I'm disappointed to hear that. In theory, it should stop evaluating. And, in theory, it should be more efficient addressing the largest number of records first. Drat! frown.gif

 

I thought If() would keep evaluating but Case() would stop. Then FM Help is deceptive when it says:

 

The case function evaluates each expression in order, and when a TRUE expression is found, returns the result supplied for that expression.

 

Well then I'm glad I asked. I don't want to waste my time if it doesn't matter either way. It's just not logical to me, that's all. wink.gif Thanks again!

 

LaRetta

Link to comment
Share on other sites

This is not the only inefficiency of FM.

Much worse is the situation in WebCompanion.

WC is wrongly constructing the whole page in memory with IF ELSE ENDIF statement and then serving only the valid part.

 

So in this example FM/WC will construct HTML/CDML page in size 301k + the normal HTML tags and then it will send to browser only the 1k part frown.gifmad.gif

 

IF is true

1k of HTML and CDML database code

ELSE

300k of HTML and CDML database code

ENDIF

 

Yak.

Link to comment
Share on other sites

  • 8 months later...

Well thank you FileMaker again! I had already read that If() no longer requires a result on Cleveland Consulting but this latest news is even better.

 

Case() stops evaluating. And so does Choose(), If(), AND, and OR!!! Short-circuiting!! And now my original question on this thread is valid...

 

... that the order of the items within the calculation will make a difference in speed and efficiency! I guess its called "branch prediction". Thanks to The Shadow for filling in these pieces for me. I won't be as apathetic when listing their order now (using FM7). smile.gif

 

LaRetta

Link to comment
Share on other sites

I tried out Mariano's example above:

Case(
    1 = 1; "no count";
    count(self.constant::my_constant) > 0; "count"
)

verus:

Case(
    1 = 1; "no count";
   true; "count"
)

In FM7 with a looping SetField script against 100,000 records, it is about 50 seconds either way. The first run takes longer (about 60 seconds) probably cuz the records were not loaded in the cache yet.

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use