Ask To Optimize Looping - Mailing list pgsql-sql

<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 />