Home » SQL & PL/SQL » SQL & PL/SQL » Performance Issue (For loop) (Oracle 10g,Win XP)
Performance Issue (For loop) [message #383539] Thu, 29 January 2009 02:09 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

How to make faster my FOR LOOP

for rec_name in cur_name loop --(here their is be millions of records)
   QRY:=rec_nodeboundary.ORD;
   kml:=UTL_FILE.FOPEN('DIR','t.txt','A',32767);
   UTL_FILE.put (kml, rec_nodeboundary.modif); 
   utl_file.fclose(kml);  
 end loop; 
 




Thanks in Advance
Re: Performance Issue (For loop) [message #383540 is a reply to message #383539] Thu, 29 January 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
spool myfile
select ...
spool off

Regards
Michel
Re: Performance Issue (For loop) [message #383551 is a reply to message #383539] Thu, 29 January 2009 02:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You are opening and closing the same file for each record.

Try this structure:
kml:=UTL_FILE.FOPEN('DIR','t.txt','A',32767);
for rec_name in cur_name loop --(here their is be millions of records)
   QRY:=rec_nodeboundary.ORD;
   UTL_FILE.put (kml, rec_nodeboundary.modif); 
end loop;
utl_file.fclose(kml);   
Re: Performance Issue (For loop) [message #383560 is a reply to message #383540] Thu, 29 January 2009 03:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Thu, 29 January 2009 09:17
spool myfile
select ...
spool off

Regards
Michel

That's a client side file, while UTL_FILE saves on the server. Not the same thing, Michel Wink

JRowbottom has given the solution, I think.

MHE
Re: Performance Issue (For loop) [message #383567 is a reply to message #383560] Thu, 29 January 2009 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe the script can run on the server. Wink

Regards
Michel
Re: Performance Issue (For loop) [message #383569 is a reply to message #383567] Thu, 29 January 2009 03:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Laughing Granted, but you know that spool is no substitute for UTL_FILE, right?

MHE
Re: Performance Issue (For loop) [message #383583 is a reply to message #383569] Thu, 29 January 2009 03:57 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, but if the final issue is performances, I can't see a better option without writing your own program.

Regards
Michel

[Updated on: Thu, 29 January 2009 03:58]

Report message to a moderator

Previous Topic: How to call JSP from Oracle
Next Topic: Bulk Binding Query (merged)
Goto Forum:
  


Current Time: Wed Dec 07 14:53:25 CST 2016

Total time taken to generate the page: 0.07796 seconds