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
Table sale contains
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