FMP 16 ( I did not test other versions)
Left outer joins with multiple criteria (in ExecuteSQL function) turn out to be extremely slow to the point of not being practical. Optimization would be most welcome.
See the discussion here
FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
It turns out that the only viable workaround is to concatenate keys into one key. However, this cuts off at the ankles all the new possibilities that ExecuteSQL introduces to dynamically (i.e. at run time rather than design time) combine join criteria on the fly. It brings us back to having to introduce endless series of key fields (which all have to be stored and indexed) for different combinations and values, depending on needs.
As pointed out by Vincent_L after I posted this issue report, he had reported the issue earlier ( FQL LEFT OUTER JOIN gets 471 times slower if there's more than 1 criteria in the JOIN )
Then and there, TSGal suggested to move the additional criteria to the WHERE clause. However, as I recently added to the original discussion, such a change does not preserve the LEFT side under all circumstances.
FrankvanderMost:
Thank you for your post.
This is currently a design limitation. I have attached your post to the original report. When more information becomes available, I will post again.
TSGal
FileMaker, Inc.