Re: Performance improvment -loop update.

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

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