Speed Looping
By John Mark Osborne (editor@isoproductions.com)
http://www.filemakerpros.com/
BONUS FILE: LOOPS.FP3
PLATFORM: Macintosh & Windows
Marking all the records in the found set is a common, but tedious task. It usually takes a long time to perform a looping script. Here are some tips that will speed up the process significantly.
Freeze Window
There are many variables that can slow down a looping script. One of the most common is screen redraw. Each time FileMaker advances to the next record, a screen redraw occurs. Every time a script switches to a new layout, a screen redraw occurs. Now, this may not seem like it will slow down your database much, but trust me, it does, especially if the screen redraw happens when looping through a large set of records. Even solutions without fancy interfaces take time to redraw.
Try running two scripts in the Bonus file provided with this article. The first is titled, "Set Field Loop [No Freeze]". This script sets fields A through Z to a random number for all the records in the database. The reason so many fields and a random value are used will be apparent later. For now, the point of the script is to show the elapsed time when running without the Freeze Window script step. Running it on a Power Macintosh G3/233 took 28 seconds over 250 records. When you run the same script with the addition of the Freeze Window script step, the elapsed time is 10 seconds.
Freeze Window makes such a big difference because it prevents the screen from redrawing. The speed increase depends on the complexity of the layout. If there are a lot of fields and graphical objects to update, the Freeze Window script step will make a bigger difference. I have seen as much as 10 fold speed enhancement on some databases. The speed increase is even more significant when run on a multi-user solution. My guess would be that FileMaker also has to recheck values over the network when the screen refreshes which adds to the time delays.
Indexing
There are two more scripts in the Bonus file titled "Set Field Loop [Exit Record]" and "Set Field Loop [Go to Field]". The only difference between these two scripts is that one exits all fields before running the loop and the other enters a field before running the loop. Which do you think will be faster?
The version of the script that exits all fields at the beginning, consistently took 10 seconds to place a random value in all 26 fields on 250 records. The same script, which makes sure a field is selected before looping, took 3 to 4 seconds to run. That's a major speed difference, but why?
I got this tip from Danny Mack at New Millennium Software http://www.newmillennium.com. He explained it to me this way. When any field on the current layout is selected, FileMaker doesn't index the fields until you exit the record. If no fields are selected, FileMaker indexes each field as it is changed. Apparently, FileMaker is a lot better at batch indexing than indexing fields one at a time.
Now you understand why there are so many fields in this file. This speed enhancement will only work if there are 2 or more fields being set per record. To make the point very obvious, 26 fields were set at once. You might be thinking, why not use Replace? Isn't it faster than a looping script?
Replace
The Replace feature is faster than a looping script that uses Set Field to place values into a field or fields. At least that's what I thought when I built the last script in the Bonus file titled, "Replace". However, I found the Replace script to be no faster than the looping script that uses the Go to Field trick in order to batch index fields. Both scripts took 4 seconds to run.
This got me to thinking. So, I created two new scripts: "Set Field Loop [Single Field]" and "Replace [Single Field]". These two scripts differ from the originals in that they only work on a single field rather than 26 fields. And, guess what? I was right. Now when I run the scripts, the Replace is twice as fast. I ran the test on 500 records and the Set Field Loop took 2 seconds while the Replace took 1 second.
I wondered if the Replace might have been almost 2 seconds and the Set Field Loop just barely over 2 seconds. But, when you watch the scripts run, you can definitely tell the difference. To make the differences more apparent, you could run the test on 10,000 records. However, this will not change the outcome of this test. Replace is faster than a looping script with the Set Field step under certain circumstances. If you have a single field to mark over multiple records, use Replace. Otherwise, you should select the technique based on your needs.
Let's say you need to run a script that marks all the records in the current found set, but you don't know what layout your user will have selected. You could place the field being replaced on every single layout, but that would be a pain in the neck. Since it doesn't matter which field is selected when performing the looping script, just use the Go to Next Field script step to make sure any field is selected. The Set Field script step will set the designated fields no matter what layout the user has selected.
The moral of the story is to not blindly select a solution because you think it is superior. Choose the right technique for the right job. Sometimes that requires sitting down and listing the advantages and disadvantages. Other times it means testing the speed. I am always amazed at how little I know about the way FileMaker works. Once I think I understand it, I learn something new that makes me think outside the box.
One More Tip
I didn't create an example script in the Bonus file for this tip, but it is a solid tip. If you ever need to place a value from a related field into a field in the master file, consider using a lookup rather than Set Field. Lookups tend to be faster than moving data through a relationship using Set Field. The only explanation I have is that a lookup is an internal feature of FileMaker that has been optimized for speed. Set Field can pull data through a relationship, but it is mainly for setting data from one database to another field in the same database. This technique really shines when working in a multi-user database scenario.
# # END # #