Re: Performance improvment -loop update.
- From: "rogergorden...@xxxxxxxxx" <rogergorden@xxxxxxxxx>
- Date: 24 Aug 2006 08:58:54 -0700
yogi wrote:
Hi all,
Please help me in tuning this query...
Declare
i Number:=0;
Cursor c1 is
Select SupplierCommentCode,
CustomerCommentCode,
PCCommentCode,
trim(ShopOrderNumber) ShopOrderNumber,
trim(RequestDate) RequestDate,
trim(DemandOrderId) DemandOrderId,
trim(PartNumber) PartNumber,
detailstoexpedite,
pst,pet,expediteqty,
trunc(daterequired) daterequired,
trunc(promisedate) promisedate
from Ems_Currentsoexpedite_Tmp
where upper(trim(FPRunIdentifier)) = (Select
decode(count(distinct fprunidentifier),1,'EOD','FIRST')
From
ems_currentsoexpedite_tmp
Where
upper(fprunidentifier)='EOD'
)
and (SupplierCommentCode is not null or CustomerCommentCode
is not null or PCCommentCode is not null);
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 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));
If i=3000 then
commit;
i:=0;
else
i:=i+1;
End if;
Exception
when others then
NULL;
End;
End loop;
commit;
--
-- Once the processing is done then delete the records from the temp
table
--
Delete from ems_currentsoexpedite_tmp;
--
Commit;
table Ems_Currentsoexpedite_Tmp is having 8000 records and cursor
selects 7550 records...and table ems_currentsoexpedite is having 15000
records and number of rows updated is 3000.
index structure on the tables is
create index NIDX_EMSCURRENTSOEXPEDITE on EMS_CURRENTSOEXPEDITE
(FPRUNIDENTIFIER,DATEREQUIRED,PARTNUMBER,SHOPORDERNUMBER,DEMANDORDERID,CUSTOMERCODE,SUPPLIERID,PURCHASEORDERNUMBER,DETAILSTOEXPEDITE)
create index NUIDX_CURRENTSOEXPEDITETMP on EMS_CURRENTSOEXPEDITE_TMP
(FPRUNIDENTIFIER,SHOPORDERNUMBER,REQUESTDATE,DEMANDORDERID,PARTNUMBER,DETAILSTOEXPEDITE,PST,PET,EXPEDITEQTY)
please help me..i have to finish it very urgently.....thank a lot in
advance.
Remove the commit in the LOOP after the i=3000. After a commit is done
in a loop it invalidates the cursor so no more records are retrieved
after that. (Steven Fuerstein has a humorous example in his PL/SQL book
of a "Job Jar" that only seemed to return one job for him to do
regardless of how many his wife entered into the table).
If your version supports it, (9i +) use BULK COLLECT, it's faster.
HTH
.
- References:
- Performance improvment -loop update.
- From: yogi
- Performance improvment -loop update.
- Prev by Date: Performance improvment -loop update.
- Next by Date: Re: Performance improvment -loop update.
- Previous by thread: Performance improvment -loop update.
- Next by thread: Re: Performance improvment -loop update.
- Index(es):
Relevant Pages
|
