AnsweredAssumed Answered

Huge variations in script performance (duration) - but why??

Question asked by wxtyrs on Aug 22, 2018
Latest reply on Aug 22, 2018 by wxtyrs

I have a complex single-user FMPA 16/17 (not Server) app that transforms raw data.  It takes an inordinate amount of time to process the entire data set across numerous tables.  A major time consumer is looking up and retrieving data in unrelated tables.  There are >70 scripts that do so over several tables.


My concern: huge variations in the time it takes to execute a given script.


For example, I ran >30 repeated timings of the attached script against the exact same tables/data.


The same exact script run under the exact same (controlled) conditions has ranged in run times from 16 to 54 minutes.


The work is done on a 4GHz iMac with 32GB RAM, 1TB SSD with ample space and 250GB allocated to FMPA cache.


The script screenshot is attached to this note.


Invoking the same script manually, one time after another, yielded the following durations for example: 24 minutes, 40, 32, 38, 27, 16, 54, 20, 16, 26 , 41.   Duration is based on the Get ( CurrentTimeUTCMilliseconds ) function.


What might explain this wide variation in the duration taken to execute the one script on the same data?


How does table width impact Loop performance etc with FMP?? If at all.


This particular script retrieves 10 demographic data elements from an unrelated table using executeSQL() and distributes these to the relevant fields in the original table using Set Field via a Loop routine.


The executeSQL() uses a simple SELECT statement - nothing fancy.


In this example ...


The main table has 572,031 records.  The lookup table has 527 records.  Both are wide tables: >100 fields.


The data are not normalized to maximize performance: no calculated field values in this process.


Indexing is fine.  I have rebuilt the index a few times (turned off then on again) just in case.


In my testing, the same sort of unexplained pattern of variation exists regardless of whether the app is restarted, compacted or even OSX rebooted altogether.  Background processes, such as ESET and Time Machine, are off.


Some fields DO HAVE auto-entry configured.


That is, if empty, auto-entry automatically inputs the term "missing" into the field.


Can auto-entry impact performance in the way described above?