John,

I got your update down to a few secs using a procedure, which
I could optimize a bit more. I'm quite willing to give you
a hand so you buy some time before making the switch :)

Best Regards
Hans

-----
CREATE PROCEDURE JOHN_UPDATE
RETURNS ( STATUS INTEGER )
AS
declare variable revenue_no integer;
declare variable crt_no integer;
declare variable stud_no integer;
begin
/*
UPDATE ENROL E
SET E.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)
*/

for SELECT RF.REVENUE_NO, E.crt_no, e.stud_no
FROM REVENUE_FACULTY RF, STUDENT S, enrol e
WHERE S.STUD_NO = E.stud_no
AND RF.CRT_NO = E.crt_no
AND RF.OS = S.OS
into :REVENUE_no,:crt_no,:stud_no
DO
begin
status = 1;
UPDATE ENROL EE
SET EE.REVENUE_NO =
:REVENUE_no
where EE.crt_no = :crt_no
and ee.stud_no = :stud_no;
//suspend;
end


suspend;
end

-----

john@... wrote:

>
> Thanks Hans and all,
>
> My customers databases have grown and as the volume of data grows and
> complexity of reports - I quite often get no result - (either blown
> temp files or 6+ hrs later the server is still at 100%)
>
> In one update, using the query before mentioned, at my customers site
> (in Sydney with me in Florida) it took 3 1/2 days ... I told them to
> hang in there ... they did ... but I expected less than a day.
>
> I have settled on ditching IB - thanks for all your help - but I have
> to give fast results - in minimum time and after 5 years with IB -
> and all the tools and selling I have done - that's it - no more - my
> credibility is shot - my customers blame me/my company for poor db
> response - not IB or MS SQL.
>
> I am using the BDE, so will transfer all the data, rework the
> generator stuff and re-write the db procedures.
>
> Helen, I respect and thank you for your fine efforts to all IB users -
> but this has got too much - I need fast replies from my db - and I
> can see much better options without IB - sorry.
>
> Best regards to all,
>
> John
>
> --- In ib-support@y..., hans@h... wrote:
> > Hi Helen,
> >
> > I generated all the tables and procedures he had in his fine
> example
> > and had to kill my 800Mhz Pentium server with 512M of Ram, since it
> > never came back with an anwer after a couple of mins.
> >
> > Best Regards
> > Hans
> > -------------------------------------------------------------------
> >
> > Helen Borrie wrote:
> > >
> > > John,
> > > Apologies if you already answered these:
> > >
> > > 1. Was SQLServer running on the same machine as IB when you ran
> your various queries?
> > >
> > > 2. Does the machine have multiple processors?
> > >
> > > Helen
> > >
> > > All for Open and Open for All
> > > InterBase Developer Initiative · http://www.interbase2000.org
> > > _______________________________________________________
> > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > ib-support-unsubscribe@egroups.com
> > >
> > >
> > >
> > > Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/