Performance improvment -loop update.

From: yogi <yogigoyal_at_gmail.com>
Date: 24 Aug 2006 05:02:07 -0700
Message-ID: <1156420927.933135.295450_at_74g2000cwt.googlegroups.com>

[Quoted] Hi all,

[Quoted] 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. Received on Thu Aug 24 2006 - 14:02:07 CEST