AnsweredAssumed Answered

Performance issue on first ExecuteSQL lookup

Question asked by c.wagner1 on Jul 17, 2014
Latest reply on Jul 17, 2014 by philmodjunk


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