|Subject||Re: [firebird-support] Major query performance problems|
>> First thing I would try, _especially_ since you use inner joins, wouldThat may be so in theory, but in practice it _is_ significant.
>> be changing the order of the tables: Instead of
>> SELECT a.id, b.id FROM a JOIN b WHERE ...
>> SELECT a.id, b.id FROM b JOIN a WHERE ...
> The order of tables in an inner join is not significant to the
> Firebird optimizer.
>> Second thing, it's been my experience that inner joins tend to be muchAgain, it may not be normal in theory, but it is common in practice.
>> slower than outer joins with Firebird (probably a problem with my
>> optimizations, but the end result is that I am getting much better
>> performance from SELECT a.id, b.id FROM a LEFT JOIN b WHERE ... than
>> from SELECT a.id, b.id FROM a JOIN b WHERE ...
> You really ought to look at the plans and the index statistics.
> What your seeing is not normal.
Sure, plans are the problem, but every time I checked them, they seemed
> My recollection is that an outer join with conditions on bothAgain, maybe. But it still tends to improve the performance a lot.
> tables is turned into an inner join.