Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Performance improvment -loop update.

Re: Performance improvment -loop update.

From: yogi <yogigoyal_at_gmail.com>
Date: 24 Aug 2006 09:43:55 -0700
Message-ID: <1156437835.812687.317380@h48g2000cwc.googlegroups.com>


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
Received on Thu Aug 24 2006 - 11:43:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US