To summarize the issue:
- when using FileMaker Server 19 or 20...
- a field that has an AutoEnter calculation that references a field in a table 2 relationships away...
- and the 2nd relationship has a match field that is a Calculation field set to Global storage
The performance is nearly 100 times as slow. Diagnostics on the FMS machine show that the FMSERVERD and FMSAED processes are using very little CPU, suggesting some sort of pathological situation with poor thread usage.
Solutions / Workarounds:
1. Stay with FileMaker Server 18 or earlier.
2. Change the match field type.
- Global Calculation: Bad
- Unstored Calculation: OK
- Stored Calculation (no index): OK
- Stored Calcuation (with index): OK
3. Redesign the relationship.
Having a Global Calculation key field does not cause a problem when the relationship is direct (A-->C) but only causes trouble in an indirect relationship (A->B->C).
4. Use an intermediate calc field
If you add a Calculation field in table B that is equal to the field value from table C, when Table A references this field in table B, there is no speed penalty.