1 Reply Latest reply on Jul 17, 2014 9:13 AM by philmodjunk

    Performance issue on first ExecuteSQL lookup



      Performance issue on first ExecuteSQL lookup


           Here is the relevant information:

           Table customers contains

           fullName Indexed text field required

           customerID Indexed number field auto enter serial

           30k records


           Table sale contains


           80k records


           Tables are related...

           I have a script that runs fairly often in my solution.  The script runs from a sale layout.  The bulk of the script is fairly complicated and mostly irrelevant.  The script runs flawlessly every time it is run finishing execution in milliseconds, except for the first time.  The first time the script is run after the solution is loaded up or when the computer has been idle for a few hours the script gets hung up on this line:

      ExecuteSQL ( "Select customerID from customers where upper(fullName) like ? or upper(company) like ?"; "" ; ""; "%" & Upper($$customerName) & "%"; "%" & Upper($$customerName) & "%")

           The script hangs for almost 30 seconds on this step.  My best guess as to what is happening is filemaker is updating the appropriate indexes to make future finds faster.  Can anyone confirm this is the case and does anyone have any ideas how to speed this up?  I toyed with the idea of running the script once on start up but it would kind of hang the solution for 20 - 30 seconds on start up and there is no way to even build a progress indicator since it is a single script step causing the hang up. 

           Thanks for your thoughts


        • 1. Re: Performance issue on first ExecuteSQL lookup

               This is an unstored calculation so FileMaker has to perform the query each time it is referenced.

               I've recently read that since FileMaker isn't based on an SQL engine like most DB systems, it has to run an interpreter in order to run such a query...

               So those factors may also apply here.

               But what you are doing with a query could also be done with a scripted find using the * wildcard so it might be interesting to try that option and see if the performance is any better using that method. (assuming that company and customer name fields are indexed...)

               For examples of scripted finds, see: Scripted Find Examples