Andrew,

At 12:05 PM 5/03/2003 +0000, you wrote:

>Hi
>
>Why does Firebird handle inner joins so inefficiently?

It doesn't, as a rule, provided you have taken good care of the
indexing. You will tend to get NATURAL in lieu of an expected index, where
you have duplicate indexes on the column -- typical would be in a database
imported from another DB with its indexes intact. When you apply a primary
key to the table, a unique index to enforce the PK is created. There will
usually be optimizer problems if you don't kill the old index. Can't tell
if you have this issue, though.

>Here is a
>example.
>
>select prt.Inc_Reqn,
> pr.PartNo, pr.PartParam, pr.Operation,
> pr.FulfilUnitsQty, pr.RequesterNo, jm.Division,
> pr.FulfilInc_Cost, pr.RequiredDte, pr.Destination
>from PurchReqTags prt
>inner join PurchRequisition pr on ( prt.Inc_Reqn = pr.Inc_Reqn )
>left join JobMast jm on ( jm .JobNo = pr.RequesterNo )
>where ( prt.Inc_ReqnB = :AiInc_ReqnB )
>and ( pr.Actioned = 'N' )
>and ( pr.FulFilSuppNo = :sSuppNo )

Assuming that you do actually want the inner join, isn't the wrong table
driving this query? If the PK of PurchRequisition is Inc_Reqn and the
query were...

select pr.Inc_Reqn, <--- not prt.Inc_Reqn
pr.PartNo, pr.PartParam, pr.Operation,
pr.FulfilUnitsQty, pr.RequesterNo, jm.Division,
pr.FulfilInc_Cost, pr.RequiredDte, pr.Destination
from PurchRequisition pr
join PurchReqTags prt
on pr.Inc_Reqn = prt.Inc_Reqn
left join JobMast jm on pr.RequesterNo = jm .JobNo
where prt.Inc_ReqnB = :AiInc_ReqnB
and pr.Actioned = 'N'
and pr.FulFilSuppNo = :sSuppNo

.. I think you would find the primary key index being used...
Other thoughts:
1. Wildly guessing that you named the indexes on PurchReqTags as you did,
in the expectation that they would force an ordering if you placed prt on
the lefthand side of the join - - they won't, i.e. this is not a
justification for putting the lookup table on the left. You get ordered
sets (and have the indexes used for sorting) only via an ORDER BY clause.

2. More guessing: I don't see how the left join on JobMast is going to be
useful, since, where the RequesterNo doesn't find a matching JobNo, it will
return rows consisting of all nulls except in pr.Inc_Reqn. If this is what
you want, fine; if not, you can speed up the query by using an inner join
or a correlated subquery instead to get the Division from JobMast.

heLen