Find in Progress . . .
Not all Find Functions are created equal. Performance is secondary only to Accuracy.
Unfortunately, there are times FileMaker Pro has presented the 'Find in Progress' dialogue, and you have to wait for an 'unknown' time, to see the results of your Find Request . . .
The reason for this dialogue being presented can be the combination of network, the solution configuration, and/or the data storage method applied to the field data.
Here's a quick list of possible suspects
Client or Server Network Connection
Virtual Private Network (VPN)
Speed of VPN Solution.
Here's my personal comparison without and with two different VPNs enabled:
Sans VPN: 80mb down / 40mb up
Server A VPN: 0.5mb down / 0.5mb up
Server B VPN: 75mb down / 35mb up
Yes, you are reading that right, there's obviously an issue with Server A's VPN.
These test results are simply to illustrate there are possible environmental considerations when troubleshooting 'slow' FileMaker performance, including 'Find' Functions.
For a true illustration of the traffic between the User and Server, open up a Terminal Window in either your Mac OS or Windows OS, and 'Ping' the Server.
Server A VPN.
64 bytes from 192.168.0.5: icmp_seq=0 ttl=127 time=155 ms
Server B VPN.
64 bytes from 10.200.1.33: icmp_seq=0 ttl=126 time=37 ms
Windows RDP Server.
ping -l 64000 10.200.1.33
Reply from 10.200.1.33: bytes=64000 time=3ms TTL=128
This test purposely does not take into consideration FileMaker itself, as the 'ping' test is one of your quickest and easiest ways of removing the network communication from the client to the server, from any 'slow' speed concerns.
And as the RDP ping test access shows, the 'fastest' way to allow remote access is for the RDP Server to be on the internal network with the FileMaker Server.
WAN Performance Data Monitoring.
In August, Claris updated their WAN Performance Data Monitoring Tool for FileMaker 19.
Once you are confident the connection to your Server is optimal, it's time to get some 'real' performance numbers for absolute before and after results.
FileMaker Client data is 'Cached' on the local client, and depending on your version, cleared on either close, quit, or not at all. For true testing of your Find Function performance, ensure you clear the Client Cache between Finds, with the WAN Performance Data Monitoring Tool.
Stored and Un-stored Data.
The simplest method of confirming if the field data is Stored or Un-stored, is to display the field on the layout, switch to layout mode, and also ensure that you have turned on 'Quick Find' in the View > Show menu option.
You will notice that there is now a magnifying glass in the right side of each fields. 'Green' represents that the data is stored, 'Orange' represents that the data is un-stored.
Three Field Method.
In this method, the solution is presenting only the 'Stored' version of the Field to the User, while having the Un-stored Calculation Field referenced for the Auto-Enter on the Stored Field. For isolating and re-setting any fields that becomes out of sync, we also have a 'Mismatch' field.
Case ( not IsEmpty ( Employee Code ) ; Skills Non-Compliant.calc )
List ( Employees_Employees Skill::Skills.Missing.Expired )
Case ( Skills Non-Compliant ≠ Skills Non-Compliant.calc ; 1 )
Stored fields are displayed as Purple, and Un-stored fields are displayed as Blue.
The 'Employee Code' field has been chosen as the 'trigger' field to reset the Stored data from the Calculated field. There is nothing to say that the calculation can not be referenced directly within the Auto Enter, although this method allows for the Mismatch field.
With this method, the nightly Server Side Script performs a find and returns the found set where Missing Skills.Mismatch = 1, then 'resets' the stored data for the 'Missing Skill' field by replacing the 'Employee Code' field with itself.
We also have the option of running the update script whenever a user updates one of the flagged fields, only targeting the current records.
This method also allows multiple stored fields to be updated at the same time, by replacing the 'Employee Code' Field with itself. In this live example, there are approximately 10 Stored Fields being reset via this main 'trigger' field.
Here's how the Stored and Un-stored versions of the fields performed with the a text based Find request, also with the Stored Cached result, for comparison.
The Found Set is 574 records from a Total of 4637 records in the Table of Employees.
00 mins 01 secs = Cached Stored Find
00 mins 25 secs = Un-cached Stored Find
10 mins 20 secs = Un-cached Un-stored Find
As you can see, that's a huge difference between Cached, Un-cached and Un-stored.
Along with time taken to perform the Find, there is also the data transferred from the Server.
600 B Cached Stored Find
107 KB Un-cached Stored Find
62mb Un-stored Un-stored Find (62000 KB)
The above is an 'extreme' example of a very complicated calculated field from a Related Table that's required for User Finds (Reports) and presentation on various Layouts.
Server CPU Performance.
Along with the time taken to return the Find to the User, and the data required to be transferred to the User, there is also the CPU overhead of Un-stored Finds.
I've witnessed the client's FileMaker Server reach 80% CPU Usage due to multiple Users queueing up their Un-stored Search requests. Once the User Finds were switched over to the Stored Fields, the CPU Usage stays below 10%, at all times.
FileMaker Server 19.1.2
There are promising reports coming through regarding the improvements for Find Performance with FileMaker Server 19.1.2 and Stored field Finds.
Parallel indexed find operations.
Because a sharing lock is used to allow concurrent read access, version 19.1.2 can now perform find operations on indexed fields in parallel. Previously, only one indexed find operation could be performed at a time.
You can read the full article on FileMaker Server 19.1.2, here:
Stored Data is the end goal for structuring your Finds for optimal performance . . .