Hello,

I am sorry I was not clear on my objectives. The first goal is to understand
why commenting out either INNER join causes the query to run 20 times faster
even though the first query in the join always returns a fixed number of
rows (in this case 0 rows). The second goal is to optimize the query to run
faster.

I am using Firebird 2.1.2. Here are some query plans:

-- Your query
SELECT * FROM PHYSICAL_COPY a
INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
COPY_CLASSIFICATION."COPY_ID"
INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID"
WHERE (a."REPOSITORY_TYPE" = 'blah')

PLAN JOIN (COPY_CLASSIFICATION NATURAL, A INDEX
(IDX_/F13/OidwbfyhwFbjRjfng==), COPY INDEX (PK_ZM6SRonqR8AHSQuCISgvnQ==))
334757 fetches, 0 marks, 4209 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 41609 index, 41586 seq.
Delta memory: -1000 bytes.
Total execution time: 0.829s


-- Your query without join with COPY
SELECT * FROM PHYSICAL_COPY a
INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
COPY_CLASSIFICATION."COPY_ID"
--INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID"
WHERE (a."REPOSITORY_TYPE" = 'blah')

PLAN JOIN (A INDEX (IDX_wOHPDCBu0ruVfoUjPp2ujg==), COPY_CLASSIFICATION INDEX
(IDX_UFoCmlswm8xwnGRmBa7/yg==))
43 fetches, 0 marks, 8 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 17 index, 0 seq.
Delta memory: -19268 bytes.
Total execution time: 0.032s


-- Your query without join with COPY_CLASSIFICATION
SELECT * FROM PHYSICAL_COPY a
--INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
COPY_CLASSIFICATION."COPY_ID"
INNER JOIN COPY ON a.COPY_ID = COPY."ID"
WHERE (a."REPOSITORY_TYPE" = 'blah')

PLAN JOIN (A INDEX (IDX_wOHPDCBu0ruVfoUjPp2ujg==), COPY INDEX
(PK_ZM6SRonqR8AHSQuCISgvnQ==))
55 fetches, 0 marks, 1 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 23 index, 0 seq.
Delta memory: 716 bytes.
Total execution time: 0.031s

Thanks!

Alec

On Mon, Jan 18, 2010 at 11:29 AM, emb_blaster <EMB_Blaster@...>wrote:

>
>
> Hi!
> I maybe I din't understand ALL that you want. So few questions, what's the
> PLAN of your actual query?
> What the diference yours from this one below?
>
> SELECT * FROM PHYSICAL_COPY a
>
> INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
> COPY_CLASSIFICATION."COPY_ID"
> INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID";
> WHERE (a."REPOSITORY_TYPE" = 'blah')
>
> Can you provide what time is spend in each time for the querys?(prepare,
> fetch, execute...)
> also, what version of FB?
>
> regards,
>
>
>


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