Hi,

Help please!

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
PLAN JOIN (JOIN (NLT NATURAL,JCA INDEX (JC_ALLOCATIONS_IDX2)),J INDEX (RDB$PRIMARY88))

Adapted Plan
PLAN JOIN (JOIN (NLT NATURAL,JCA INDEX (JC_ALLOCATIONS_IDX2)),J INDEX (INTEG_422))

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.
Firebird version is 1.5.2.

The performance results provided by IBExpert are as follows:


Query Time
------------------------------------------------
Prepare : 0.00 ms
Execute : 2,297.00 ms
Avg fetch time: 328.14 ms


Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 958,013


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| JC_ALLOCATIONS| 0 | 409734 | 0 | 0 | 0 | 0 |
| JC_JOBS| 0 | 7 | 0 | 0 | 0 | 0 |
| NL_TRANSACTIONS| 0 | 0 | 22763 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

There are 22763 records in NL_TRANSACTIONS, 7 records in JC_ALLOCATIONS and 1 record in JC_JOBS.
Not sure why it would be reading JC_ALLOCATIONS 409734 times!

I cannot change the query to select from JC_JOBS, and left outer join on the other 2 tables due to the other logic required by the report, which I have removed from this example in order to simplify it.

Thanks in Advance

Maya McLeod