AnsweredAssumed Answered

Slow left outer joins

Question asked by FrankvanderMost on Sep 17, 2018
Latest reply on Sep 27, 2018 by FrankvanderMost

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.

Outcomes