Firebird will execute the sub query for each record. This is not
ideal in this case, because it means that if you have 100 potential
records, it will execute the same query 100 times.

In this case, you should be able to remove the subquery altogether.
You have already joined the author table, so you can just add it to
your where clause. Also, it sometimes helps (in FB1.5) to move some
of the conditions to the JOIN lines.

Is this not the same?

SELECT PR.product_nm, PB.publisher_nm, AR.author_nm, PR.id, PR.closing
FROM product PR
JOIN publisher PB ON ( PR.company_id = PB.company_id ) AND (
PR.publisher_id = PB.id )
JOIN author AR ON ( PR.company_id = AR.company_id ) AND (
PR.author_id = AR.id )
WHERE ( PR.company_id = 1 )
AND ( AR.company_id = 1 )
AND ( AR.author_nm LIKE 'OSCAR%' )
ORDER BY PR.product_nm

You have also got some extra checks in there that are not necessary.

For example, you are checking that AR.Company_ID = 1, but you are
already checking that PR.Company_ID = 1 AND that PR.Company_ID =
AR.Company_ID.

So this one may be even quicker.

SELECT PR.product_nm, PB.publisher_nm, AR.author_nm, PR.id, PR.closing
FROM product PR
JOIN publisher PB ON ( PR.company_id = PB.company_id ) AND (
PR.publisher_id = PB.id )
JOIN author AR ON (( PR.company_id = AR.company_id ) AND
(PR.author_id = AR.id ) AND ( AR.author_nm LIKE 'OSCAR%' ))
WHERE ( PR.company_id = 1 )
ORDER BY PR.product_nm


Hope that helps.
Adam


--- In firebird-support@yahoogroups.com, Sudheer Palaparambil
<sudheer.clt@g...> wrote:

> Hi,
>
> The following query is taking 11 secs. If I run this as 2

seperate

> queries it is faster.
>
>
> SELECT PR.product_nm, PB.publisher_nm, AR.author_nm, PR.id,

PR.closing

> FROM product PR JOIN publisher PB ON ( PR.company_id =

PB.company_id )

> AND ( PR.publisher_id = PB.id )
> JOIN author AR ON ( PR.company_id =

AR.company_id )

> AND ( PR.author_id = AR.id )
> WHERE ( PR.company_id = 1 )
> AND ( PR.author_id IN ( SELECT id FROM author AR
> WHERE ( AR.company_id = 1 )
> AND ( AR.author_nm

LIKE 'OSCAR%' ) ) )

> ORDER BY PR.product_nm
>
>
> Key details
> -----------
> product (114,000 rows)
> PK - company_id, id
> FK - company_id, author_id >> to author
> FK - company_id, publisher_id >> to publisher
> UNQ - company_id, publisher_id, product_nm
>
>
> author (44,000 rows)
> PK - company_id, id
> UNQ - company_id, author_nm
>
>
> publisher (1,500 rows)
> PK - company_id, id
> UNQ - company_id, publisher_nm
>
>
> Can I improve this query ? Please help.
>
> One more question, Can we specify 2 seperate plan for the above

query,

> one plan for the main query and another for the sub-query ?
>
> Thank you.
>
> Sudheer Palaparambil