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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: SQL*PLUS transaction exited what happens
Next Topic: ERP training information
Goto Forum:
  


Current Time: Tue Aug 09 17:08:53 CDT 2022