At 10:21 PM 25-09-01 +0000, you wrote:

>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.

I suppose it is shutting the stable door after the horse has bolted, but I'd like to have seen the queries that were taking so long...

>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

So do you have enrol records that have no associated student? Just curious...

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

This isn't surprising in a correlated sub-select. You would do a much more more efficient job here with a stored procedure - one result set for all of the updates instead of multiple result sets for each single update.

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

Not worth it.

>The read number on the student table was 100 Million!!
>
>This took about 2 hours to run...
>
>What am I doing wrong?

Simply causing the db engine to run around in circles. But you also made your query ambiguous by omitting the table identifier in the subselect 'SELECT REVENUE_NO...'

Try this:

CREATE PROCEDURE UPDATE_ENROL
AS
DECLARE VARIABLE VREVENUE_NO INTEGER; (or whatever type required)
DECLARE VARIABLE VCRT_NO INTEGER; (or whatever type required)
DECLARE VARIABLE VSTD_NO INTEGER; (or whatever type required)
FOR SELECT RF.REVENUE_NO,
RF.CRT_NO
S.STUD_NO
FROM REVENUE_FACULTY RF
JOIN STUDENT S
ON RF.OS = S.OS
INTO :VREVENUE_NO, :VCRT_NO, :VSTUD_NO DO
BEGIN
UPDATE ENROL
SET REVENUE_NO = :VREVENUE_NO
WHERE STUD_NO = :VSTUD_NO
AND CRT_NO = :VCRT_NO;
END
END

regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________