Re: Performance improvment -loop update.

[Quoted] [Quoted] well Dude sorry if find it offensive...i didn't mean that.....

but i got a solution for it..i am posting newly written proc here....it's taking only some seconds....

thanks a lot ppl...it was ny first time i asked for the help here..and i got nice response.

Cursor c1 is

        Select    a.SupplierCommentCode,
                  a.CustomerCommentCode,
                  a.PCCommentCode,
                  b.rowid
            from  Ems_Currentsoexpedite_Tmp a, ems_currentsoexpedite b
            where upper(trim(a.FPRunIdentifier)) = (Select
decode(count(distinct fprunidentifier),1,'EOD','FIRST')
                                                  From
ems_currentsoexpedite_tmp
                                                  Where
upper(fprunidentifier)='EOD'
                                                 )
            and (a.SupplierCommentCode is not null or
a.CustomerCommentCode is not null or  a.PCCommentCode is not null)
            and a.demandorderid=b.demandorderid
            and a.partnumber=b.partnumber
            and a.expediteqty=b.expediteqty
            and a.detailstoexpedite=b.detailstoexpedite
            and nvl(a.daterequired, trunc(sysdate))=nvl(b.daterequired,
trunc(sysdate))
            and upper(b.fprunidentifier)='EOD';

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   rowid=c1_rec.rowid;
            /* 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));
*/
           Exception
              when others then
                 NULL;
           End;
       End loop;

       commit;
--

v_stepid := v_stepid +10;
--
-- Once the processing is done then delete the records from the temp
table
--
       execute immediate 'Truncate table EMS_CURRENTSOEXPEDITE_TMP';
--
Commit;
v_stepid := v_stepid +10;
--
          --
End update_emsaftnooncomments;


DA Morgan wrote:


> yogi wrote:

> > Hi all,

> >

> > Please help me in tuning this query...


>


> You've got to be kidding. I help a lot of people and try to keep


> an open mind but your question, as I read it, is offensive.

>


> You provide no information about the system


> No SLA

> No Explain Plan

> You have a cursor loop which has been repeatedly sited as inefficient

> compared with array processing.

>


> And then you want someone else to do what you are paid for?


> And urgently too?

>


> No! Perhaps I just need some coffee. But if you are not qualified


> to do your job then quit.

>


> Now I'll go get some coffee. ;-)


> --

> Daniel A. Morgan

> University of Washington

> damorgan_at_x.washington.edu

> (replace x with u to respond)

> Puget Sound Oracle Users Group

> www.psoug.org