Hi Kenneth!

I still don't understand why you are using a RIGHT JOIN here and would
suspect it to be the source of your problems unless proven otherwise ;o)
Another thing could be 'some values' (as I just found out Helen already
mentioned). I would believe this to be OK if 'some values' actually are
fixed values (according to Helen < 1K of fixed values), but if it is a
subselect then no wonder your query is slow. Never use IN (subselect) if
speed is an issue, go for EXISTS(subselect).

Set

PS! If it wasn't for The Firebird Foundation going public today, Helen
would have kicked you over to ib-support with this type of question.

At 10:00 12.12.2002 +0000, you wrote:

>Thank you for all who answered my questions. I have found out that
>the problems is relateded to sorting a columns which are not in the
>table of SELECT FROM clause. i.e. I am try to sort A.NAME which is
>not in table HERBAL that I am selecting from.
>
>I solve the query by arranging the order of table in the select from:
>SELECT H.REFNO
> , A.NAME
> , A.STROKE
> , A.S_CODE
> , H.DOSAGE
> , H.CHARACTERISTIC
> , H.SYSTEM
> , H.FUNC
> , H.USES
> , H.REMARK
>FROM ALL_HERBAL A
>RIGHT JOIN HERBAL_WITH_CAT HC
>ON HC.H_REFNO = A.REFNO
>JOIN HERBAL H
>ON H.REFNO = A.REFNO
>WHERE HC.HC_REFNO in (some values);
>
>Now sorting on A.NAME, A.STROKE and A.S_CODE work correctly.
>However this query proforms less effective than the previous one.
>Any ideas to optimise this query?
>
>Thanks once again!