Re: Performance improvment -loop update.

yogi wrote:
> Hi all,
>
> Please help me in tuning this query...
>
> Declare
> i Number:=0;
> Cursor c1 is
> Select SupplierCommentCode,
> CustomerCommentCode,
> PCCommentCode,
> trim(ShopOrderNumber) ShopOrderNumber,
> trim(RequestDate) RequestDate,
> trim(DemandOrderId) DemandOrderId,
> trim(PartNumber) PartNumber,
> detailstoexpedite,
> pst,pet,expediteqty,
> trunc(daterequired) daterequired,
> trunc(promisedate) promisedate
> from Ems_Currentsoexpedite_Tmp
> where upper(trim(FPRunIdentifier)) = (Select
> decode(count(distinct fprunidentifier),1,'EOD','FIRST')
> From
> ems_currentsoexpedite_tmp
> Where
> upper(fprunidentifier)='EOD'
> )
> and (SupplierCommentCode is not null or CustomerCommentCode
> is not null or PCCommentCode is not null);
> Begin
>
> --
> For c1_rec in c1 Loop
> Begin
> Update ems_currentsoexpedite
> Set SupplierCommentCode = c1_rec.SupplierCommentCode,
> CustomerCommentCode = c1_rec.CustomerCommentCode,
> PCCommentCode = c1_rec.PCCommentCode
> where upper(trim(FPRunIdentifier)) =
> 'EOD'
> and trim(ShopOrderNumber) =
> c1_rec.ShopOrderNumber
> and trim(nvl(RequestDate,trunc(sysdate))) =
> nvl(c1_rec.RequestDate,trunc(sysdate))
> and trim(DemandOrderId) =
> c1_rec.DemandOrderId
> and trim(PartNumber) =
> c1_rec.PartNumber
> and nvl(promisedate,trunc(sysdate)) =
> nvl(c1_rec.promisedate,trunc(sysdate))
> and nvl(trunc(daterequired),trunc(sysdate)) =
> nvl(c1_rec.daterequired,trunc(sysdate));
>
> If i=3000 then
> commit;
> i:=0;
> else
> i:=i+1;
> End if;
>
>
> Exception
> when others then
> NULL;
> End;
> End loop;
> commit;
> --
>
> -- Once the processing is done then delete the records from the temp
> table
> --
> Delete from ems_currentsoexpedite_tmp;
> --
> Commit;
>
>
>
> table Ems_Currentsoexpedite_Tmp is having 8000 records and cursor
> selects 7550 records...and table ems_currentsoexpedite is having 15000
> records and number of rows updated is 3000.
>
> index structure on the tables is
>
> create index NIDX_EMSCURRENTSOEXPEDITE on EMS_CURRENTSOEXPEDITE
> (FPRUNIDENTIFIER,DATEREQUIRED,PARTNUMBER,SHOPORDERNUMBER,DEMANDORDERID,CUSTOMERCODE,SUPPLIERID,PURCHASEORDERNUMBER,DETAILSTOEXPEDITE)
>
>
> create index NUIDX_CURRENTSOEXPEDITETMP on EMS_CURRENTSOEXPEDITE_TMP
> (FPRUNIDENTIFIER,SHOPORDERNUMBER,REQUESTDATE,DEMANDORDERID,PARTNUMBER,DETAILSTOEXPEDITE,PST,PET,EXPEDITEQTY)
>
> please help me..i have to finish it very urgently.....thank a lot in
> advance.

Remove the commit in the LOOP after the i=3000. After a commit is done in a loop it invalidates the cursor so no more records are retrieved after that. (Steven Fuerstein has a humorous example in his PL/SQL book of a "Job Jar" that only seemed to return one job for him to do regardless of how many his wife entered into the table).

If your version supports it, (9i +) use BULK COLLECT, it's faster.

HTH Received on Thu Aug 24 2006 - 17:58:54 CEST