Thread: Ask To Optimize Looping

Ask To Optimize Looping

From
Otniel Michael
Date:
<span style="color: rgb(255, 0, 0);">Hi guys, can anyone help to optimize this loop :</span><br /><br />    <span
style="color:rgb(51, 51, 255);">for i in</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51,
255);">       select kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4,NILAIPROP as nilaiygdibagi</span><br
style="color:rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">        from EDP040_07_23</span><br
style="color:rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">        order by
kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4</span><brstyle="color: rgb(51, 51, 255);" /><span
style="color:rgb(51, 51, 255);">    loop</span><br style="color: rgb(51, 51, 255);" /><br style="color: rgb(51, 51,
255);"/><span style="color: rgb(51, 51, 255);">        jmlData2 = jmlData2 + 1;</span><br style="color: rgb(51, 51,
255);"/><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">        if (jmlData2 = 500)
then</span><brstyle="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">            jmlData3 = jmlData3
+jmlData2;</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">            jmlData2 =
0;</span><brstyle="color: rgb(51, 51, 255);" /><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51,
255);">           Raise Notice '' 12 - 2 - EDP040_07_23 = %'', jmlData3;</span><br style="color: rgb(51, 51, 255);"
/><spanstyle="color: rgb(51, 51, 255);">        end if;</span><br style="color: rgb(51, 51, 255);" /><br style="color:
rgb(51,51, 255);" /><span style="color: rgb(51, 51, 255);">        Update EP_ES06_N_TEMP2</span><br style="color:
rgb(51,51, 255);" /><span style="color: rgb(51, 51, 255);">        Set NilPakai = NilPakai + case when i.nilaiygdibagi
>0 then VNilaiPembagi else -1*VNilaiPembagi end</span><br style="color: rgb(51, 51, 255);" /><span style="color:
rgb(51,51, 255);">        Where (THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF,</span><br
style="color:rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">              
JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl)in</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51,
255);">           (Select THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF,</span><br
style="color:rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">                   
JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl</span><brstyle="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51,
255);">               From EP_ES06_N_TEMP2</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51,
255);">               Where DIVCOD = i.kodedivisi and</span><br style="color: rgb(51, 51, 255);" /><span style="color:
rgb(51,51, 255);">                    WIPCOD = i.kodewip and</span><br style="color: rgb(51, 51, 255);" /><span
style="color:rgb(51, 51, 255);">                    NBATCH = i.nobatch and</span><br style="color: rgb(51, 51, 255);"
/><spanstyle="color: rgb(51, 51, 255);">                    SKSFOH = i.kodeseksi and</span><br style="color: rgb(51,
51,255);" /><span style="color: rgb(51, 51, 255);">                    trim(MAINAC) = trim(i.ket1) and</span><br
style="color:rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">                    trim(SUB_AC) =
trim(i.ket2)and</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">                   
trim(BAGCOD)= trim(i.ket3) and</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">   
               trim(SLGIDC) = trim(i.ket4)</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51,
255);">               Order by thbltr desc,divcod desc,wipcod desc,nbatch desc,</span><br style="color: rgb(51, 51,
255);"/><span style="color: rgb(51, 51, 255);">                    SKSFOH desc,mainac desc,sub_ac desc,bagcod
desc,slgidcdesc,dsrtrf desc,</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">   
               jampakai desc,nilpakai desc,kodehsl desc,nobatchhsl desc </span><br style="color: rgb(51, 51, 255);"
/><spanstyle="color: rgb(51, 51, 255);">                    limit (abs(i.nilaiygdibagi) / VNilaiPembagi));</span><br
style="color:rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">    end loop;</span><br clear="all" /><br
/><spanstyle="color: rgb(255, 0, 0);">This loop need 30 minutes (18.000 record data), and the raise notice will show
every50 second (500 record data).</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0,
0);">Thanksanyway.</span><br /><br />-- <br />-------------------------------------------------------------------<br
/>"Hewho is quick to become angry will commit folly, and a crafty man is hated"<br /> 

Re: Ask To Optimize Looping

From
"Marc Mamin"
Date:
Hello,
 
I would try to replace the loop with a single  UPDATE FROM  Statement:
 
 
Update EP_ES06_N_TEMP2
        Set  ....
FROM  (
        select kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4,NILAIPROP as nilaiygdibagi
        from EDP040_07_23
        --order by kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4
        ) i
 
WHERE ..
 
 
Here a simple example for this syntax:

create table test (i int);
insert into test select * from generate_series (1,20);
 
update test set i =0
from (select * from generate_series (1,10) s)i
where test.i=i.s;
--Query returned successfully: 10 rows affected
 
 
But beware the limitation of update from:
 
"When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join. "

HTH,

 

Marc Mamin