pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAsk To Optimize Looping
2+ messages / 2 participants
[nested] [flat]
* Ask To Optimize Looping
@ 2009-08-19 09:22 Otniel Michael <otnieltera@gmail.com>
2009-08-19 15:46 ` Re: Ask To Optimize Looping Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 2+ messages in thread
From: Otniel Michael @ 2009-08-19 09:22 UTC (permalink / raw)
To: pgsql-sql
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"
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Ask To Optimize Looping
2009-08-19 09:22 Ask To Optimize Looping Otniel Michael <otnieltera@gmail.com>
@ 2009-08-19 15:46 ` Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 2+ messages in thread
From: Marc Mamin @ 2009-08-19 15:46 UTC (permalink / raw)
To: Otniel Michael <otnieltera@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-08-19 15:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-19 09:22 Ask To Optimize Looping Otniel Michael <otnieltera@gmail.com>
2009-08-19 15:46 ` Marc Mamin <M.Mamin@intershop.de>
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