Fred(CH)

Search on unstored calcs

Discussion created by Fred(CH) on Feb 10, 2015
Latest reply on Feb 10, 2015 by Fred(CH)

Hello,

 

Yesterday, i solved an issue that i first believed very difficult and was finally very easy to solve.

 

I inherited a very complex hosted database, in term of structure but also about the domain i have pain to understand : building  accounting.

The database have multiple files (5) and each files have many tables.

But overall, they have numerous unstored calcs : most of the calcs ARE unstored.

 

Yesterday, the enduser complained about the tremendous slowness of a so simple script.

With the debugger and a bit of analysis, i realized that the culprit was an unstored calc involved on search request :

 

Enter Find Mode

Set Field [<regular field 1>]

Set Field [<regular field 2>]

Set Field [<unstored calc 1>]

Perform Find

 

Instinctively, I first tried to understand why the calc has been unstored and also why it was so slow, to have a chance to optimize it.

 

But  after 15 minutes to analyse the formulas and the relationships, i thought : no luck, except if i would rewrite the whole database.

 

5 minutes to dream of a miracle and i had a very basic idea : perform the search in two steps instead of one :

 

Enter Find Mode

Set Field [<regular field 1>]

Set Field [<regular field 2>]

Perform Find

Enter Find Mode

Set Field [<unstored calc 1>]

Constrain Found Set

 

Yes, the script is now executing instantaneously.

 

My conclusion :

I really hope this gentle history could help beginners or intermediate on FileMaker scripting.

But also, it could ask a more advanced question : could the FileMaker Search Engine be easily optimized ?

 

Bye, Fred

Outcomes