Thanks for your time and efforts Helen,

I entered the procedure and executed it - I stopped Interbase after 1
1/2 hours.

So no great improvement.

I have been developing apps using Interbase for over 4 years and have
about 30 customers using Interbase - I am looking down the barrel of
dumping Interbase - this is not what I want to choose.

The basis of my question I guess is why is it so difficult with
Interbase and so easy for SQL_SERVER?

Hours compared to 2 seconds, for exactly the same query - on exactly
the same server.

Why do I need to enter a procedure for Interbase, when I don't to get
a decent response from SQL_SERVER?

I have over 30 customers using Interbase - and my larger customers
(ie database size > 100mb) the reports die with blown temp files on
the server.

SQL_SERVER replies in seconds with the same complex queries entered
using SQL_Explorer.

Do I have to re-write my app to use SQL_SERVER instead?

John

--- In ib-support@y..., Helen Borrie <helebor@d...> wrote:
> 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...

I have too many - my aim in showing a simple example indicating the
nature of the problem.

>
>
> >So I thought I would de-normalise the table structure and added a
new
> >field 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...

Definitely not - foreign keys are used liberally

>
>
> >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 writing 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
> _______________________________________________________