Re: Performance improvment -loop update.

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

[Quoted]
1. Please do not top post. People read from top to bottom. Post your comments at the bottom.

[Quoted] And still no version number? Do you really want help or just to waste people's time?

[Quoted] 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.

[Quoted] Demos in Morgan's Library at www.psoug.org.

[Quoted] 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