Optimising the Case Statement

There are a few optimisation strategies for Case. At a simple level consider the Let statement…
Let([
f1 = field1;
f2 = field2
];
Case (
f1 = 0 and f2 = 0 ; <result1> ;
f1 = 0 and f2 = 1 ; <result2> ;
f1 = 1 and f2 = 0 ; <result3> ;
f1 = 1 and f2 = 1 ; <result4> ;
<default result> )
)

This is quicker because FileMaker only calls the fields once, as opposed anything from 1 to 4 times depending on which result is returned below.

Also consider making sure the most likely result is first. The Case will stop at the first clause that is evaluated as true so having the most likely first saves FileMaker running unnecessary calcs.

Let([
f1 = field1;
f2 = field2
];
Case (
f1 = 1 and f2 = 1 ; <result4> ; //Most likely result
f1 = 0 and f2 = 1 ; <result2> ; //Second most likely
f1 = 0 and f2 = 0 ; <result1> ; //Third Most likely
f1 = 1 and f2 = 0 ; <result3> ; //Least likely
<default result> )
)

On the other hand if one clause contains a complex calc that may be slow putting it last is better. That way, even if the complex calc is most likely, it is only evaluated as needed

Let([
f1 = field1;
f2 = field2
];
Case (
f1 = 0 and f2 = 0 ; <some static value> ; //All pretty simple and quick
f1 = 0 and f2 = 1 ; <easy peasy calc> ; //Relatively quick calc
f1 = <tricky calc> and f2 =<another tricky calc> ; <result1> ; //A couple of vaguely slow calcs
f1 = 0 and f2 = 1 ; <nightmare summary from hell> ; //Some evil sub summary across a gazillion records
<default result> )
)

Darren Kayes

Darren is Linear Blue's Chief Operations Officer, ensuring the smooth running of the company and making sure nothing gets in the way of our developers creating top-notch web and database solutions for our clients.

More Posts - Website

1 thought on “Optimising the Case Statement”

  1. Another consideration and you can reverse the f1 and f2 as needed

    case(
    f1 = 0; case ( f2= 0 ; xxx ; yyyy) ;
    case( f2 = 0; xxxx; yyyy )
    )

    This is not as easy to follow as your previous examples but it might save a tic or two since f1 and f2 need only be evaluated once. However, your consideration of the nasty calcs can’t be considered here.

    I enjoyed reading this thread as I haven’t given much thought to the nature of the calcs in the ifs…

Leave a Reply