Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Question
PL/SQL Question [message #269302] Fri, 21 September 2007 10:24 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I currently have a stored procedure that runs once a week. This procedure runs approximately 20 hours.

It has a cursor built and then runs through a loop. If conditions are met in this loop it then inserts this record into another table and deletes it from the table the cursor is generated from. I am looking for a way to cut processing time down and had an idea, but not sure if it is possible.

Is it possible to insert records into a cursor(for lack of better terms) and then the loop is complete enter all records from this "cursor" into the new table at once rather than one at a time, then also delete as a batch instead of one at a time.

Any help appreciated, my procedure works, just trying to make it more efficient.

[Updated on: Fri, 21 September 2007 10:25]

Report message to a moderator

Re: PL/SQL Question [message #269303 is a reply to message #269302] Fri, 21 September 2007 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The slow by slow (sorry row by row) process is most often the wrong one.
But without more details and especially your Oracle version, we cant' help you more.
Yes, it is possible, in some cases, no, it is not possible, in other cases.

Regards
Michel

[Updated on: Fri, 21 September 2007 10:34]

Report message to a moderator

Re: PL/SQL Question [message #269307 is a reply to message #269302] Fri, 21 September 2007 10:51 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You are definitely thinking along the correct lines though...the right frame of reference. As Michel rightly points out, doing things a row at a time should be the last resort, because it is the slowest. If possible you can use sql statements instead of plsql (if the logic is simple enough for your oracle version). Do an insert into new table select from old table, then do a delete from old table with the same where clause criteria, then a commit at the end.

Or, if you do need plsql, look into the bulk collect and forall operators.
Re: PL/SQL Question [message #269313 is a reply to message #269302] Fri, 21 September 2007 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you really want to identify the source of the slowness & not just guess at some random solution, then enable SQL_TRACE & run the results through TKPROF.
The resultant output will provide accurate measures as to where all the time is being spent.
Re: PL/SQL Question [message #269314 is a reply to message #269313] Fri, 21 September 2007 11:53 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Sorry... Oracle version 9i...

briefly read the responses, and will look more into it after this meeting I have to go to.

Thanks guys.
Re: PL/SQL Question [message #269368 is a reply to message #269314] Fri, 21 September 2007 17:03 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Why don't you try for BULK COLLECT INTO for storing your featched record's once and then check these values using loop.
It will centeinly reduce your time.

Cheers
Soumen
Re: PL/SQL Question [message #269398 is a reply to message #269368] Sat, 22 September 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Soumen, I see you're still good to repeat other answers.

Regards
Michel
Re: PL/SQL Question [message #269818 is a reply to message #269398] Mon, 24 September 2007 14:22 Go to previous message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Opps! Michel, I haven't seen the answer from smartin.
Appologies for that.

Soumen
Previous Topic: Password and Username
Next Topic: BULK collect performance is slow
Goto Forum:
  


Current Time: Sat Dec 10 07:09:31 CST 2016

Total time taken to generate the page: 0.05683 seconds