Hi all, any help would be appreciated.

I have been finding (even after backup and restore) that Interbase is
taking and incredibly long time, to do complex queries.

So I thought I would de-normalise the table structure and added a new
filed and wrote this script.

UPDATE ENROL E
SET REVENUE_NO = (
SELECT REVENUE_NO
FROM REVENUE_FACULTY RF, STUDENT S
WHERE S.STUD_NO = E.stud_no
AND RF.CRT_NO = E.crt_no
AND RF.OS = S.OS)

There is only one rf.revenue_no for each rf.crt_no and rf.os

The enrol table is one to many - although most enrol table entries
have only one associated student

The enrol table has about 10,000 records, and student table about
7,000

The plan only used natural order - and ignored the stud_no primary
key.

I have seen some doc on righting your own plan but this seems like re-
inventing the wheel.

The read number on the student table was 100 Million!!

This took about 2 hours to run...

What am I doing wrong?

Thanks in advance for any clues.

John Newcombe


PS Sacrilege - I datapumped the entire database into a sql_server db -
and ran the exact same query with no indexes and it took 2 secs!!