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> )
)

Damian Kelly

Damian has been a database developer with Linear Blue since 2007. During this time he has developed complex FileMaker solutions in a broad range of industries from Finance and Investment to Manufacturing to New Media Promotion. Damian graduated in Production Engineering from the University of Hertfordshire in 1996, attained his Masters of Engineering from Kingston University in 1997 and MBA from Heriot Watt in 2006. He holds certification in FileMaker versions 7 through 12 and in Oracle MySQL 5.

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