Hi !
There is some tricks to change the way optimizer will choose the order
of tables:
example:
select
*
from
LongTable L left join
ShortTable S on (S.FieldA = L.FieldA)
The outer join would force the table on the left to be scaned first.
Another option is
select
*
from
LongTable L join
ShortTable S on (S.FieldA = L.FieldA+0)
the +0 (or || '' if the field is a char/varchar) would prevent the
optimizer to use the index on LongTable.FieldA and could lead to change
the join order
see you !
Em 1/8/2014 18:09, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] escreveu:
Sean and Carlos,
thanks for your answer.
Although the statistics are up to date the optimizer still chooses
the shorter stream as the controller.
Nevertheless, I found a workaround using a subquery on the shorter
table instead of a join.
This forced the optimizer to traverse the longer table first and
boosts the performance even more than using an optimized query with a
fixed plan.
Thank you very much for your help.
Aldo
El 01/08/14 a las 16:01, 'Carlos H. Cantu' lis...@warmboot.com.br
[firebird-support] escibió:
Are the indexes statistics up to date?
Also, post the query, query plan, and indexes stats.
Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br
ACacacfs> Hello,
ACacacfs> In a select statement there are two tables joined by a single
ACacacfs> matching field, with search conditions in both of them. One
of them has
ACacacfs> roughly 130000 records, while the other has 600.
ACacacfs> Inspecting the plan generated by the optimizer, I realized
that it
ACacacfs> uses the shorter one as the controlling stream instead of
the longer one
ACacacfs> (i.e. the shorter table is at the left side).
ACacacfs> Using a PLAN expression with the corresponding indexes to
bypass
ACacacfs> the plan deviced by the optimizer in order to switch the
order of the
ACacacfs> streams, the performance is boosted (nearly three times
faster).
ACacacfs> What can cause the optimizer to pick a shorter table as the
ACacacfs> controlling stream ?
ACacacfs> Is there a workaround to induce the optimizer to select the
streams
ACacacfs> in a fixed way or should I have to resort to a manually
imposed PLAN ?
ACacacfs> Thanks,
ACacacfs> Aldo
