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 Go to next message
aliyesami
Messages: 28
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
aliyesami
Messages: 28
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 Go to previous messageGo to next message
aliyesami
Messages: 28
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 #314864 is a reply to message #314861] Fri, 18 April 2008 01:29 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
aliyesami wrote on Fri, 18 April 2008 08:23
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 ?


Put code-tags around it

[ CODE ]
SELECT sysdate from dual ;
[ /CODE ]

(remove the spaces off course)
Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314870 is a reply to message #314829] Fri, 18 April 2008 01:38 Go to previous messageGo to next message
aliyesami
Messages: 28
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 #314872 is a reply to message #314870] Fri, 18 April 2008 01:41 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
aliyesami wrote on Fri, 18 April 2008 08:38
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?



Not around every statement off course Confused

Just one set of code-tags will do.

And, maybe you didn't notice, but there's a Preview Message button. Pushing that one will give you a ... preview Shocked of how your message would look like.
If it doesn't look right, you can still change it.

Why don't you just try a bit?
Re: BULK COLLECT SLOWER THAN FOR CURSOR LOOP [message #314873 is a reply to message #314870] Fri, 18 April 2008 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
aliyesami
Messages: 28
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
aliyesami
Messages: 28
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 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior 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]

Report message to a moderator

Previous Topic: procedure - subset of string ?
Next Topic: Regular SQL Syntax and ANSI Syntax
Goto Forum:
  


Current Time: Sat Dec 03 06:20:11 CST 2016

Total time taken to generate the page: 0.17021 seconds