Display = fast (when using an unsorted relationship // our default practice)
Finding and Sorting = works (equally slow as [1])
Please note that I do not know who came up with this, otherwise I'd give attribution
[Summary] I like... [3] Last method ...as the display is fast AND we can Find and Sort on it when we need to. // Best of both worlds.
Here are the results that we got from testing the [3] different methods:
It seems weird that the most natural method (using a Count() function in the parent table) is 50x slower. Conceptually, method [1] and [2] seem like they should be doing the same thing:
determine a set of records (the Found Set or/or Related Records)
count them
So I would expect both to be about the same speed. Method [3] is different, since (in theory) it only needs to find a single record (the Last() one) and that could be much faster?
Have you thought about reporting this to Claris as bug or feature enhancement?
Yes, while Count is the approach that looks right, testing shows there are better ways...
Side note: We occasionally use {{FoundCount}} in a filtered portal (with a Refreshing script Step).
Also, Last is perhaps the last FileMaker Function that you might think of for this use case, while it is our first choice for the reasons stated above!
I am relatively content with the tools available for counting related records. I do have some other feature enhancements in mind a topic for another post!
Are these tests local in FMPro or running on a file hosted with FileMakerServer? We've seem some pretty dramatic (10x to 100x) speed differences with Pro vs. Server, and also across version #s. Supposedly there are some speed enhancements in FMS 21 (2024) as well...
If I recall correctly, the tests that I did were with a File hosted on FileMaker Server. I would expect the relative speeds would be similar (hosted vs local).
Someone might want to test using the FileMaker versions that came out since the test that I did in 2020-09-04.
the explanation regarding count and found count is really simple. The count downloads the records but the found count is almost instant since filemaker use it internally like lets say meta data