Alexandre,
thanks for your clues !
I certain circumstances the first one could change the result set, as
it changes an inner join to an outer join.
On the other hand, the second one is really good an prevents the
optimizer to use the index on the long table, as you said.
Aldo
El 01/08/14 a las 18:29, Alexandre Benson Smith
[email protected] [firebird-support] escibió:
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 [email protected]
[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' [email protected]
[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
