Re: Slow Code

In <Pine.A32.3.93.960607114932.21344A-100000_at_work6c.rhrz.uni-bonn.de> Wolf Kammer <uzr10a_at_work6c.rhrz.uni-bonn.de> writes:
>
>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
>
>

Slow is a relative term. It is slow only if there is a faster way to accomplish the same thing. You are looping 50,000 times. The typical algorithmic solutions are time vs space. You can increase time by using more storage. You can reduce storage by using more time for computation.

In your case, consider storing the count in the table rather than counting it (pay me now or pay me later). You may need a trigger to keep the count updated. Alternatively, consider using COST based optimization and analyze with compute statistics the table(s) in question.

Also, do you have indexes on uszid and uzsnm?

What is your sort size? Is the procedure using a lot of disk i/o vs memory sort and memory hits? Look at some tuning inidicators.

Hope this helps.

Steve
804-262-6332 Received on Fri Jun 07 1996 - 00:00:00 CEST