Maya McLeod wrote:

> I am having trouble trying to get a query to make use of indexes
> in order to make it faster.
>
> The query (simplified) is as follows:
>
> select * from NL_Transactions NLT
> left outer join JC_Allocations JCA
> on (JCA.TranNo = NLT.ID)
> left outer join JC_Jobs J
> on (J.JobNo = JCA.JobNo)
> where (JCA.JobNo = :IJobNo)
>
> PLAN JOIN (JOIN (NLT NATURAL,JCA INDEX (JC_ALLOCATIONS_IDX2)),J INDEX (RDB$PRIMARY88))
>
>
> I am assuming that my performance problem is caused by the following
> part of the plan: NLT NATURAL. There is an index on NLT.ID, so I am
> not sure why it is not being used.

The problem is with the query, not with the plan. In FROM part of the
select, you're asking for all records from the NL_Transactions table,
with optional matching records from the JC_Allocations table and
optional matching records from the JC_Jobs table. The WHERE clause
subsequently eliminates NL_Transaction records that don't have a
matching JC_Allocations record, but by then the damage is done.

Change the first left outer join to an inner join.

As a general rule, referencing a table that's the right side of a left
outer join in the WHERE clause is a red flag. Either the condition
should be in the ON clause or the join should be inner, not outer.

Regards,


Ann