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' listas@... [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