News, tips, advice, ideas, experience…

Marvelous Optimization #3 - Faster Imports

This example demonstrates that even a single-step script can be optimized. You just have to think a little bit out of the box...

I was showing this as a surprise in my session Marvelous Optimizations at Pause On Error [x] London 2011. I used a sample file with 25 fields and 5,000 records and imported these records 5 times in a row in just 13 seconds. OK, 13 seconds is not bad but what if you need to import data into FileMaker Go  over network? If you want to know how I imported the same data in less than 3 seconds, watch the following video from my Pause On Error session.

Simply said: If you want to make imports significantly faster, you can do it by decreasing the number of fields you are importing. This does not mean you have to import less data. If you import the same amount of data but instead of importing 25 fields you import 1 repeating field with 25 repetitions, your imports can get 5 times faster or even more.

I have not revealed this on my own. Let me credit Heather McCue from Harmonic Data who told me about this idea. It's really very useful discovery, especially for mobile solutions based on FileMaker Go. Thank you, Heather!

Download zipped archive (23.3 MB)

This example includes precise benchmarking code to compare the different implementations of the import script. You need to download 24U FM Bench Detective, install the included 24U Toolbox Plug-In (no registration necessary) and put the FMBench_Log.fp7 file into the same folder as the example for the precise benchmarking to work.

It consists of 2 files, FasterImport_Source.fp7 and FasterImport_Target.fp7. The FasterImport_Source file just contains 5,000 randomly generated records. The FasterImport_Target file is the actual example you should open and try out. You'll be amazed by the results!

Note that the three scrips in the example are almost identical. The only difference is in the Import Records script step's setup. The "normal" version simply imports data into the 25 fields. The "slower" version imports just the one repeating field and uses auto-enter calculations to copy the data from the repeating field to the regular field. The "faster" version only imports data to the repeating field.

I hope this example will inspire you to think more outside of the box and come up with your own optimization ideas. Feel free to share the ideas in the comments in the Comments section below.  

By completing and sending the form you agree that 24U s.r.o., a company established under the laws of the Czech Republic, with its registered office: Zvole u Prahy, Skochovická 88, CZ-25245, registered in the Commercial Register with the Municipal Court in Prague, section C, inset 74920 will use your personal data contained in the form for the purpose of sending 24U’s news, updates and other commercial communications. Providing 24U with personal data for the said purpose is optional. Details on personal data processing and on your rights connected therewith are contained in 24U’s Privacy Policy.