Home » Developer & Programmer » Forms » How can i lessen the time of millions of records to update
| How can i lessen the time of millions of records to update [message #83532] |
Wed, 22 October 2003 18:03  |
Susane
Messages: 27
Registered: September 2002
|
Junior Member |
|
|
hi to all..
Can anyone help in my problem in my processing.
My problem is too much time consumed in update / insert
processing in forms. I have more than 1 million records to update in table i put it in cursor and sequentially read and update it in table. It tooks 1 day to complete and sometimes more than 1 day because of error ORA-01555 "SNAPSHOT ERROR".
My processing is too simple the problem is millions of records are being proces.. THis is my program, please help i need to run this everyday.
Any suggestions will be highly appreciated.
thanks
shane
PROCEDURE pcs_rqqt_data IS
CURSOR pcfile_cursor IS
SELECT part_no PANO, prodn_date, need
FROM pcfwork
-- where part_no <= 'C-VO 8X9 B L=270'
order by part_no;
CURSOR curctr IS SELECT count(part_no)
FROM pcfwork
-- where part_no <= 'C-VO 8X9 B L=270'
order by part_no;
pcfile_w_rec pcfile_cursor%ROWTYPE;
varcolumn VARCHAR2(30);
varpartnoctr NUMBER;
vardupctr NUMBER;
ws_rqqt NUMBER;
vardupctr1 NUMBER;
varinsertctr NUMBER := 0;
varcommdescode tariffs.comm_desc_code%TYPE;
Numctr NUMBER := 0;
Commitctr NUMBER := 0;
vc2_part_no PARTS.PART_NO%TYPE;
c NUMBER := 0; -- Counter of total records to be processed
y NUMBER := 0; -- Counter of Percent Process
x NUMBER := 0; -- Counter of Records read
w NUMBER := 0; -- WIDTH of Progress Bar
BEGIN
c := 0;
x := 0;
:cg$ctrl.progress_bar := NULL;
OPEN curctr;
FETCH curctr INTO c;
SHOW_VIEW('PROC_MESS');
SET_APPLICATION_PROPERTY(CURSOR_STYLE,'BUSY');
SET_ITEM_PROPERTY('CG$CTRL.PROGRESS_BAR', VISIBLE, PROPERTY_TRUE);
SET_ITEM_PROPERTY('CG$CTRL.PERCENT', VISIBLE, PROPERTY_TRUE);
SET_ITEM_PROPERTY('CG$CTRL.ITEM_CTR', VISIBLE, PROPERTY_TRUE);
SET_ITEM_PROPERTY('CG$CTRL.time_consumed', VISIBLE, PROPERTY_TRUE);
synchronize;
OPEN pcfile_cursor;
FETCH pcfile_cursor INTO pcfile_w_rec;
LOOP EXIT WHEN pcfile_cursor%NOTFOUND;
x := x + 1;
y := (x/c) * 100;
:CG$CTRL.ITEM_CTR := x || '/' || c || ' ==> ' || pcfile_w_rec.pano;
-- MESSAGE('Record Process: ' || x || '/' || c || ' - ' || pcfile_w_rec.pano,NO_ACKNOWLEDGE);
IF y < 100 THEN
w := ROUND(( 3.25 / 100 ) * trunc (y), 2);
SET_ITEM_PROPERTY('CG$CTRL.PROGRESS_BAR',WIDTH, w );
SYNCHRONIZE;
END IF;
IF trunc(y) = 100 THEN
:cg$ctrl.progress_bar := 'Completed';
END IF;
:cg$ctrl.percent := trunc(y) || '%';
SYNCHRONIZE;
:global.v_elapsed_time := trunc(SYSDATE) + (SYSDATE - TO_DATE(:global.start_time,'RRRRMMDDHH24MISS'));
:cg$ctrl.time_consumed := :global.v_elapsed_time;
BEGIN
SELECT part_no
INTO vc2_part_no
FROM parts
WHERE part_no = pcfile_w_rec.pano
OR art_part_no = pcfile_w_rec.pano
OR supp_part_no = pcfile_w_rec.pano
OR cust_part_no = pcfile_w_rec.pano
OR cae_part_no = pcfile_w_rec.pano;
IF SQL%FOUND THEN
UPDATE PCS_HEADER
SET NEED = pcfile_w_rec.need
WHERE part_no = VC2_PART_NO
and prodn_date = pcfile_w_rec.prodn_date;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO pcs_header
VALUES (VC2_PART_NO, pcfile_w_rec.prodn_date, pcfile_w_rec.need,0,0,0,0,0,0,0,0,0,0,NULL);
END IF;
varinsertctr := varinsertctr + 1;
COMMIT;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MESSAGE('INVALID PART NUMBER IN PARTS ===> ' || pcfile_w_rec.pano, NO_ACKNOWLEDGE);
WHEN OTHERS THEN
MESSAGE('OTHER INVALID PART NUMBER IN PARTS ===> ' || pcfile_w_rec.pano, NO_ACKNOWLEDGE);
END;
FETCH pcfile_cursor INTO pcfile_w_rec;
END LOOP;
CLOSE pcfile_cursor;
COMMIT;
SHOW_VIEW('PROC_ENDS');
SET_APPLICATION_PROPERTY(CURSOR_STYLE,'DEFAULT');
MESSAGE('Successful Downloading of pcfile DATA- Total Record: ' || NVL(varinsertctr,0));
END;
|
|
|
|
| Re: How can i lessen the time of millions of records to update [message #83553 is a reply to message #83532] |
Sun, 26 October 2003 22:17  |
gary
Messages: 35
Registered: January 2001
|
Member |
|
|
I'd get rid of all that progress meter stuff, and do a simple :
INSERT INTO PCS_HEADER (.....)
SELECT p.part_no, w.prodn_date,
w.need,0,0,0,0,0,0,0,0,0,0,NULL
FROM pcfwork w, parts p
WHERE (p.part_no = w.part_no
OR p.art_part_no = w.part_no
OR p.supp_part_no = w.part_no
OR p.cust_part_no = w.part_no
OR p.cae_part_no = w.part_no)
and not exists
(select 1 from pcs_header h
where h.part_no = p.part_no
and h.prodn_date = w.prodn_date);
UPDATE PCS_HEADER h
set need =
(SELECT w.need
FROM pcfwork w, parts p
WHERE (p.part_no = w.part_no
OR p.art_part_no = w.part_no
OR p.supp_part_no = w.part_no
OR p.cust_part_no = w.part_no
OR p.cae_part_no = w.part_no)
and h.part_no = p.part_no)
WHERE exists
(SELECT w.need
FROM pcfwork w, parts p
WHERE (p.part_no = w.part_no
OR p.art_part_no = w.part_no
OR p.supp_part_no = w.part_no
OR p.cust_part_no = w.part_no
OR p.cae_part_no = w.part_no)
and h.part_no = p.part_no)
COMMIT;
If you've got a 9i database, I'd use a MERGE in a database procedure as it would be even quicker.
|
|
|
|
| Re: How can i lessen the time of millions of records to update [message #83555 is a reply to message #83553] |
Mon, 27 October 2003 05:44 |
gary
Messages: 35
Registered: January 2001
|
Member |
|
|
hi! thanks a lot for giving me time i will try your suggestions i'll give you feed back if it is work
we are using oracle8 only some experts suggested me to used "MERGED" command same as you. Gary thanks i will try first your suggestion.
thanks and wait for my feed back... good day..
shane
|
|
|
|
| Re: How can i lessen the time of millions of records to update [message #83556 is a reply to message #83553] |
Mon, 27 October 2003 05:46 |
Shane
Messages: 27
Registered: December 1999
|
Junior Member |
|
|
hi gary! thanks a lot for giving me time i will try your suggestions i'll give you feed back if it is work
we are using oracle8 only some experts suggested me to used "MERGED" command same as you. Gary thanks i will try first your suggestion.
thanks and wait for my feed back... good day..
shane
|
|
|
|
| Re: update How can i lessen the time of millions of records to update [message #83559 is a reply to message #83553] |
Mon, 27 October 2003 10:47 |
Shane
Messages: 27
Registered: December 1999
|
Junior Member |
|
|
hi gary!
i have still problem can u help me finding out how can i check the problem in update process, please see error below. Insert statement was successfully done.
I already check the subquery that has duplicate records. I already fix it. But still the error occurs.
Please give me some idea for this.
thanks and your suggestions are highly appreciated..
shane
SQLWKS> INSERT INTO PCS_HEADER
2> SELECT p.part_no, w.prodn_date,w.need,0,0,0,0,0,0,0,0,0,0,NULL
3> FROM pcfwork w, parts p
4> WHERE (p.part_no = w.part_no
5> OR p.art_part_no = w.part_no
6> OR p.supp_part_no = w.part_no
7> OR p.cust_part_no = w.part_no
8> OR p.cae_part_no = w.part_no)
9> and not exists
10> (select 1 from pcs_header h
11> where h.part_no = p.part_no
12> and h.prodn_date = w.prodn_date);
1731 rows processed.
Parse 0.45 (Elapsed) 0.00 (CPU)
Execute/Fetch 358.13 (Elapsed) 0.00 (CPU)
Total 358.58 0.00
IT WAS FASTER ALMOST 6 mins processing. ( VERY GOOD RESULT)
SQLWKS>
SQLWKS> UPDATE PCS_HEADER h
2> set need = (SELECT w.need
3> FROM pcfwork w, parts p
4> WHERE (p.part_no = w.part_no
5> OR p.art_part_no = w.part_no
6> OR p.supp_part_no = w.part_no
7> OR p.cust_part_no = w.part_no
8> OR p.cae_part_no = w.part_no)
9> and h.part_no = p.part_no
10> and h.prodn_date = w.prodn_date)
11> WHERE exists
12> (SELECT w.need
13> FROM pcfwork w, parts p
14> WHERE (p.part_no = w.part_no
15> OR p.art_part_no = w.part_no
16> OR p.supp_part_no = w.part_no
17> OR p.cust_part_no = w.part_no
18> OR p.cae_part_no = w.part_no)
19> and h.part_no = p.part_no
20> and h.prodn_date = w.prodn_date)
21>
ORA-01427: single-row subquery returns more than one row
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 03:28:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
