Hi Zlatko,

Replacing the first INNER JOIN with LEFT JOIN drastically improved the query
performance to 0.031s! Thank you. Here is the new query plan:

PLAN JOIN (JOIN (A INDEX (IDX_wOHPDCBu0ruVfoUjPp2ujg==), COPY_CLASSIFICATION
INDEX (IDX_UFoCmlswm8xwnGRmBa7/yg==)), COPY INDEX
(PK_ZM6SRonqR8AHSQuCISgvnQ==))
70 fetches, 0 marks, 9 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 29 index, 0 seq.
Delta memory: -11180 bytes.
Total execution time: 0.031s

However, this also changes the semantics of the query and may cause the
query to return more rows.

Could you or anybody else explain why replacing "inner" with "left" made
such a drastic difference? Also, how can I improve the query performance
with the "inner join"?

Thanks,

Alec

On Mon, Jan 18, 2010 at 12:56 PM, zlatko.ivankovic <
zlatko.ivankovic@...> wrote:

>
>
>
>
> Hi Alec,
>
> > select *
> > from
> > (
> > -- This sub-query returns empty result in 0.047 s
> > SELECT * FROM PHYSICAL_COPY WHERE (PHYSICAL_COPY."REPOSITORY_TYPE" =
> 'blah')
> > ) a
> > INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
> COPY_CLASSIFICATION."COPY_ID"
> > INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID";
> >
>
> try to replace first "inner join" with "left join "
>
> Regards,
> Zlatko
>
>
> --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>,
> Alec Swan <alecswan@...> wrote:
> >
> > We have 3 tables: COPY, PHYSICAL_COPY and COPY_CLASSIFICATION. The
> > last two all linked to COPY using foreign keys: PHYSICAL_COPY.COPY_ID
> > -> COPY.ID and COPY_CLASSIFICATION.COPY_ID to COPY.ID
> >
> > We have the following query which returns an empty result set because
> > the sub-query with alias "a" returns an empty result set
> >
> > select *
> > from
> > (
> > -- This sub-query returns empty result in 0.047 s
> > SELECT * FROM PHYSICAL_COPY WHERE (PHYSICAL_COPY."REPOSITORY_TYPE" =
> 'blah')
> > ) a
> > INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
> COPY_CLASSIFICATION."COPY_ID"
> > INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID";
> >
> > This query with 2 joins takes 0.8 s. However, commenting out either
> > join brings this down to 0.047s.
> >
> > How can we optimize this query so that it runs fast with both joins in
> place?
> >
> > Thanks,
> >
> > Alec
> >
>
>
>

[Non-text portions of this message have been removed]