| Update is very very slow, need help! [message #192578] |
Tue, 12 September 2006 21:50  |
quynhndbk
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
Hi.
I have 2 tables with structure as below:
> desc NSCHCS_FROM_TG
ID NUMBER(20)
DB_LKHOAN_MA VARCHAR2(5)
DB_CHUONG_MA VARCHAR2(3)
DB_CHUONG_CAP VARCHAR2(1)
DB_MCHI_MA VARCHAR2(6)
DB_DVSDNS_MA VARCHAR2(15)
DB_DBHC_MA VARCHAR2(7)
NAM VARCHAR2(4)
SO_CH NUMBER(16)
DB_NGUON_MA VARCHAR2(20)
THANG VARCHAR2(2)
NAM_ND VARCHAR2(4)
ID_LKHOAN NUMBER(20)
ID_CHUONG NUMBER(20)
ID_MCHI NUMBER(20)
ID_DVSDNS NUMBER(20)
ID_DBHC NUMBER(20)
MA_CHUONG_NV VARCHAR2(10)
>desc DB_DVSDNS_NAM
ID NOT NULL NUMBER
MA_NV NOT NULL VARCHAR2(20)
TEN_NV VARCHAR2(300)
TU_NGAY NOT NULL DATE
DEN_NGAY DATE
TEN_GD VARCHAR2(200)
CAP_NS VARCHAR2(1)
CAP_DT VARCHAR2(1)
CAP_CHUONG VARCHAR2(1)
MA_CHUONG VARCHAR2(10)
LOAI_HINH VARCHAR2(100)
NGAY_HDONG DATE
TEL VARCHAR2(50)
FAX VARCHAR2(50)
DIA_CHI VARCHAR2(200)
MA_TK VARCHAR2(100)
NOI_MO VARCHAR2(200)
GHI_CHU VARCHAR2(2000)
TINH_TRANG VARCHAR2(200)
MA_CHA VARCHAR2(200)
With each row in the NSCHCS_FROM_TG table i must compare the value of the DB_DVSDNS_MA field with the value of the MA_NV field in the DB_DVSDNS_NAM table. If the value of the DB_DVSDNS_MA equals the value of the MA_NV, i get the value of the ID field in DB_DVSDNS_NAM table to update back ID_DVSDNS field in the NSCHCS_FROM_TG table. The former table has 3 millions rows and the latter table has 100.000 rows.
Well, my problem is that the speed of this update is very very slow.
Please help me to improve the speed. Thanks a lot.
|
|
|
|
|
|
| Re: Update is very very slow, need help! [message #192638 is a reply to message #192578] |
Wed, 13 September 2006 02:31  |
quynhndbk
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
My code shown as below:
cursor c_id_dvsdns is
select /*+ PARALLEL(nschcs_from_tg1,4) */ distinct(db_dvsdns_ma) db_dvsdns_ma from nschcs_from_tg where thang = '01' and nam = '2005';
r_id_dvsdns c_id_dvsdns%rowtype;
Cursor C_dvsdns is
Select /*+ PARALLEL(db_dvsdns_nam,4) */ id, ma_nv, tu_ngay, den_ngay from db_dvsdns_nam
where ma_nv = r_id_dvsdns.db_dvsdns_ma and to_date('2005','yyyy') between tu_ngay and den_ngay;
C_dvsdns1 C_dvsdns%ROWTYPE;
open c_id_dvsdns;
loop
fetch c_id_dvsdns into r_id_dvsdns;
exit when c_id_dvsdns%notfound;
open c_dvsdns;
loop
fetch c_dvsdns into c_dvsdns1;
exit when c_dvsdns%notfound;
cur_id_dvsdns := c_dvsdns1.id;
end loop;
close c_dvsdns;
--Tien hanh update
update /*+ PARALLEL(nschcs_from_tg1,4) */ nschcs_from_tg set id_dvsdns = cur_id_dvsdns where db_dvsdns_ma = r_id_dvsdns.db_dvsdns_ma and thang = '01' and nam = '2005';
commit;
end loop;
close c_id_dvsdns;
|
|
|
|
| Re: Update is very very slow, need help! [message #192657 is a reply to message #192638] |
Wed, 13 September 2006 03:08 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You have a few basic problems:
- Cursor within a cursor is very very slow. Never do this.
- You are committing for every row in the outer cursor. This is too frequent. It affects performance and exposes you to a higher risk of ORA-01555 snapshot too old error.
- You are not using bulk processing.
There is also a fundamental design problem with your algorithm. For any one row in the outer cursor, you are looping through an inner cursor that may return 2 or more rows (I suppose). You then use this information to update the first table. It seems to me that you will be updating the same row many times - possibly with different values.
The best way to do this is with an UPDATEABLE JOIN VIEW (find it in the doco). It works like this:
UPDATE (
SELECT old.col1, new.col1
FROM table1 old, table2 new
WHERE old.col2 = new.col2
AND ....
)
SET old.col1 = new.col1
The downside of this method is that it will only work if you have a Unique/PK index on the table2 join key (eg. table2.col2 above). There is an undocumented work-around for this, but in your case it is inappropriate because it will yield non-deterministic results.
Another way is to use PL/SQL bulk binds. You use a cursor (but not a cursor withing a cursor) to build up a Nested Table of Table1 rows that you want to update, and then use the FORALL PL/SQL statement to perform the update. See the PL/SQL Users Reference for instructions on doing this.
Ross Leishman
|
|
|
|
|
|
| Re: Update is very very slow, need help! [message #192941 is a reply to message #192657] |
Thu, 14 September 2006 05:28 |
quynhndbk
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
I have repaired my code according to your suggestion. I also created index in the NSCHCS_FROM_TG. But i see that it still is very very slow. With 3 millions records in NSCHCS_FROM_TG table and 100.000 records in DB_DVSDNS_NAM table, it takes about 4 days.
Please give me another way for improving speed.
Thank you.
|
|
|
|
|
|
| Re: Update is very very slow, need help! [message #193140 is a reply to message #193130] |
Fri, 15 September 2006 00:47 |
quynhndbk
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
My new code shown as below:
cursor c_id_dvsdns is
select /*+ PARALLEL(nschcs_from_tg,4) */ distinct(db_dvsdns_ma) db_dvsdns_ma from nschcs_from_tg where thang = '01' and nam = '2005';
r_id_dvsdns c_id_dvsdns%rowtype;
open c_id_dvsdns;
loop
i:=i+1;
if mod(i,10)=0 then
forms_ddl('commit');
end if;
fetch c_id_dvsdns into r_id_dvsdns;
exit when c_id_dvsdns%notfound;
--Lay ID
select NVL(max(id),0) into cur_id_dvsdns from db_dvsdns_nam where ma_nv = r_id_dvsdns.db_dvsdns_ma and to_date('2005','yyyy') between tu_ngay and den_ngay;
--Tien hanh update
update /*+ PARALLEL(nschcs_from_tg,4) */ nschcs_from_tg set id_dvsdns = cur_id_dvsdns where db_dvsdns_ma = r_id_dvsdns.db_dvsdns_ma and thang = '01' and nam = '2005';
end loop;
close c_id_dvsdns;
|
|
|
|
| Re: Update is very very slow, need help! [message #193374 is a reply to message #193140] |
Fri, 15 September 2006 22:36 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry, I wasn't clear. Cursor within a Cursor also includes SELECT INTO within a cursor (SELECT INTO is also known as an "Implicit Cursor").
To get this running faster, you have to combine the SELECT on db_dvsdns_nam into the main cursor using a table join.
Once you have done that, you can get further improvement as follows:
- A single commit at the end.
- Perform BULK COLLECT fetches from the cursor
- Load the changes into a Nested Table and perform bulk UPDATES using a FORALL loop on the Nested Table.
The Golden Rule is:
- NO SQL (ie. SELECT, INSERT, UPDATE, or DELETE, OPEN CURSOR, or EXECUTE IMMEDIATE) inside a FOR LOOP if it can possibly be avoided.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
