Re: Slow Code

From: Willy Klotz <willyk_at_t-online.de>
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

Original text of this message