Hmmm, a simple comparison on a 1 million record table seems to favor GTRR, as others have suggested. Note, i was matching names in a name field that contained duplicate names.
There might still be some scenarios where Find is faster though.
Script if you care to scrutinize:
Good work Josh!
Would be interesting to do the same test with data like UIDs generated with Get ( UUID )
Good idea. Here are some quick results for unique UUIDs. GTRR really shines in this scenario. Perhaps because of the way FM uses value indexes instead of word indexes in relationships?
BTW, while the Find routine took 1717ms to find 1000 primary keys, ~80% of that time was spent creating the requests in a loop. When I timed the actual Perform Find part alone, it only took 315ms.
wow, really interesting, those findings, thank you so much for charing.
The performance gained by GTRR could lead to building selfjoins based on global fields, something I often do, I didn't take any sophisticated measurements but the scripting is a lot easier, validation can be done beforehand etc. I didn't think UUID would be handled so much faster.
On the other hand if I had a lot of integer IDs and my foundset could be grouped and this was mission critical I would think about getting the finds done in chunks with the ... - operator like 102...780 which would help reducing the amount of iterations of the find loop, but it depends on data and it would of course produce overhead at another place
Holger
Thanks a lot indeed for doing all this testing!
I wasn't expecting the differences to be so much in favour of GTRR.
Too early for R.I.P. - GTRR I suppose...
Hi @jwilling,
Just wanted to echo a big thanks for doing this work and sharing the results.
I am wondering if I could trouble you for one or two things about this?
Were these tests on a local or hosted file? (this is something I left out in my above post, and just realized I should probably add it in.
Any chance we could ask you to some day re-run things with a Freeze window step before the execution of building the Find query? I found your comment about how building up the Find request is taking a long time very interesting, and of course, had to wonder if anything could be done to speed that up.
Again: many thanks for this contribution. I know time is often scarce for all of us, so I appreciate you giving some of yours, as you have.
Sincerely,
-steve
Local.
I see now in my picture that I cut off the top of the script where it does Freeze window. Here's the whole script:
I might as well describe more test conditions.
FETCH FIRST 1 ROW ONLY but I'm skeptical that it has any impact. I would guess that FM just enthusiastically checks all related records for a given index regardless of validation settings.Hope that clarifies and maybe gives others a jumping off point to do more robust testing (if we're no already satisfied). I provide no guarantees and am always happy to eat my hat.
you can rule out that behaviour by changing the language of the index to unicode
That brings more stable environment for all operations based on relationships with UUIDs involved.
Local file, FMPA 18.03.317, Macbook Pro Mojave.
Many thanks to everyone who has contributed to my original post. I have to jump in again here, as pretty much every problem we're experiencing with GTRR (and others, please see below) is Windows and FileMaker Server related, hence local testing won't be representative of the issues I initially raised.
Fundamentally, since the upgrade to FMS16, most systems that have roots in pre-script trigger days and rely heavily on FileMaker's original feature set, such as unstored calculations and conditional formatting have gone from working acceptably in FMS 14 and FMS15 to a point where, I believe, every client we've upgraded to FMS/FMP 16, FMS/FMPA 17 and FMS/FMPA 18 on, I must stress, Windows, has complained of major slow-downs to a point where some features are unusable.
Some of these systems have been running for over 15 years and have involved thousands of hours of development and thousands of GBPs investment for the clients, hence 'just recreate them' isn't an option. Many of these we have converted to themes and styles, but otherwise they remain the same as the versions running in pre-FM 16.
We had a client on a normal Windows LAN based system, replaced very old hardware with new hardware, we upgraded them from FileMaker 14 to FileMaker 17, added themes/styles; they rang on the first day of use and reported the system was unusable. This is not the only example we have.
Another example is that we have an old system, heavily reliant on custom formatting in a list layout that took about 3/4 a day to create originally and in the last week we have now spent 52 hours trying JSON, button bar calculations and many other techniques to try to make this workable again. We now have 2 options that are cause for optimism, but we can't rewrite every component of every system we've ever written, just to accommodate new versions of FileMaker software running on Windows.
I truly believe this is linked into the move from FileMaker for Windows MDI to SDI. I don't believe Mac users are seeing the same problems, but of course they never had an MDI equivalent.
Hence, if any meaningful results are to be obtained, then if you're 100% Mac, don't bother, otherwise I suggest using a Windows based FileMaker Server 16 upwards with Windows based FileMaker clients.
All the best
Andy
Hi Andy,
Our development server is a Windows machine (Windows Server 2012 R2 Standard), and we're using FileMaker Server 17.0.2.203.
I ran the following script via Perform Script on Server:
The results are as follows:
Food for thoughts?
My local machine is a Mac, but that shouldn't really make any difference to FileMaker Server. The id fields were all indexed.
I can't say what the issue is with Windows but running a Find seems definitely slower, unless I missed something.
I hope this helps, please correct me if I made a mistake 
Thanks,
Chloé
Thanks Chloe
We still haven’t managed to trace the exact reasons and it is difficult to go backwards once having moved forwards.
PSOS is interesting, as it doesn’t have any window interaction, so if it is a conversion to SDI issue, then it won’t show up.
Our findings are inconsistent, we’re using GTRR where it works (as it is so useful) and where it is grinding to a halt, we’ve resorted to finds.
This wasn’t really a GTRR vs Find post, it is really about systems that have evolved over many years performing worse as they are run in newer versions of FileMaker Pro/Server in Windows. This is a very difficult situation to manage with clients, whose expectations are for improvements not the opposite.
Appreciate your input
Andy
I hear you there! We'd have to test the same script on different FM versions to see the change over time. And of course, isolated/sterile test cases don't always predict behavior in a complex systems, which is almost always the real situation. We can only reduce to small testable chunks and hope our extrapolations hold.
That said, I feel there is an GTRR vs Find implication in this post because switching GTRR to a Find is the theoretical solution to slow GTRR, and the results seem to suggest that it might not be as good a savior as we hoped.
Anyway, cheers and glad to hear that you've been able to work around the apparent slowdown (shakes fist indignantly at the Claris gods).
Best,
Josh
Yes, somehow you could get to the conclusion that if GTRR in clinical test scenario beats finds and does not in ones real world setup, maybe something is broken on the design level of the solution?!
That's kind of what I thought when running the tests...
And as Andy said, this might hint at an issue with the move from Windows MDI to SDI. I'm still glad GTRR in PSOS seems to work as quick as expected, because I'm relying heavily on this to restore found sets when performing server-side scripts 
. That's also why I wanted to run the test in the first place!
Chloé
Don't forget the overhead of every PSOS actually starting a new session. This overhead can be, depending on the complexity of your solution, anything from a few mmilliseconds to several seconds.
// Great discussion
Wondering if Get ( UIDnumber ) would improve situation:
Quote from FM 17 / 18 help:
FileMaker Pro 17.0 Advanced
Returns a unique, 24-byte (192-bit) number. For example, you can use this function to generate a unique ID of a record. Using this function instead of Get(UUID) as the calculated value of a primary key field may improve the performance of operations based on relationships.
For unstored calculations, returns a new number each time Get(UUIDNumber) is evaluated.
https://fmhelp.filemaker.com/help/18/fmp/en/#page/FMP_Help%2Fget-uuidnumber.html%23
Gathering a list, dropping it in a global and GTRR from a single record is the fastest method I’ve tested so far.
That is obviously what the comparison graphs showed. My point is that the Get ( UIDnumber ) offers additional performance as the description might imply...
I have yet to see any tangible or even measurable performance improvement from using UUIDNumber, but it's possible the data sets I've worked with just aren't big enough.
One (maybe trivial) reason I'm loathe to use UUIDNumber is because, while the Data API returns the full-length number value rather than scientific notation eg: { "id": 1098562493519703241101242571292121006436631469400182050816 } 
, JSONGetElement truncates the number into scientific notation. That makes it useless as a record id. To extract uuid number from Data API results we have to parse the JSON text manually. 
Interesting comment. I have seen no issues with GTRR in terms of reliability but I have formed the view since 2012, when FMI re-engineered perform find, that going to the correct layout and finding records is much more performant than GTRR for the same action especially when, by so doing, you have reduced the complexity of your relationship graph.
I touched on this in my recent interview on The Fireside FileMaker podcast with John Mark Osborne & Michael Rocharde.
The problem is that up until the re-engineering of find GTRR was faster but that is no longer the case.
Cheers,
Nick
