Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3ADF063355E for ; Wed, 19 Aug 2009 06:29:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 00231-09 for ; Wed, 19 Aug 2009 09:29:40 +0000 (UTC) X-Greylist: delayed 00:07:20.61597 by SQLgrey-1.7.6 Received: from mail-iw0-f186.google.com (mail-iw0-f186.google.com [209.85.223.186]) by mail.postgresql.org (Postfix) with ESMTP id C2FEF632F07 for ; Wed, 19 Aug 2009 06:29:44 -0300 (ADT) Received: by iwn16 with SMTP id 16so109268iwn.15 for ; Wed, 19 Aug 2009 02:29:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=jE8/1zjqyrqGVfviksnSaTH7BqKmA3FiYMC5xcCl5VI=; b=B9o+4nVb+9fVmK2V7R5yRUzrm0WRnS7Vg+8xuKxWLIrRRg6yCOYh1VkL2f373ezfeh Q69UVsve0oA7JngZ76klhoYs0VF0MbFkpKXskQVS3BYWxUXDyyjYpq1ahrseNB9xHqUg M6IC95fAGzY7iFk6khIz1hQFrvbIR3QHg0TZU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=jj06844H8XFkBTgCnSz3d4m2Xtgj/pu1mRHCcV6QyeT/6UbQc2WE5JYjatFRBUzJ0T EsX/MMzC4h6CosLdVsnRHS1Dhn23G4yzi7ZNHsRCAP3UEJFaFOVxF9t/xwna9DF6k20N Op8MIdJTBMJX5ccLYXQPyfrCCMzzRlUv4TDhQ= MIME-Version: 1.0 Received: by 10.231.12.135 with SMTP id x7mr3836146ibx.42.1250673741740; Wed, 19 Aug 2009 02:22:21 -0700 (PDT) Date: Wed, 19 Aug 2009 16:22:21 +0700 Message-ID: <1703cd180908190222n724f1efcyb8658a4ed0663c0e@mail.gmail.com> Subject: Ask To Optimize Looping From: Otniel Michael To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0003255750fe1b3da204717b2c46 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.184 tagged_above=-10 required=5 tests=BAYES_40=-0.185, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/100 X-Sequence-Number: 33009 --0003255750fe1b3da204717b2c46 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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" --0003255750fe1b3da204717b2c46 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi guys, can anyone help to optimize= this loop :

=A0=A0=A0 for i in
=A0=A0=A0=A0=A0=A0=A0 select kodedivisi,kodeseksi,ko= dewip,nobatch,ket1,ket2,ket3,ket4,NILAIPROP as nilaiygdibagi
=A0=A0=A0 =A0=A0=A0 from EDP040_07= _23
=A0=A0=A0 =A0=A0=A0 order by kodedivisi,kodeseksi,kodewip,no= batch,ket1,ket2,ket3,ket4
=A0=A0=A0 loop

=A0=A0=A0 =A0=A0=A0 jmlData2 =3D jmlData2 + = 1;

=A0=A0=A0 =A0=A0=A0 if (jmlData2 =3D 500) then
=A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 jmlData3 =3D jmlData3 + jmlData2;
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 jmlD= ata2 =3D 0;

=A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 Raise Notice '' 12 - 2 - EDP040_07_23 =3D %'&#= 39;, jmlData3;
=A0=A0=A0 =A0=A0=A0 end if;=

=A0=A0=A0 =A0=A0=A0 Update EP_E= S06_N_TEMP2
=A0=A0=A0 =A0=A0=A0 Set NilPakai = =3D NilPakai + case when i.nilaiygdibagi > 0 then VNilaiPembagi else -1*= VNilaiPembagi end
=A0=A0=A0 =A0=A0=A0 Where (THBLTR,DIVCOD,WIPC= OD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF,
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0 JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl) in
=A0=A0=A0 =A0=A0=A0= =A0=A0=A0 (Select THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,= SLGIDC,DSRTRF,
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl
=A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 From EP_ES06_N_TEMP2
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 Where DIVCOD =3D i.kodedivisi and
=A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 =A0=A0=A0 WIPCOD =3D i.kodewip and
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 NBATCH =3D i.nobatch and
=A0=A0=A0 =A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 SKSFOH =3D i.kodeseksi and
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 trim(MAINAC) =3D trim(i.ket1) and
=A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 trim(SUB_AC) =3D trim(i.ket2) and
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 trim(BAGCOD) =3D trim(i.ket3) and
=A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 trim(SLGIDC) =3D trim(i.ket4) =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 Order by thbltr desc,divcod desc,wipcod desc,nbatch desc,
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 SKSFOH desc,mainac desc,= sub_ac desc,bagcod desc,slgidc desc,dsrtrf desc,
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 jampakai desc,nilpakai desc,kodehsl desc,nobatchhsl desc <= /span>
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 limit (abs(i.n= ilaiygdibagi) / VNilaiPembagi));
=A0=A0=A0 end loop;

This loop need 30 minu= tes (18.000 record data), and the raise notice will show every 50 second (5= 00 record data).
Thanks anyway.

--
= -------------------------------------------------------------------
&quo= t;He who is quick to become angry will commit folly, and a crafty man is ha= ted"
--0003255750fe1b3da204717b2c46--