Marvelous Optimization #3 – Faster Imports

December 11, 2011

Marvelous #3

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.

 

{ 10 comments… read them below or add one }

Daniel December 12, 2011 at 9:12 am

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

Reply

HOnza December 12, 2011 at 3:42 pm

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.

Bruce Herbach December 13, 2011 at 4:37 am

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

Reply

HOnza December 13, 2011 at 2:15 pm

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))))

boromir December 13, 2011 at 10:45 am

This proves that FMP import code should be optimized a lot by FM Inc !

Reply

HOnza December 13, 2011 at 2:17 pm

You might want to check this out: http://lnkd.in/Yc2unF

Daniel December 13, 2011 at 2:14 pm

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

Reply

HOnza December 13, 2011 at 2:23 pm

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 ;-)

Chris the Home Contractor January 29, 2013 at 1:55 pm

I’ve just started using FM Pro 12 and the long import time is the first problem I noticed. It’s taken 5-days to import 1M records. Not to mention that the computer locked up the first time and corrupted the database so I had to start over.

Reply

HOnza February 15, 2013 at 4:31 pm

Chris, I think that 5 days is too much even for 10M records. There must be something non-obvious slowing down your imports. You need to spot the bottleneck and remove it to speed up your imports.
How long does it take to import 1000 records?
How long does it take to import the same 1000 records into a copy of your database with half of its fields deleted?
How long does it take to import the same 1000 records into another copy of your database with the other half of its fields deleted?

Leave a Comment