|Subject||Re: [firebird-support] Major query performance problems|
> We have a very simple query (shown below) that joins two tables,First thing I would try, _especially_ since you use inner joins, would
> filters on a column in each table and projects the id from one of the
> tables. The query takes dozens of minutes to return when run on a 4.5
> GB database. We are looking for recommendations on optimizing the
> query. References to documentation relevant to query optimization will
> be appreciated as well.
be changing the order of the tables: Instead of
SELECT a.id, b.id FROM a JOIN b WHERE ...
SELECT a.id, b.id FROM b JOIN a WHERE ...
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 ...
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
JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (PHYSICAL_COPY."IS_MARKED_DELETED" = 1) AND (COPY."SOURCE_ID" =