Home » SQL & PL/SQL » SQL & PL/SQL » slow store procedure (merged 4) (Oracle 11g - sql developer)
slow store procedure (merged 4) [message #425497] Fri, 09 October 2009 06:00 Go to next message
kuddel
Messages: 5
Registered: October 2009
Junior Member
Hello,

i have a problem with my store procedure.
She need for 10000 recordsets 1,5 hour, this is to slow.

Can anyone help me?

Please see below in the store Procedure.

Thanks
Andy
Re: slow store procedure [message #425500 is a reply to message #425497] Fri, 09 October 2009 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to upload files.
Post it inline.
Before 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 and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: slow store procedure [message #425503 is a reply to message #425497] Fri, 09 October 2009 06:06 Go to previous messageGo to next message
kuddel
Messages: 5
Registered: October 2009
Junior Member
Sorry here the sql:

set serveroutput on;

declare

ID number;
I number(7);
GS number(15);

cursor c1 is select field_id from table1 where field1 is null and rownum < 10002;

begin
I :=1;
GS :=1;
DBMS_OUTPUT.enable;
DBMS_OUTPUT.PUT_LINE( ' start = ' || SYSTIMESTAMP );
for c1_rec in c1 loop

ID := c1_rec.field_ID;
delete from table2 where field_id2 = ID;
update table1 set field1='X' where field_id=ID;
I :=I+1;
if I > 1000 then
commit;
I :=1;
end if;
GS :=GS+1;
end loop;
commit;
dbms_output.put_line( ' End = ' || SYSTIMESTAMP );
end;
/
Re: slow store procedure [message #425504 is a reply to message #425503] Fri, 09 October 2009 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 09 October 2009 13:02
...Before 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 and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Fri, 09 October 2009 06:13]

Report message to a moderator

Re: slow store procedure (merged 4) [message #425506 is a reply to message #425497] Fri, 09 October 2009 06:16 Go to previous messageGo to next message
kuddel
Messages: 5
Registered: October 2009
Junior Member
sorry here again

set serveroutput on;

declare

ID number;
I number(7);
GS number(15);
		
cursor c1 is select field_id from table1 where field1 is null and rownum < 10002;

begin
	I :=1;
	GS :=1;
  DBMS_OUTPUT.enable; 
  DBMS_OUTPUT.PUT_LINE( ' start = ' || SYSTIMESTAMP );
	for c1_rec in c1 loop

		ID := c1_rec.field_ID;
		delete from table2 where field_id2 = ID;
		update table1 set field1='X' where field_id=ID;
		I :=I+1;		
		if I > 1000 then
			commit;
			I :=1;
		end if;		
		GS :=GS+1;
	end loop; 
	commit;
  dbms_output.put_line( ' End = ' || SYSTIMESTAMP );
end;
/
Re: slow store procedure (merged 4) [message #425508 is a reply to message #425506] Fri, 09 October 2009 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing weird it is long.
Do it directly:
lock table1 in exclusive mode;
delete from table2 where field_id2 in (select field_id from table1)
update table1 set field1='X';
commit;

Regards
Michel

Re: slow store procedure (merged 4) [message #425510 is a reply to message #425497] Fri, 09 October 2009 06:27 Go to previous messageGo to next message
kuddel
Messages: 5
Registered: October 2009
Junior Member
hmm

I have 9 Million records in table1 and over 80 millions in table2, I think it will be to heavy for the DB, when I do it directly or?
Re: slow store procedure (merged 4) [message #425511 is a reply to message #425510] Fri, 09 October 2009 06:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It will be quicker, to do it directly, and will update the same number of records, reading in less data in the process.
Re: slow store procedure (merged 4) [message #425515 is a reply to message #425497] Fri, 09 October 2009 06:47 Go to previous messageGo to next message
kuddel
Messages: 5
Registered: October 2009
Junior Member
ok do you mean it so?

set serveroutput on;

declare

ID number;
I number(7);
GS number(15);
		
cursor c1 is select field_id from table1 where field1 is null and rownum < 10002;

begin
    lock table1 in exclusive mode;
    delete from table2 where field_id2 in c1
    update table1 set field1='X' where field_id in c1;
    commit;
end;
/
Re: slow store procedure (merged 4) [message #425521 is a reply to message #425515] Fri, 09 October 2009 07:00 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I mean EXACTLY what I posted.

By the way, if you REALLy want to use ROWNUM (I don't see why) you MUST use an ORDER BY clause to be sure to get the same result in both statements.

Regards
Michel
Previous Topic: Update/insert
Next Topic: Converting TIMESTAMP from one timezone to other
Goto Forum:
  


Current Time: Thu Sep 29 20:59:02 CDT 2016

Total time taken to generate the page: 0.08038 seconds