Subject Re: [firebird-support] Re: Common Table E xpressions ( “WITH ... AS ... SELECT”)
Author Tim Ward
On 26/01/2015 09:53, masberg@... [firebird-support] wrote:

Hi Dimitry,
thank you for your response.
I think in my case I can replace the outer join with an inner join, because it is the same sp with the same query parameters I am calling twice.

Are there any future plans to change this re-evaluation structure in outter joins?

With a lot of complex queries typically involving several joins, both inner and outer, several CTEs, and sometimes some procedures as well, I find that sometimes changing an inner join to an outer join improves performance by orders of magnitude, and sometimes changing an outer join to an inner join improves performance by orders of magnitude. I have not been able to deduce any rhyme or reason to what's going on - when I find something that works I'm afraid I just have to leave it and move on to the next problem rather than take time to investigate in any more detail.

In some cases the logic of the query is such that inner and outer joins *can only* deliver the same result, so one might hope that an optimiser would spot this and pick the faster execution path (manual tweaks to the syntax that don't affect the meaning of the query should not affect the output of the optimiser), but this doesn't (always?) seem to be what's happening.
Tim Ward