Unresponsive replace operation - extremely slow performance

Hi all,

I heared some advice recently that a scripted loop is faster than a replace operation.

From my experience up to FMP11, replace had always been significantly faster. But today I tried FMP20 and was shocked by the result: a simple string concatenation, on which a relation is based, took several minuts for about 15 000 records. A first run started rather quickly, but became slower and slower. For the last 100 records it managed about 8 records per second.

The same calculation within a Libreoffice spreadsheet was finished within fractions of a second.

The second attempt was even worse: No progress bar appeared, but the replace dialog was sticking for minutes on top of any other window until the replace operation was finished.

What happened to FMP, making that operation that slow? I could have imagined a performance killer, if the current FMP operation would have permitted a revert / undo of the replace operation. But this command is as dangerous and unprotected as ever.

So what's the performance killer here?

The killer is latency.

To understand what's happening you need to consider what is required each time you update a single record in the found set.

First, that record must be downloaded, requiring a request/response.
Next, to update that field, any used relationships must be evaluated. Once evaluated, found record(s) are also downloaded to client. the udpate happens, and then the record is written back to server.

So in a very basic sense (and this abridged):

1x request for record download
1x response for sending record
1x query for relationship needed
1x response and record received
update record
1x request to commit record
1x response acknowledging update

Next it all depends on your latency, that being the time required to send a request. Let's say you're latency is 20ms

Taking that for the above, it's about 6 requests, so 20ms = 120ms (per record). That's just if you're using 1 relationship in your update, the more you add, the more queries required per record.

Multiply that by 15,000 records and you quickly end up with 1,800 seconds.. so yeah slow

I'm assuming here obviously that your solution is hosted on a server.

Factory in also the string concatenation has a small overhead too. There is also index writing to consider. In that situation I would imagine a loop to be slower. The reason being each time you iterate a loop, the index (if there is one) is updated after each record committal. On a replace however index writing is batched into groups of 25 records at a time, so there are less index writes

Overall, a replace on 15,000 records will be slow on any hosted solution, it's just the nature of request and latency over the network and the amount of requests/responses . This is the reason anything in FileMaker is slow on a hosted solution, it's really all about the # of requests multiplied by latency per request.

One solution is performing these on server using PSOS instead, you eliminate any latency overheads, especially for an operation of this size I would be recommending that.

Other factors could be when backups are occurring, a backup will pause operations like a replace while the databases are paused to carry out the backup. Even other users on server doing things will affect performance of a replace.

Finally, any other actions occurring as a result of the replace will have an impact. Consider other fields in the table with auto-enter calculations which themselves update as a result of the field being updated. These must also evaluate - if any of these auto-enters themselves reference relationships, t hen all of those must also be queried, with records downloaded - very quickly things begin to spiral.

I do work offline on a single user database

not necessarily if the operation actually would run on the server.

I would not have expected a really severe step back, from a slower MacBook with older MacOS and FMP11 to a newer, faster MacBook, with newer MacOS and FMP20

I'll have to time it, but it's more than factor 10!

What version of FMP are you using (version #, mac, windows or linux?)
You are running FMP locally? (not using FMS to host the database?)

Edit: I see you specify macOS.

Note that in FMP20.3.1 there are some new features:

New options for the Loop Script Step:

  • The Loop script step now includes the Flush option which determines when relationships are updated. Flush includes the following three values:
    • Always: When setting a field within a loop, the relationship is flushed along with the join data.
    • Minimum: When setting a field within a loop, minimal data is flushed.
    • Defer: When setting a field within a loop, data and relationship data are flushed only after exiting the loop.


  • To improve performance, the database engine now caches relationships to evaluate dependencies.

Are you using 20.3.1 ? If not, worth a try.

Also, perhaps you could try the Scripted Loop version using the Defer option...?

I use FMP on MacOS 12.7.2

I ran into a performance regression when I upgraded from FMS18 to FMS19, and I was kind of hoping FM 20 was faster.

However, in my case the slowdown only happened while using FMS - running FMP only was just as fast as before. My details here: FMS19.6.3 poor performance on M1 mini / Ventura (Edit: fixed link)

I have not yet run these benchmarks under FMS20.

1 Like

I enjoy reading these discussions, as I typically learn something by doing so (even if it's not something on topic :wink: ).

It's interesting to me in that I often see suggestions to use "Replace field contents" rather than suggesting looping through records. I just can't get there and almost always prefer to loop. Looping through records provides for so many control options (error handling, multiple updates, IF statements, etc.). I think I literally have only one use-case among hundreds of FM solutions where I use "Replace field contents" in a script (by calculation) and that's to force pagination for a specific report type. Otherwise, I avoid it like the plague. :grin:

One performance suggestion I often make (that seems non-intuitive to some): running "utility" operations on a blank (zero fields) layout based on the necessary table (typically off-screen for me). This should be a form view layout, of default styling. I've seen people run tasks on cluttered layouts, even with summary fields and such, and complain of performance issues within FM. Yes, Fox Pro was faster than FM in some tasks, as is MySQL, but sometimes it's up to us, the dev, to feed it the right "soup".