Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT SLOWER THAN FOR CURSOR LOOP (PL/SQL , 9.2.0.7, SUN SOLARIS)
| BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314829] |
Thu, 17 April 2008 23:50  |
aliyesami
Messages: 17
Registered: October 2005
Location: Florida |
Junior Member |
|
|
all the documentations point to BULK COLLECT being faster than FOR CURSOR loop but Iam not getting the performance gain from it and its slower compared to FOR CURSOR LOOP. below is my code which are identical except one is using FOR CURSOR LOOP and other one BULK COLLECT . What am I doing wrong or missing ?
thanks
--------------------------------------------------------------------------------------------------------
-- exercise using BULK COLLECT - execution time 8.90 min
--------------------------------------------------------------------------------------------------------
AS
V_BALANCE_LAST Number := 0;
V_TOLL_COLLECTED Number := 0;
V_TOLL_SUSPENDED Number := 0;
V_BALANCE_LAST_NO Number := 0;
counter NUMBER := 0;
nbr_commits NUMBER := 0;
high_txn_id number ;
low_txn_id number ;
last_txn_id number ;
ERR_NUM NUMBER ;
ERR_MSG VARCHAR2 (100) ;
TYPE comml_cur_TY IS TABLE OF comml_cur_tab%ROWTYPE;
TY_comml_cur_TAB comml_cur_TY := comml_cur_TY();
CURSOR COMML_CUR is
select a.invtransp_transp_transp_id ,
c.acct_Acct_num,
(nvl(l.toll_amt_charged,0)/100) toll_amt_charged,
(nvl(l.toll_amt_collected,0)/100) toll_amt_collected,
(nvl(l.toll_amt_full,0)/100) toll_amt_full,
l.TXN_PROCESS_DATE,
nvl(l.COMMl_UPDATE_FLAG,'N') ,
l.ext_date_time,
l.txn_id ,
( nvl(c.COMM_ACCT_USE_PAID_AMT,0)
+ nvl(c.COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_OTHER,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_COLLECTED,0)) COMM_BALANCE
from pa_lane_txn l,
PA_ACCT_TRANSP a,
pa_commercial c
where l.txn_id between low_txn_id and high_txn_id
and l.txn_id > 179929
and l.transp_id = a.invtransp_transp_transp_id
AND c.acct_Acct_num = a.acct_Acct_num
and trans_source is NULL
and (nvl(l.toll_amt_collected,0)/100) <> 0
AND nvl(l.COMMl_UPDATE_FLAG,'N') = 'N'
and l.MSG_INVALID = 1
and c.acct_Acct_num not in (3793,3797,3655)
order by l.txn_id;
begin
SELECT MAX(TXN_ID) INTO HIGH_TXN_ID FROM PA_LANE_TXN
where txn_process_date between to_date('02-mar-2008 18:25:12','dd-mon-yyyy hh24:mi:ss')
and (trunc(sysdate) -1/86400);
---
SELECT LAST_UPDATED_TXN_ID
INTO LOW_TXN_ID
FROM PA_PROCESS
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
LAST_TXN_ID := LOW_TXN_ID ;
UPDATE PA_PROCESS
SET NO_COMMITS = 0 ,
COMMIT_TIME = SYSDATE
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
open comml_cur;
fetch comml_cur bulk collect into TY_comml_cur_TAB;
close comml_cur;
for i in 1..TY_comml_cur_TAB.last loop -- << MAIN LOOP >>
select (nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + TY_comml_cur_TAB(i).toll_amt_collected ))
Into V_BALANCE_LAST
from pa_commercial
where ACCT_ACCT_NUM = TY_comml_cur_TAB(i).acct_Acct_num ;
IF V_BALANCE_LAST >= 0 or TY_comml_cur_TAB(i).toll_amt_collected < 0 -- <<IF-2>>
THEN
update pa_commercial
set COMM_ACCT_TOTAL_TOLL_CHARGED = nvl(COMM_ACCT_TOTAL_TOLL_CHARGED,0) + TY_comml_cur_TAB(i).toll_amt_charged,
COMM_ACCT_TOTAL_TOLL_COLLECTED = nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + TY_comml_cur_TAB(i).toll_amt_collected,
COMM_ACCT_TOTAL_TOLL_FULL = nvl(COMM_ACCT_TOTAL_TOLL_FULL, 0) + TY_comml_cur_TAB(i).toll_amt_full,
TOTAL_IN_LANE_DISCOUNTS = nvl(TOTAL_IN_LANE_DISCOUNTS,0) + (TY_comml_cur_TAB(i).toll_amt_full - TY_comml_cur_TAB(i).toll_amt_charged)
where ACCT_ACCT_NUM = TY_comml_cur_TAB(i).acct_Acct_num ;
--- new 11/06
ELSE
IF (TY_comml_cur_TAB(i).COMM_BALANCE <= 0 and TY_comml_cur_TAB(i).toll_amt_collected > 0) -- <<IF-3>>
THEN
V_TOLL_SUSPENDED := TY_comml_cur_TAB(i).toll_amt_collected ;
ELSE
select
(nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) ))
Into V_BALANCE_LAST_NO
from pa_commercial
where ACCT_ACCT_NUM = TY_comml_cur_TAB(i).acct_Acct_num ;
--
V_TOLL_COLLECTED := V_BALANCE_LAST_NO ;
V_TOLL_SUSPENDED := TY_comml_cur_TAB(i).toll_amt_collected - V_TOLL_COLLECTED ;
END IF ; -- << END IF-3>>
--
update pa_commercial
set COMM_ACCT_TOTAL_TOLL_CHARGED = nvl(COMM_ACCT_TOTAL_TOLL_CHARGED,0) + TY_comml_cur_TAB(i).toll_amt_charged,
COMM_ACCT_TOTAL_TOLL_COLLECTED = nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + V_TOLL_COLLECTED,
where ACCT_ACCT_NUM = TY_comml_cur_TAB(i).acct_Acct_num ;
--- Updates pa_lane_txn with the real collected amount
---
END IF ; -- << END IF-2>>
counter := counter + 1;
nbr_commits := nbr_commits + 1;
last_txn_id := TY_comml_cur_TAB(i).txn_id ;
update pa_lane_txn
set COMMl_UPDATE_FLAG = 'Y'
where TXN_ID = TY_comml_cur_TAB(i).txn_id;
--
UPDATE PA_PROCESS
SET NO_COMMITS = NBR_COMMITS ,
COMMIT_TIME = SYSDATE ,
LAST_UPDATED_TXN_ID = last_txn_id
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
-- COMMIT;
--COMMIT;
end loop; -- << END MAIN LOOP >>
UPDATE PA_PROCESS
SET NO_COMMITS = NBR_COMMITS , COMMIT_TIME = SYSDATE ,LAST_UPDATED_TXN_ID = HIGH_TXN_ID
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
--COMMIT;
end GTB2;
--------------------------------------------------------------------------------------------------------------------------------
exercise using FOR CURSOR LOOP - execution time 8.05 sec
--------------------------------------------------------------------------------------------------------------------------------
AS
--
counter NUMBER := 0;
nbr_commits NUMBER :=0;
high_txn_id number ;
low_txn_id number ;
last_txn_id number ;
ERR_NUM NUMBER ;
ERR_MSG VARCHAR2 (100) ;
--
V_BALANCE_LAST Number := 0;
-- new 11/06/2003
V_TOLL_COLLECTED Number := 0;
V_TOLL_SUSPENDED Number := 0;
V_BALANCE_LAST_NO Number := 0;
--
CURSOR COMML_CUR is
select a.invtransp_transp_transp_id ,
c.acct_Acct_num,
(nvl(l.toll_amt_charged,0)/100) toll_amt_charged,
(nvl(l.toll_amt_collected,0)/100) toll_amt_collected,
(nvl(l.toll_amt_full,0)/100) toll_amt_full,
l.TXN_PROCESS_DATE,
nvl(l.COMMl_UPDATE_FLAG,'N') ,
l.ext_date_time,
l.txn_id ,
(nvl(c.COMM_ACCT_USE_PAID_AMT,0)
+ nvl(c.COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_OTHER,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_COLLECTED,0)) COMM_BALANCE
from pa_lane_txn l,
PA_ACCT_TRANSP a,
pa_commercial c
where l.txn_id between low_txn_id and high_txn_id
and l.txn_id > 1799296540
and l.transp_id = a.invtransp_transp_transp_id
AND c.acct_Acct_num = a.acct_Acct_num
and trans_source is NULL
and (nvl(l.toll_amt_collected,0)/100) <> 0
AND nvl(l.COMMl_UPDATE_FLAG,'N') = 'N'
and l.MSG_INVALID = 1
order by l.txn_id;
---
BEGIN
SELECT MAX(TXN_ID) INTO HIGH_TXN_ID FROM PA_LANE_TXN
where txn_process_date between to_date('02-mar-2008 18:25:12','dd-mon-yyyy hh24:mi:ss')
and (trunc(sysdate) -1/86400);
---
SELECT LAST_UPDATED_TXN_ID
INTO LOW_TXN_ID
FROM PA_PROCESS
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
LAST_TXN_ID := LOW_TXN_ID ;
UPDATE PA_PROCESS
SET NO_COMMITS = 0 ,
COMMIT_TIME = SYSDATE
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
FOR COMML_REC in COMML_CUR
LOOP --- << BEGIN CURSOR LOOP >>
IF (comml_rec.acct_Acct_num <> 3793 and comml_rec.acct_Acct_num <> 3797 and comml_rec.acct_Acct_num <> 3655) -- <<IF-1>>
THEN
select (nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + comml_rec.toll_amt_collected ))
Into V_BALANCE_LAST
from pa_commercial
where ACCT_ACCT_NUM = comml_rec.acct_Acct_num ;
IF V_BALANCE_LAST >= 0 or comml_rec.toll_amt_collected < 0 -- <<IF-2>>
THEN
update pa_commercial
set COMM_ACCT_TOTAL_TOLL_CHARGED = nvl(COMM_ACCT_TOTAL_TOLL_CHARGED,0) + comml_rec.toll_amt_charged,
COMM_ACCT_TOTAL_TOLL_COLLECTED = nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + comml_rec.toll_amt_collected,
COMM_ACCT_TOTAL_TOLL_FULL = nvl(COMM_ACCT_TOTAL_TOLL_FULL, 0) + comml_rec.toll_amt_full,
TOTAL_IN_LANE_DISCOUNTS = nvl(TOTAL_IN_LANE_DISCOUNTS,0) + (comml_rec.toll_amt_full - comml_rec.toll_amt_charged)
where ACCT_ACCT_NUM = comml_rec.acct_Acct_num ;
--- new 11/06
ELSE
IF (comml_rec.COMM_BALANCE <= 0 and comml_rec.toll_amt_collected > 0) -- <<IF-3>>
THEN
V_TOLL_SUSPENDED := comml_rec.toll_amt_collected ;
ELSE
select
(nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) ))
Into V_BALANCE_LAST_NO
from pa_commercial
where ACCT_ACCT_NUM = comml_rec.acct_Acct_num ;
--
V_TOLL_COLLECTED := V_BALANCE_LAST_NO ;
V_TOLL_SUSPENDED := comml_rec.toll_amt_collected - V_TOLL_COLLECTED ;
END IF ; -- << END IF-3>>
--
update pa_commercial
set COMM_ACCT_TOTAL_TOLL_CHARGED = nvl(COMM_ACCT_TOTAL_TOLL_CHARGED,0) + comml_rec.toll_amt_charged,
COMM_ACCT_TOTAL_TOLL_COLLECTED = nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + V_TOLL_COLLECTED,
where ACCT_ACCT_NUM = comml_rec.acct_Acct_num ;
---
END IF ; -- << END IF-2>>
counter := counter + 1;
nbr_commits := nbr_commits + 1;
last_txn_id := COMML_rec.txn_id ;
update pa_lane_txn
set COMMl_UPDATE_FLAG = 'Y'
where TXN_ID = comml_rec.txn_id;
--
UPDATE PA_PROCESS
SET NO_COMMITS = NBR_COMMITS ,
COMMIT_TIME = SYSDATE ,
LAST_UPDATED_TXN_ID = last_txn_id
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
-- COMMIT;
COUNTER := 0;
END IF; --<< END IF-1>>
--COMMIT;
end loop; -- << END CURSOR LOOP >>
UPDATE PA_PROCESS
SET NO_COMMITS = NBR_COMMITS , COMMIT_TIME = SYSDATE ,LAST_UPDATED_TXN_ID = HIGH_TXN_ID
WHERE PROCESS_NAME = 'COMML_BALANCE_UPDATE';
--COMMIT;
END GTB3;
|
|
|
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314845 is a reply to message #314829 ] |
Fri, 18 April 2008 00:42  |
Michel Cadot
Messages: 15226
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
This is unreadable.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel
|
|
|
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314859 is a reply to message #314829 ] |
Fri, 18 April 2008 01:21 |
aliyesami
Messages: 17
Registered: October 2005
Location: Florida |
Junior Member |
|
|
Thanks Michael for the advise , here is the formatted code.
------------- bulk collect ---------------------------------
CREATE OR REPLACE PROCEDURE gTb2
AS
v_Balance_Last NUMBER := 0;
v_Toll_Collected NUMBER := 0;
v_Toll_Suspended NUMBER := 0;
v_Balance_Last_No NUMBER := 0;
Counter NUMBER := 0;
nbr_Commits NUMBER := 0;
High_txn_Id NUMBER;
Low_txn_Id NUMBER;
Last_txn_Id NUMBER;
Err_num NUMBER;
Err_msg VARCHAR2(100);
TYPE comml_Cur_ty IS TABLE OF comml_Cur_Tab%ROWTYPE;
ty_comml_Cur_Tab COMML_CUR_TY := comml_Cur_ty();
CURSOR comml_Cur IS
SELECT a.InvtRansp_tRansp_tRansp_Id,
c.acct_acct_num,
(Nvl(l.Toll_Amt_Charged,0) / 100) Toll_Amt_Charged,
(Nvl(l.Toll_Amt_Collected,0) / 100) Toll_Amt_Collected,
(Nvl(l.Toll_Amt_Full,0) / 100) Toll_Amt_Full,
l.txn_Process_Date,
Nvl(l.comml_Update_Flag,'N'),
l.ext_Date_Time,
l.txn_Id,
(Nvl(c.comm_acct_Use_Paid_Amt,0) + Nvl(c.comm_acct_Total_Toll_Rebate,0) - Nvl(c.comm_acct_Total_Toll_Other,0) - Nvl(c.comm_acct_Total_Toll_Collected,0)) comm_Balance
FROM Pa_Lane_txn l,
Pa_acct_tRansp a,
Pa_Commercial c
WHERE l.txn_Id BETWEEN Low_txn_Id
AND High_txn_Id
AND l.txn_Id > 179929
AND l.tRansp_Id = a.InvtRansp_tRansp_tRansp_Id
AND c.acct_acct_num = a.acct_acct_num
AND tRans_Source IS NULL
AND (Nvl(l.Toll_Amt_Collected,0) / 100) <> 0
AND Nvl(l.comml_Update_Flag,'N') = 'N'
AND l.msg_Invalid = 1
AND c.acct_acct_num NOT IN (3793,
3797,
3655)
ORDER BY l.txn_Id;
BEGIN
SELECT MAX(txn_Id)
INTO High_txn_Id
FROM Pa_Lane_txn
WHERE txn_Process_Date BETWEEN To_date('02-mar-2008 18:25:12','dd-mon-yyyy hh24:mi:ss')
AND (Trunc(SYSDATE) - 1 / 86400);
---
SELECT Last_Updated_txn_Id
INTO Low_txn_Id
FROM Pa_Process
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
Last_txn_Id := Low_txn_Id;
UPDATE Pa_Process
SET No_Commits = 0,
Commit_Time = SYSDATE
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
OPEN comml_Cur;
FETCH comml_Cur BULK COLLECT INTO ty_comml_Cur_Tab;
CLOSE comml_Cur;
FOR i IN 1.. ty_comml_Cur_Tab.LAST LOOP -- << MAIN LOOP >>
SELECT (Nvl(comm_acct_Use_Paid_Amt,0) + Nvl(comm_acct_Total_Toll_Rebate,0) - Nvl(comm_acct_Total_Toll_Other,0) - (Nvl(comm_acct_Total_Toll_Collected,0) + Ty_comml_cur_tab(i).toll_amt_collected))
INTO v_Balance_Last
FROM Pa_Commercial
WHERE acct_acct_num = Ty_comml_cur_tab(i).acct_Acct_num;
IF v_Balance_Last >= 0
OR ty_comml_Cur_Tab(i).Toll_Amt_Collected < 0 -- <<IF-2>>
THEN
UPDATE Pa_Commercial
SET comm_acct_Total_Toll_Charged = Nvl(comm_acct_Total_Toll_Charged,0) + Ty_comml_cur_tab(i).toll_amt_charged,
comm_acct_Total_Toll_Collected = Nvl(comm_acct_Total_Toll_Collected,0) + Ty_comml_cur_tab(i).toll_amt_collected,
comm_acct_Total_Toll_Full = Nvl(comm_acct_Total_Toll_Full,0) + Ty_comml_cur_tab(i).toll_amt_full,
Total_In_Lane_Discounts = Nvl(Total_In_Lane_Discounts,0) + (Ty_comml_cur_tab(i).toll_amt_full - Ty_comml_cur_tab(i).toll_amt_charged)
WHERE acct_acct_num = Ty_comml_cur_tab(i).acct_Acct_num;
--- new 11/06
ELSE
IF (ty_comml_Cur_Tab(i).comm_Balance <= 0
AND ty_comml_Cur_Tab(i).Toll_Amt_Collected > 0) -- <<IF-3>>
THEN
v_Toll_Suspended := ty_comml_Cur_Tab(i).Toll_Amt_Collected;
ELSE
SELECT (Nvl(comm_acct_Use_Paid_Amt,0) + Nvl(comm_acct_Total_Toll_Rebate,0) - Nvl(comm_acct_Total_Toll_Other,0) - (Nvl(comm_acct_Total_Toll_Collected,0)))
INTO v_Balance_Last_No
FROM Pa_Commercial
WHERE acct_acct_num = Ty_comml_cur_tab(i).acct_Acct_num;
--
v_Toll_Collected := v_Balance_Last_No;
v_Toll_Suspended := ty_comml_Cur_Tab(i).Toll_Amt_Collected - v_Toll_Collected;
END IF; -- << END IF-3>>
--
UPDATE Pa_Commercial
SET comm_acct_Total_Toll_Charged = nvl(comm_acct_Total_Toll_Charged,0) + ty_comml_Cur_Tab(i).Toll_Amt_Charged,
comm_acct_Total_Toll_Collected = nvl(comm_acct_Total_Toll_Collected,0) + v_Toll_Collected,
WHERE acct_acct_num = ty_comml_Cur_Tab(i).acct_acct_num ;
--- Updates pa_lane_txn with the real collected amount
---
END IF; -- << END IF-2>>
Counter := Counter + 1;
nbr_Commits := nbr_Commits + 1;
Last_txn_Id := ty_comml_Cur_Tab(i).txn_Id;
UPDATE Pa_Lane_txn
SET comml_Update_Flag = 'Y'
WHERE txn_Id = Ty_comml_cur_tab(i).txn_id;
--
UPDATE Pa_Process
SET No_Commits = nbr_Commits,
Commit_Time = SYSDATE,
Last_Updated_txn_Id = Last_txn_Id
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
-- COMMIT;
--COMMIT;
END LOOP; -- << END MAIN LOOP >>
UPDATE Pa_Process
SET No_Commits = nbr_Commits,
Commit_Time = SYSDATE,
Last_Updated_txn_Id = High_txn_Id
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
--COMMIT;
END gTb2;
------------------ FOR CURSOR LOOP----------------------------
CREATE OR REPLACE PROCEDURE gTb3
AS
Counter NUMBER := 0;
nbr_Commits NUMBER := 0;
High_txn_Id NUMBER;
Low_txn_Id NUMBER;
Last_txn_Id NUMBER;
Err_num NUMBER;
Err_msg VARCHAR2(100);
--
v_Balance_Last NUMBER := 0;
-- new 11/06/2003
v_Toll_Collected NUMBER := 0;
v_Toll_Suspended NUMBER := 0;
v_Balance_Last_No NUMBER := 0;
--
CURSOR comml_Cur IS
SELECT a.InvtRansp_tRansp_tRansp_Id,
c.acct_acct_num,
(Nvl(l.Toll_Amt_Charged,0) / 100) Toll_Amt_Charged,
(Nvl(l.Toll_Amt_Collected,0) / 100) Toll_Amt_Collected,
(Nvl(l.Toll_Amt_Full,0) / 100) Toll_Amt_Full,
l.txn_Process_Date,
Nvl(l.comml_Update_Flag,'N'),
l.ext_Date_Time,
l.txn_Id,
(Nvl(c.comm_acct_Use_Paid_Amt,0) + Nvl(c.comm_acct_Total_Toll_Rebate,0) - Nvl(c.comm_acct_Total_Toll_Other,0) - Nvl(c.comm_acct_Total_Toll_Collected,0)) comm_Balance
FROM Pa_Lane_txn l,
Pa_acct_tRansp a,
Pa_Commercial c
WHERE l.txn_Id BETWEEN Low_txn_Id
AND High_txn_Id
AND l.txn_Id > 1799296540
AND l.tRansp_Id = a.InvtRansp_tRansp_tRansp_Id
AND c.acct_acct_num = a.acct_acct_num
AND tRans_Source IS NULL
AND (Nvl(l.Toll_Amt_Collected,0) / 100) <> 0
AND Nvl(l.comml_Update_Flag,'N') = 'N'
AND l.msg_Invalid = 1
ORDER BY l.txn_Id;
---
BEGIN
SELECT MAX(txn_Id)
INTO High_txn_Id
FROM Pa_Lane_txn
WHERE txn_Process_Date BETWEEN To_date('02-mar-2008 18:25:12','dd-mon-yyyy hh24:mi:ss')
AND (Trunc(SYSDATE) - 1 / 86400);
---
SELECT Last_Updated_txn_Id
INTO Low_txn_Id
FROM Pa_Process
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
Last_txn_Id := Low_txn_Id;
UPDATE Pa_Process
SET No_Commits = 0,
Commit_Time = SYSDATE
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
FOR comml_rec IN comml_Cur LOOP --- << BEGIN CURSOR LOOP >>
IF (comml_rec.acct_acct_num <> 3793
AND comml_rec.acct_acct_num <> 3797
AND comml_rec.acct_acct_num <> 3655) -- <<IF-1>>
THEN
SELECT (Nvl(comm_acct_Use_Paid_Amt,0) + Nvl(comm_acct_Total_Toll_Rebate,0) - Nvl(comm_acct_Total_Toll_Other,0) - (Nvl(comm_acct_Total_Toll_Collected,0) + comml_rec.Toll_Amt_Collected))
INTO v_Balance_Last
FROM Pa_Commercial
WHERE acct_acct_num = comml_rec.acct_acct_num;
IF v_Balance_Last >= 0
OR comml_rec.Toll_Amt_Collected < 0 -- <<IF-2>>
THEN
UPDATE Pa_Commercial
SET comm_acct_Total_Toll_Charged = Nvl(comm_acct_Total_Toll_Charged,0) + comml_rec.Toll_Amt_Charged,
comm_acct_Total_Toll_Collected = Nvl(comm_acct_Total_Toll_Collected,0) + comml_rec.Toll_Amt_Collected,
comm_acct_Total_Toll_Full = Nvl(comm_acct_Total_Toll_Full,0) + comml_rec.Toll_Amt_Full,
Total_In_Lane_Discounts = Nvl(Total_In_Lane_Discounts,0) + (comml_rec.Toll_Amt_Full - comml_rec.Toll_Amt_Charged)
WHERE acct_acct_num = comml_rec.acct_acct_num;
--- new 11/06
ELSE
IF (comml_rec.comm_Balance <= 0
AND comml_rec.Toll_Amt_Collected > 0) -- <<IF-3>>
THEN
v_Toll_Suspended := comml_rec.Toll_Amt_Collected;
ELSE
SELECT (Nvl(comm_acct_Use_Paid_Amt,0) + Nvl(comm_acct_Total_Toll_Rebate,0) - Nvl(comm_acct_Total_Toll_Other,0) - (Nvl(comm_acct_Total_Toll_Collected,0)))
INTO v_Balance_Last_No
FROM Pa_Commercial
WHERE acct_acct_num = comml_rec.acct_acct_num;
--
v_Toll_Collected := v_Balance_Last_No;
v_Toll_Suspended := comml_rec.Toll_Amt_Collected - v_Toll_Collected;
END IF; -- << END IF-3>>
--
UPDATE Pa_Commercial
SET comm_acct_Total_Toll_Charged = nvl(comm_acct_Total_Toll_Charged,0) + comml_rec.Toll_Amt_Charged,
comm_acct_Total_Toll_Collected = nvl(comm_acct_Total_Toll_Collected,0) + v_Toll_Collected,
WHERE acct_acct_num = comml_rec.acct_acct_num ;
---
END IF; -- << END IF-2>>
Counter := Counter + 1;
nbr_Commits := nbr_Commits + 1;
Last_txn_Id := comml_rec.txn_Id;
UPDATE Pa_Lane_txn
SET comml_Update_Flag = 'Y'
WHERE txn_Id = comml_rec.txn_Id;
--
UPDATE Pa_Process
SET No_Commits = nbr_Commits,
Commit_Time = SYSDATE,
Last_Updated_txn_Id = Last_txn_Id
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
-- COMMIT;
Counter := 0;
END IF; --<< END IF-1>>
--COMMIT;
END LOOP; -- << END CURSOR LOOP >>
UPDATE Pa_Process
SET No_Commits = nbr_Commits,
Commit_Time = SYSDATE,
Last_Updated_txn_Id = High_txn_Id
WHERE Process_Name = 'COMML_BALANCE_UPDATE';
--COMMIT;
END gTb3;
|
|
|
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314861 is a reply to message #314859 ] |
Fri, 18 April 2008 01:23 |
aliyesami
Messages: 17
Registered: October 2005
Location: Florida |
Junior Member |
|
|
|
It was showing the code formatted in SQL FORMATTER but when I pasted it in the forum its again all left aligned ..whats going on here? did i miss some instruction ?
|
|
| |
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314870 is a reply to message #314829 ] |
Fri, 18 April 2008 01:38 |
aliyesami
Messages: 17
Registered: October 2005
Location: Florida |
Junior Member |
|
|
put code tag around every statement? there are hundred of statements there ...
remove spaces? so what does SQLformatter do if I have to adjust each line manually?
|
|
| |
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314873 is a reply to message #314870 ] |
Fri, 18 April 2008 01:42 |
Michel Cadot
Messages: 15226
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
This is explained in the link I posted.
Put code tags around the whole code. [code] before the first line of code [/code] after the last line of code.
Easy, doesn't it?
And check it i using the Preview BEFORE clicking on Submit.
Regards
Michel
|
|
|
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #315023 is a reply to message #314829 ] |
Fri, 18 April 2008 12:32 |
aliyesami
Messages: 17
Registered: October 2005
Location: Florida |
Junior Member |
|
|
Ok lets try again. I did preview the previous time and it showed formatted output. this time I am putting the around the code.
--------------------------------------------------------------------------------------------------------
-- exercise using REFERENCE CURSOR n BULK COLLECT
--------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE GTB2
AS
comml_ref_cur SYS_REFCURSOR;
V_BALANCE_LAST Number := 0;
V_TOLL_COLLECTED Number := 0;
V_TOLL_SUSPENDED Number := 0;
V_BALANCE_LAST_NO Number := 0;
counter NUMBER := 0;
nbr_commits NUMBER := 0;
high_txn_id number ;
low_txn_id number ;
last_txn_id number ;
ERR_NUM NUMBER ;
ERR_MSG VARCHAR2 (100) ;
TYPE comml_cur_TY IS TABLE OF comml_cur_tab%ROWTYPE;
TY_comml_cur_TAB comml_cur_TY := comml_cur_TY();
CURSOR COMML_CUR is
select a.invtransp_transp_transp_id ,
c.acct_Acct_num,
(nvl(l.toll_amt_charged,0)/100) toll_amt_charged,
(nvl(l.toll_amt_collected,0)/100) toll_amt_collected,
(nvl(l.toll_amt_full,0)/100) toll_amt_full,
l.TXN_PROCESS_DATE,
nvl(l.COMMl_UPDATE_FLAG,'N') ,
l.ext_date_time,
l.txn_id ,
( nvl(c.COMM_ACCT_USE_PAID_AMT,0)
+ nvl(c.COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_OTHER,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_COLLECTED,0)) COMM_BALANCE
from pa_lane_txn l,
PA_ACCT_TRANSP a,
pa_commercial c
where l.txn_id between 232323 and 545354
and l.txn_id > 1799296540
and l.transp_id = a.invtransp_transp_transp_id
AND c.acct_Acct_num = a.acct_Acct_num
and trans_source is NULL
and (nvl(l.toll_amt_collected,0)/100) <> 0
AND nvl(l.COMMl_UPDATE_FLAG,'N') = 'N'
and l.MSG_INVALID = 1
order by l.txn_id;
begin
open comml_cur;
fetch comml_cur bulk collect into TY_comml_cur_TAB;
close comml_cur;
for i in 1..TY_comml_cur_TAB.last loop -- << MAIN LOOP >>
select (nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + TY_comml_cur_TAB(i).toll_amt_collected ))
Into V_BALANCE_LAST
from pa_commercial
where ACCT_ACCT_NUM = TY_comml_cur_TAB(i).acct_Acct_num ;
IF V_BALANCE_LAST >= 0 or TY_comml_cur_TAB(i).toll_amt_collected < 0 -- <<IF-2>>
THEN
update pa_commercial
set COMM_ACCT_TOTAL_TOLL_CHARGED = nvl(COMM_ACCT_TOTAL_TOLL_CHARGED,0) + TY_comml_cur_TAB(i).toll_amt_charged,
COMM_ACCT_TOTAL_TOLL_COLLECTED = nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + TY_comml_cur_TAB(i).toll_amt_collected,
COMM_ACCT_TOTAL_TOLL_FULL = nvl(COMM_ACCT_TOTAL_TOLL_FULL, 0) + TY_comml_cur_TAB(i).toll_amt_full,
TOTAL_IN_LANE_DISCOUNTS = nvl(TOTAL_IN_LANE_DISCOUNTS,0) + (TY_comml_cur_TAB(i).toll_amt_full - TY_comml_cur_TAB(i).toll_amt_charged)
where ACCT_ACCT_NUM = TY_comml_cur_TAB(i).acct_Acct_num ;
ELSE
IF (TY_comml_cur_TAB(i).COMM_BALANCE <= 0 and TY_comml_cur_TAB(i).toll_amt_collected > 0) -- <<IF-3>>
THEN
V_TOLL_COLLECTED := 0 ;
V_TOLL_SUSPENDED := TY_comml_cur_TAB(i).toll_amt_collected ;
ELSE
select
(nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) ))
Into V_BALANCE_LAST_NO
from pa_commercial
where ACCT_ACCT_NUM = TY_comml_cur_TAB(i).acct_Acct_num ;
V_TOLL_COLLECTED := V_BALANCE_LAST_NO ;
V_TOLL_SUSPENDED := TY_comml_cur_TAB(i).toll_amt_collected - V_TOLL_COLLECTED ;
END IF ; -- << END IF-3>>
END IF;
end loop; -- << END MAIN LOOP >>
end GTB2;
----------------------------------------------------------------------------------------------------------------------------
FOR CURSOR LOOP
----------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE GTB3
AS
counter NUMBER := 0;
nbr_commits NUMBER :=0;
high_txn_id number ;
low_txn_id number ;
last_txn_id number ;
ERR_NUM NUMBER ;
ERR_MSG VARCHAR2 (100) ;
--
V_BALANCE_LAST Number := 0;
-- new 11/06/2003
V_TOLL_COLLECTED Number := 0;
V_TOLL_SUSPENDED Number := 0;
V_BALANCE_LAST_NO Number := 0;
--
CURSOR COMML_CUR is
select a.invtransp_transp_transp_id ,
c.acct_Acct_num,
(nvl(l.toll_amt_charged,0)/100) toll_amt_charged,
(nvl(l.toll_amt_collected,0)/100) toll_amt_collected,
(nvl(l.toll_amt_full,0)/100) toll_amt_full,
l.TXN_PROCESS_DATE,
nvl(l.COMMl_UPDATE_FLAG,'N') ,
l.ext_date_time,
l.txn_id ,
(nvl(c.COMM_ACCT_USE_PAID_AMT,0)
+ nvl(c.COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_OTHER,0)
- nvl(C.COMM_ACCT_TOTAL_TOLL_COLLECTED,0)) COMM_BALANCE
from pa_lane_txn l,
PA_ACCT_TRANSP a,
pa_commercial c
where l.txn_id between 232323 and 545354
and l.txn_id > 1799296540
and l.transp_id = a.invtransp_transp_transp_id
AND c.acct_Acct_num = a.acct_Acct_num
and trans_source is NULL
and (nvl(l.toll_amt_collected,0)/100) <> 0
AND nvl(l.COMMl_UPDATE_FLAG,'N') = 'N'
and l.MSG_INVALID = 1
order by l.txn_id;
BEGIN
FOR COMML_REC in COMML_CUR
LOOP
select (nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + comml_rec.toll_amt_collected ))
Into V_BALANCE_LAST
from pa_commercial
where ACCT_ACCT_NUM = comml_rec.acct_Acct_num ;
IF V_BALANCE_LAST >= 0 or comml_rec.toll_amt_collected < 0 -- <<IF-2>>
THEN
update pa_commercial
set COMM_ACCT_TOTAL_TOLL_CHARGED = nvl(COMM_ACCT_TOTAL_TOLL_CHARGED,0) + comml_rec.toll_amt_charged,
COMM_ACCT_TOTAL_TOLL_COLLECTED = nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) + comml_rec.toll_amt_collected,
COMM_ACCT_TOTAL_TOLL_FULL = nvl(COMM_ACCT_TOTAL_TOLL_FULL, 0) + comml_rec.toll_amt_full,
TOTAL_IN_LANE_DISCOUNTS = nvl(TOTAL_IN_LANE_DISCOUNTS,0) + (comml_rec.toll_amt_full - comml_rec.toll_amt_charged)
where ACCT_ACCT_NUM = comml_rec.acct_Acct_num ;
ELSE
IF (comml_rec.COMM_BALANCE <= 0 and comml_rec.toll_amt_collected > 0) -- <<IF-3>>
THEN
V_TOLL_COLLECTED := 0 ;
V_TOLL_SUSPENDED := comml_rec.toll_amt_collected ;
ELSE
select
(nvl(COMM_ACCT_USE_PAID_AMT,0) + nvl(COMM_ACCT_TOTAL_TOLL_REBATE,0)
- nvl(COMM_ACCT_TOTAL_TOLL_OTHER,0) - (nvl(COMM_ACCT_TOTAL_TOLL_COLLECTED,0) ))
Into V_BALANCE_LAST_NO
from pa_commercial
where ACCT_ACCT_NUM = comml_rec.acct_Acct_num ;
V_TOLL_COLLECTED := V_BALANCE_LAST_NO ;
V_TOLL_SUSPENDED := comml_rec.toll_amt_collected - V_TOLL_COLLECTED ;
END IF ; -- << END IF-3>>
end loop;
END GTB3;
|
|
|
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #315037 is a reply to message #315023 ] |
Fri, 18 April 2008 13:28 |
ThomasG
Messages: 620
Registered: April 2005
Location: Heilbronn, Germany |
Senior Member |
|
|
Is the runtime difference really 8.90 Minutes to 8.05 Seconds?
Then I assume something strange is going on, and you should trace both sessions to see what is rally happening.
If that was a typo and both time were minutes or seconds, then that is about the behaviour I have seen. When there is much processing (and additional selecting) going on inside the loop anyway a forall loop doesn't speed things up much.
|
|
|
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #315060 is a reply to message #315037 ] |
Fri, 18 April 2008 14:39 |
aliyesami
Messages: 17
Registered: October 2005
Location: Florida |
Junior Member |
|
|
sorry it was a typo .. both times are in mins .
shouldnt the BULK COLLECT be much faster than FOR CURSOR LOOP ? why am I getting lower execution time for BULK COLLECT?
|
|
|
| Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #315064 is a reply to message #314829 ] |
Fri, 18 April 2008 15:23 |
coleing
Messages: 91
Registered: February 2008 |
Member |
|
|
Ok,
The only thing you are speeding up is the outside reads. You need to speed up the writes also. probably 8 mins is spent on the update, and half a min on the reads.
Look up array processing and bulk updates also. (FORALL syntax)
Additionally, try to get the 2 nested reads into the outer cursor (as inline views).
This means you have 1 read cursor, some array processing, and a FORALL update loop.
[Updated on: Fri, 18 April 2008 15:23]
|
|
|
Goto Forum:
Current Time: Fri May 16 11:29:50 CDT 2008
Total time taken to generate the page: 0.01149 seconds |
]
Members
Search
Help
Register
Login
Home
of how your message would look like.