I was able to install FMS19.4.2 on my M1 and do some tests.
I was listening to the DigFM session last week. Claris Software Development Engineer, Keith Proctor was the speaker. He told us that running scripts on the server could obtain performance benefits if they are managed properly. I was under the impression that he was using server side script schedules rather than user initiated PSOS but I didn't get to clear that up as I had to leave early. @bobino may be able to clarify. He suggested having a few (testing required to get the best performance but single digits were discussed) schedules which ran every minute or so. Each schedule would take a chunk of the work. Again, testing required, to determine the best chunk size. The idea is that FileMaker can work with smaller record sets, even across relationships, easily. So you are trying to configure your scripts to be performing the just enough work - not too much - so that they don't lag or hold up the server. Having several running allows them to overlap, keeping the server busy but not straining its resources.
@Malcolm thanks, that's interesting.
Specifically, my Benchmarck script does this:
Each PSOS script:
I wonder if perhaps ReplaceFieldContents is not multithreaded friendly? Watching the PSOS scripts operate, it does appear that they run in parallel (e.g. script 1, 2, 3, and 4 all start and complete about the same time) but that the actual updates are happening in a single thread (watching fmserverd and fmaesd I never see either get over 100% CPU usage).
Here's a benchmark idea you could easily try without changing anything.
Using a third-party SQL tool like RazorSQL or similar, you could see how long it would take to do an SQL UPDATE statement. That's like a ReplaceFieldContents.
So, it would be something like:
UPDATE <TABLE_NAME> SET to (possibly multiple fields) WHERE (limit which records you update), etc.
I've always found SQL with FileMaker to be about 3X faster than native FileMaker itself.
However, this idea assumes you know how fast the ReplaceFieldContents is now so you can estimate. If you don't know that, then you might need a larger test case.
I'd be happy to help you formulate the testing update statement.
Another option on PSoS alternative - set up a job queue table - when you want to run a script, dump the script name into a record in that table. Run a scheduled script that does a perform script by name, picking up the top record in that table for the name of the script to run. Run this script every N mins server side scheduled. A single scheduled script will not start again, until the existing execution completes, so no parallel operations. Delete the record when complete, and the schedule will start again, processing the next script in the stack. If you have headroom, set up a second scheduled script doing the same operation.
This would be the FM equivalent of a legacy mainframe job scheduler. Push script names onto the queue and process server side. Fast and effective.
- 3000 : 1.0x : Mac Mini 2012 (Intel i7) macOS 10.13, FileMaker Pro 18
- 6000 : 2.0x : Macbook Pro 2019 (Intel i9) macOS 12.2, FileMaker Pro 19
- 9000 : 3.0x : Mac Mini 2020 (M1) macOS 12.2, FileMaker Pro 19
Fascinating results… but too many variables to tell where the speed improvements are coming from. As you said… maybe you don't care, but I'd be curious to know: Can you run the same test on those machines with the other version of FMP? I'd be real curious to see how much of a factor FMP 18 vs 19 is. And of course, which version of 19?
I would also be very curious to see that same test on a MacBook Pro with the M1 Pro or M1 Max chip… then the new Mac Studio (releasing tomorrow) with M1 Max or M1 Ultra.
I would expect little improvement. What I'm seeing on the M1 is that my tests appear to be running single-threaded. All of the M1 variants (M1, Pro, Max, Ultra) have about the same single-threaded performance (around 1750 on Geekbench5).
I was using the latest version of FM 19 in all tests. I can't do 18 vs 19 tests since FM19 won't run on 10.13 which is what I have on the 2012 mini.
Good point about single-threaded performance. But I'd still be curious to confirm if that is the case.
Dang, that shallow macOS support window (shakes fist in the air)!
But you should be able to run FM18 on the 2019 MBP running 12.2 or the 2020 Mac mini, right?
Just a thought:
@EfficientBizz @OliverBarrett : the suggestion to use SQL is interesting, but not something I want to pursue, as it would require me to do an entire rewrite of the complicated calcuations moving them from FileMaker's function editor (which I'm very comfortable with) to SQL calculations (which I find very painful to work in).
I think the problem here is not so much the speed of calculations, but rather that FileMaker appears to be locking the entire table when updating a record, which means any script (or set of scripts) which update records in a table will be limited to single-core performance. I suspect SQL UPDATE would have the same issue.
More thoughts:
SQL is easy to work with if you get a third-party app like Data Grip or similar. Here you just use regular SQL statements (no "ExecuteSQL()" syntax). These tools connect to your live FMP database (FMP/S). Plus, they work with ANY JDBC database (all of them). I couldn't get work done without these. I use DataGrip to connect to: Oracle, MySQL, FileMaker, and even old Visual FoxPro DBF files. Soon also SQL Server pending a new project coming up.
I meant the suggestion as only something to "try" since I've found that SQL (via JDBC) tends to be about 3X faster (except for GROUP BY) than FileMaker itself.
Yes indexing is work intensive and happens on commit.
From Skeleton Key
….there is a significant performance impact when a user does a local query (not Perform Script on Server) and that same user has as an open record in the target table. Due to this behavior and the need to have all records committed when a SQL call is made, the use of ExecuteSQL should be limited to scripts and avoided in field definitions or other locations such as conditional formatting or object hiding.
From Soliant
https://www.soliantconsulting.com/blog/executesql-filemaker-performance/
there are two broad areas where it will slow down:
• Complex queries that include JOINS, GROUP BY or SQL functions
• Running queries (even very simple ones) against a table where the user has an open record in that table
The fix to the second can be as simple as making sure that all records are committed before running the ExecuteSQL() query.
However, you may also need to carefully consider using the function in areas where you do not always full control over the state of the record — places such as:
Interesting thread, @xochi !
What I can confirm:
Hosting FMS 18 & 19, Mac Mini M1 clearly outperforms a Mac Mini Intel (2017) and Mac Mini Server (2012, with SSD).
I did not benchmark thoroughly, but a 2 to 3 times increase in speed is observable. In a multi-user environment the server is more responsive, waiting time reduced to nearly no time.
In general FMS 19 delivers better performance than FMS 18, but only with FM 19 clients.I would recommend keeping server and client version number in lockstep for best performance.
On client side, the most significant gains in performance come from fine-tuning scripts, using plugin routines when FM's own routines are too slow. This is true for Intel and M1 client machines.