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

Title

Performance issue on first ExecuteSQL lookup

Post

     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

     fk_customerID

     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

     Chris

Outcomes