On 12/8/2010 1:13 AM, PenWin wrote:

>
> First thing I would try, _especially_ since you use inner joins, would
> be changing the order of the tables: Instead of
> SELECT a.id, b.id FROM a JOIN b WHERE ...
> try
> 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 much
> 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.

>
> It seems to me your query could be changed to an outer-join query
> without changing results:
>
> SELECT FIRST 1000 PHYSICAL_COPY."ID" AS COL0
> FROM COPY
> JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
> WHERE (PHYSICAL_COPY."IS_MARKED_DELETED" = 1) AND (COPY."SOURCE_ID" =
> 'fb50a346-04e7-42d3-8782-1432001c74ad')

My recollection is that an outer join with conditions on both
tables is turned into an inner join.

Good luck,

Ann