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: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 24 Aug 2006 11:28:45 -0700
Message-ID: <1156444124.350025@bubbleator.drizzle.com>


yogi wrote:
> DA Morgan wrote:

>> yogi wrote:
>>> 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
>> 1. Please do not top post. People read from top to bottom. Post
>> your comments at the bottom.
>>
>> And still no version number? Do you really want help or just to
>> waste people's time?
>>
>> 2. The single most important thing you can do to improve the
>> performance of your code is to get rid of the cursor loop. I'm
>> not sure why this couldn't be done with a simple update statement
>> but if not you need to learn how to use BULK COLLECT and FORALL.
>>
>> Demos in Morgan's Library at www.psoug.org.
>>
>> And if you want help again be courteous to those who are trying
>> to help you. You still didn't post a version number. In the future
>> you need to make an effort.
>> --
>> 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

>
> Hi
>
> it's version Oracle 9i....
>
> i tried it with a single update as well as BULK collect and for
> all..it's not letting me to refrence it's element with (i) suffix to
> the variables. i am posting my sigle UPDATE statement here...
>
>
>
> Update (SELECT t.SupplierCommentCode
> newSupplierCommentCode,
> t.CustomerCommentCode
> newCustomerCommentCode,
> t.PCCommentCode newPCCommentCode,
> t1.SupplierCommentCode
> oldSupplierCommentCode,
> t1.CustomerCommentCode
> oldCustomerCommentCode,
> t1.PCCommentCode oldPCCommentCode
> from Ems_Currentsoexpedite t1
> ,Ems_Currentsoexpedite_Tmp t
> WHERE upper(trim(t1.FPRunIdentifier))
> = 'EOD'
> and trim(t1.ShopOrderNumber) =
> t.ShopOrderNumber
> and trim(nvl(t1.RequestDate,trunc(sysdate))) =
> trim(nvl(t.RequestDate,trunc(sysdate)))
> and trim(t1.DemandOrderId) =
> t.DemandOrderId
> and trim(t1.PartNumber) =
> t.PartNumber
> and nvl(t1.promisedate,trunc(sysdate)) =
> nvl(t.promisedate,trunc(sysdate))
> and nvl(trunc(t1.daterequired),trunc(sysdate)) =
> nvl(t.daterequired,trunc(sysdate))
> and upper(trim(t.FPRunIdentifier)) = (Select
> decode(count(distinct a.fprunidentifier),1,'EOD','FIRST')
> From
> Ems_Currentsoexpedite_Tmp a
> Where
> upper(a.fprunidentifier)='EOD')
> and (t.SupplierCommentCode is not null or
> t.CustomerCommentCode is not null or t.PCCommentCode is not null))
> Set oldSupplierCommentCode = newSupplierCommentCode,
> oldCustomerCommentCode = newCustomerCommentCode,
> oldPCCommentCode = newPCCommentCode
>
>
> but this query gives me error ..ORA-01779 -can not modify a column
> which maps to a non-key preserved column.
>
> bulk collect also i used.....i didn't work out...
>
> Thanks a lot to you dude for teaching me FORUM rules and your
> advice...as i am new to this.

There is nothing in your cursor that couldn't be converted into a bulk collect. If you are having a problem with it then compare what you wrote to my demo labelled: Fast Way 1 which includes a cursor definition.

-- 
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 - 13:28:45 CDT

Original text of this message

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