Re: Slow Code
Date: 1996/06/16
Message-ID: <4q0uec$6ri_at_news00.btx.dtag.de>#1/1
Wolf Kammer <uzr10a_at_work6c.rhrz.uni-bonn.de> wrote:
>Hi to everybody,
>why is the following code extremly slow in execution and
>how can it be done better ? en is 50000. I need to now for
>each uzsid in the table uzscontrol whether it shows up in
>at least in one of the three tables and to mark it, if it does
>not.
>declare en number(8);
> em number(8);
> m1 number(8);
> m2 number(8);
> m3 number(8);
> m4 number(8);
> a1 char(6);
>begin
>select count(*) into en from uzscontrol;
>for em in 1..en loop
> select uzsid into a1 from uzscontrol where uzsnm=em;
> select count(*) into m1 from uzsfrei where uzsid=a1;
> select count(*) into m2 from uzsbt where uzsid=a1;
> select count(*) into m3 from uzsht where uzsid=a1;
> m4 := m1 + m2 + m3;
> if m4 < 1 then update uzscontrol set uzsnm=0 where uzsnm=em;
> end if;
>end loop;
>end;
>/
>quit;
>Thanks in advance, Wolf Kammer
Use a correlated subquery with not exists clause like this (code not tested):
update uzscontrol
set uzsnm=0
where
uzsnm between 1 and 50000
and
not exists (select 1 from uzsfrei where uzsfrei.uzsid = uzscontrol.uzsid) and not exists (select 1 from uzsbt where uzsbt.uzsid = uzscontrol.uzsid) and not exists (select 1 from uzsht where uzsht.uzsid = uzscontrol.uzsid)
;
The "not exists" should terminate if only one row is found; your "count(*)" will travel through all rows.
In your example, if you expect to get many rows, then the "not exist" will save you plenty of time....
Willy Klotz
Willys Mail FidoNet 2:2474/117 2:2474/118 Mailbox: analog 06297 910104 ISDN 06297 910105 Internet: 0629791010_at_t-online.de -> No Request from 06.00 to 08.00 <- ======================================================================Received on Sun Jun 16 1996 - 00:00:00 CEST