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

 

 

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