From: Otniel Michael <otnieltera@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Ask To Optimize Looping
Date: Wed, 19 Aug 2009 16:22:21 +0700
Message-ID: <1703cd180908190222n724f1efcyb8658a4ed0663c0e@mail.gmail.com> (raw)
Hi guys, can anyone help to optimize this loop :
for i in
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
loop
jmlData2 = jmlData2 + 1;
if (jmlData2 = 500) then
jmlData3 = jmlData3 + jmlData2;
jmlData2 = 0;
Raise Notice '' 12 - 2 - EDP040_07_23 = %'', jmlData3;
end if;
Update EP_ES06_N_TEMP2
Set NilPakai = NilPakai + case when i.nilaiygdibagi > 0 then
VNilaiPembagi else -1*VNilaiPembagi end
Where
(THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF,
JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl) in
(Select
THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF,
JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl
From EP_ES06_N_TEMP2
Where DIVCOD = i.kodedivisi and
WIPCOD = i.kodewip and
NBATCH = i.nobatch and
SKSFOH = i.kodeseksi and
trim(MAINAC) = trim(i.ket1) and
trim(SUB_AC) = trim(i.ket2) and
trim(BAGCOD) = trim(i.ket3) and
trim(SLGIDC) = trim(i.ket4)
Order by thbltr desc,divcod desc,wipcod desc,nbatch desc,
SKSFOH desc,mainac desc,sub_ac desc,bagcod desc,slgidc
desc,dsrtrf desc,
jampakai desc,nilpakai desc,kodehsl desc,nobatchhsl desc
limit (abs(i.nilaiygdibagi) / VNilaiPembagi));
end loop;
This loop need 30 minutes (18.000 record data), and the raise notice will
show every 50 second (500 record data).
Thanks anyway.
--
-------------------------------------------------------------------
"He who is quick to become angry will commit folly, and a crafty man is
hated"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: otnieltera@gmail.com
Subject: Re: Ask To Optimize Looping
In-Reply-To: <1703cd180908190222n724f1efcyb8658a4ed0663c0e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox