On 26/01/2015 09:53, masb...@za-management.com [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