Hi,

As a relatively new Filemaker convert I have found that use of calculations without careful planning can lead to performance issues, especially in lists of job or quotes for example, where I'm referencing quite a few fields in the calculation.

Is there a common approach that more experienced developers use such as 'always' scripting calculations or sometimes according to certain scenarios. Any advice would be appreciated as I would like to adopt best practice but the shortcut of advice from more experienced developers would save me a lot of trial and error.

Thank you in advance.

Simon

Hello, Simon.

It's difficult to answer your question precisely without more information (e.g., a sample of a calculation that's giving you trouble). However, I can give you some general principles:

1) FileMaker has two kinds of calculations, stored and unstored. Stored calculations are evaluated and "stored" (hence the name) in the data table where they reside. As a result, they can be indexed by the database engine and will render no slower than any other field. Unstored calculations, on the other hand, have to be evaluated at runtime. There are a variety of reasons for this, including referencing a related field, a global field, another unstored calculation, or a summary field. Because they have to be evaluated at runtime, unstored calculations are typically a performance hit, compared to stored calculations.

2) Whenever you reference a field or function in a calculation, the calculation has to evaluate its value. This can lead to inefficiencies in a couple of ways. First, repeated references to the same field or function forces FileMaker to repeat itself - which is inherently inefficient. Second, if the object you're referencing is itself something that has to be referenced, it can slow the calc engine down because it has to figure out what A is before it can figure out what B is.

Now, let's take a couple of examples to make that clearer. Let's say I have a need to calculate a variable tax rate on the sum of a balance in a related table. This is inherently an unstored calculation, so we're going to skip over item 1 and go straight to item 2. (I've borrowed this example from Ray Cologon's excellent

FileMaker Pro 9 Bible, which I recommend.)Here's an original example of a calculation - from a real-world solution, BTW:

Case(

Sum( Invoices::BalancePayable ) > 0 and Sum( Invoices::BalancePayable ) < 50 ;

Sum( Invoices::BalancePayable ) - Sum( Invoices::BalancePayable ) * 0.05 ;

Sum( Invoices::BalancePayable ) >= 50 and Sum( Invoices::BalancePayable ) < 100 ;

Sum( Invoices::BalancePayable ) - Sum( Invoices::BalancePayable ) * 0.1 ;

Sum( Invoices::BalancePayable ) >= 100 and Sum( Invoices::BalancePayable ) < 150 ;

Sum( Invoices::BalancePayable ) - Sum( Invoices::BalancePayable ) * 0.15 ;

Sum( Invoices::BalancePayable ) >= 150 and Sum( Invoices::BalancePayable ) < 200 ;

Sum( Invoices::BalancePayable ) - Sum( Invoices::BalancePayable ) * 0.2 ;

Sum( Invoices::BalancePayable ) > = 200 ; Sum( Invoices::BalancePayable ) - Sum( Invoices::BalancePayable ) * 0.25

)

Whew. That's really ugly. But what else can I do? I have a bunch of boundaries and each boundary has a different value I have to calculate?

One thing to do is get really, really friendly with the Let function. You can read about it in the FileMaker Help, but basically, Let allows you to specify variables up front. How does that help? One way it helps us is that it allows us to calculate that unstored bit - the Sum ( Invoices::BalancePayable ) - only

once. The calculation engine won't have to work nearly as hard if it only grabs that bit once instead of, what, 19 times?What would that look like?

Let( Bal = Sum( Invoices::BalancePayable ) ;

Case(

Bal > 0 and Bal < 50 ; Bal – Bal * 0.05 ;

Bal >= 50 and Bal < 100 ; Bal – Bal * 0.1 ;

Bal >= 100 and Bal < 150 ; Bal – Bal * 0.15 ;

Bal >= 150 and Bal < 200 ; Bal – Bal * 0.2 ;

Bal >= 200 ; Bal – Bal * 0.25

)

)

Whew. A lot easier on the calculation engine.

Another way people hurt themselves is by making multiple calculation fields in an attempt to "segment" their calculation code. You might see this with a calculation like this:

TodaysDate = Get ( CurrentDate ) [unstored]

And then you have another calculation like this:

Status = Case ( TodaysDate > DueDate ; "Late" ; TodaysDate = DueDate ; "Due Today" ; "OK" )

Again, you've asked the calc engine to recalculate your current date twice - but you've also created a "dependency". That means that field B is "dependent" on field A - and can't evaluate until field A is evaluated. This can be a performance hit on your solution as well. You might be better off with this:

Let ( TodaysDate = Get ( CurrentDate ) ;

Status = Case ( TodaysDate > DueDate ; "Late" ; TodaysDate = DueDate ; "Due Today" ; "OK" )

)

In this case, you've used Let to do two things: Evaluate the current date only once, AND eliminate the dependency tree. Both will improve performance.

3) Recognize that there's a prioritization in certain situations. For example, the Case function evaluates conditions in the order in which they appear. When it hits a true condition, it quits evaluating. Therefore, to make your calculation faster, put the most likely conditions near the front of the calculation. This will reduce the number of iterations the engine has to run through and speed it up.

Those are just a few ideas off the top of my head. I (and others, I'm sure) can provide more detailed advice if you give us a little more information on exactly what you're trying to do.

HTH

Mike