Home » SQL & PL/SQL » SQL & PL/SQL » performance issue
performance issue [message #217081] Wed, 31 January 2007 10:48 Go to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Hi,

I'm facing a performance problem while executing a procedure.

I've a source table of 1ml rows and its to be passed for the validation of 43 rules.

After this, the no error rows to be inserted in the target table.

The procedure has been written like, implementing a validation rule for all the data and also update the status
of the data in the same source table.This will be repeating for all the rules. Finally the no error rows are
inserted to the target table.But its taking time becuse each rule is implemented in the where clause of the
updation part.

For eg, if the source table have 10000 rows, then the 10000 rows will be getting updated bulkly for 43
validation rules.

If I change the proc like fetch one row and implement all the validation(43 rules) then finally update the status
of the row. The same will be repeat for all the rows.

For Eg if the source table have 10000 rows, then 10000 times update will happen after all the validations.

Should it be wise decision? or First is good? If any better suggestion would be appreciated.

Thanks
Sam
Re: performance issue [message #217085 is a reply to message #217081] Wed, 31 January 2007 11:04 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
What I gather is that you have 43 update statements of the form:

update table
set status = ?
where column1= <wrong value>
and column2 = <wrong value>
...

Potentially, this will be very slow unless all the columns used in the conditions are well indexed. Otherwise, each statement will require a scan of the 1 million row table.

I would say that if you can change it to a procedure where you select all the rows through a cursor and then apply the rules by examining variables in memory, this will be much faster. It will only require one scan of the table and the time taken to examine the values will be trivial.

To do the update, you should open the cursor "for update" and use the "where current of" option. This will use the rowid to identify the row and will be very fast.
Re: performance issue [message #217161 is a reply to message #217085] Wed, 31 January 2007 21:59 Go to previous messageGo to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Thanks for the immediate reply.

I have a another question here. If I hold millions of data in a cursor, and read one by one would affect the performance? Is there any way to handle?

Thanks
Sam
Re: performance issue [message #217165 is a reply to message #217085] Wed, 31 January 2007 22:07 Go to previous messageGo to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Hi

Also I couldnt understand how to implement this step what you mentioned in your reply
"To do the update, you should open the cursor "for update" and use the "where current of" option. This will use the rowid to identify the row and will be very fast."

If you can explain, I can implement the same.

Thanks
Sam
Re: performance issue [message #217173 is a reply to message #217165] Wed, 31 January 2007 23:38 Go to previous messageGo to next message
CoolBuddy
Messages: 10
Registered: December 2006
Location: India
Junior Member
check Link for the "for update" and "Where current of" options.
Re: performance issue [message #217200 is a reply to message #217081] Thu, 01 February 2007 02:58 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Quote:
If I hold millions of data in a cursor, and read one by one would affect the performance? Is there any way to handle?


You can improve the performance by using bulk fetches, if it is an issue. Look in the PL/SQL manual for more details.

http://download-uk.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/05_colls.htm#23723
Re: performance issue [message #217531 is a reply to message #217081] Fri, 02 February 2007 12:16 Go to previous message
nvs_krishna
Messages: 3
Registered: February 2007
Location: Atlanta
Junior Member

Please read about Reference cursor. This might help you.
Previous Topic: Minimal row that is not locked
Next Topic: very urgent
Goto Forum:
  


Current Time: Tue Dec 06 14:19:48 CST 2016

Total time taken to generate the page: 0.07648 seconds