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.
{ 8 comments… read them below or add one }
However as you point out in your video this is only useful if you can leave the data in the repeating fields, which is something that should be avoided.
Daniel
Try to tell that to Heather. In her case the performance benefit of keeping all data in a single repeating field (she actually uses around 100 repetitions) significantly exceeds the limitations.
You should always consider multiple ways to do one thing before choosing the one that’s best for your situation. If you say that something “should be avoided” you’re closing your eyes and limiting your abilities.
Choose not to use this technique when you cannot afford to accept the disadvantages, not just because it’s commonly understood as something that should be avoided.
For example, when implementing synchronization between FileMaker Go and FileMaker Server, limiting the time needed for the data transfer may be critical for higher reliability even if you then need to parse the data into individual fields.
Hi HOnza,
I’m working on a system that has to do a lot of imports, so found this to be very interesting. I can see that the import is very quick using this method. The part I don’t see if how you get the data into the repeating fields in the source file. For the system I am working on this would be a key step.
Can you shed some light on this part?
Thanks
Bruce Herbach
I think this technique can only be used when the source file is FileMaker database as well. If that’s your case and you just want to know how to combine data from multiple fields into a single repeating field then I would suggest using GetField or Evaluate together with Get(CalculationRepetitionNumber). You can even put all your fields on a special utility layout (let’s say we name it “MyExportFields”) and then use the FieldNames functions:
Extend(GetField(GetValue(FieldNames(Get(FileName); “MyExportFields”); Get(CalculationRepetitionNumber))))
This proves that FMP import code should be optimized a lot by FM Inc !
You might want to check this out: http://lnkd.in/Yc2unF
The main problem is that most developers don’t fully understand the impact of using repeating fields for data storage, which typically come back to bite them later in the project. FileMaker has a great article on Repeating fields http://help.filemaker.com/app/answers/detail/a_id/7462/~/portals-%26-repeating-fields outlining the following disadvantages:
It is much more difficult (sometimes impossible) to create custom reports based on data in repeating fields
Repeating fields trap your data in ways that limit how you use it in the future. Repeating fields are not a good choice when your database is likely to grow complexity or functionality in the future
Layouts can become very cluttered when many repeating fields are displayed
Data has a possibility of being misrepresented if entered into the wrong repeating field
Defining repeating field values to be used in a summary report can be an arduous task
Which pretty much sums up why you should avoid these as data storage long term. However we use repeating fields all the time for certain things like dialogs, value lists, etc. but we avoid them for long term data storage.
I am not saying this is not a good technique for that situation just that leaving the data in the repeating field should be avoided.
Daniel
Agree. Mostly…
I can imagine (although rare) situations where even long term data storage in repeating fields makes sense.
I generally try to avoid generalizing things