Home » RDBMS Server » Performance Tuning » Performance issue
Performance issue [message #295090] Mon, 21 January 2008 05:29 Go to next message
ruchika.wadhwa
Messages: 2
Registered: January 2008
Location: Noida
Junior Member
I have to retrieve 100 rows with multiple columns on my screen which are fetched by stored procedure. The columns are dynamically formed. I want to tune the procedure as it is taking lot of time. few columns of a row are inserted first. Remaining columns of that row are updated on by one through "execute immediate". But as it is done for every column X no. of rows, it is taking a lot of time. Any suggestions??
Re: Performance issue [message #295092 is a reply to message #295090] Mon, 21 January 2008 05:39 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Let's look at what you have effectively asked.
"My procedure is slow, how can I make it go faster"
Have a read of the posting guidelines the come back and reformulate your question so that we can actually help.
Re: Performance issue [message #295093 is a reply to message #295090] Mon, 21 January 2008 05:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Are you fetching / inserting / updating row by row?
Can you use BULK COLLECT / INSERT /UPDATE?

By
Vamsi
Re: Performance issue [message #295097 is a reply to message #295093] Mon, 21 January 2008 05:51 Go to previous messageGo to next message
ruchika.wadhwa
Messages: 2
Registered: January 2008
Location: Noida
Junior Member
I was thinking to use it but its not feasible. Problem is we have to update or insert entire row in bulk commands. In my case, few columns are inserted first and the rmaining columns of the row are inserted by updation which is done for every remaining column. I am not able to use a pl/sql table coz i dont know how many columns will be inserted first and how many updated for that row later..
Re: Performance issue [message #295206 is a reply to message #295090] Mon, 21 January 2008 13:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
maybe it is time to go back to the drawing board and re-write your insert/update code. I have seen this many times and it is usually created by someone who does not understand the costs involved with this kind of process. They are usually fixated on creating "generic code" which as you have just learned is a bad idea. In particular I have never understood the need to update each column seperately. Surely on occassion this is necessary but most of the time not. Twenty Seven years in IM and I still haven't figured out how the idea of updating one column at a time got started as an acceptable way to do things.

Generic code can be a life saver in the right circumstances. Problem is, eveyone thinks their particular situation is "the perfect opportunity for some generic code" when in fact it is mearly a way to obscure what is happening in the system, and a way to make things go slow.

I suggest you think a little more about why this piece of code "needs" a generic solution, then forget about it and write the piece of code most other people would have created. What have you got to loose except a little time, and given how slow this thing is working at the moment, you have plenty of that.

Good luck, Kevin
Re: Performance issue [message #295310 is a reply to message #295090] Tue, 22 January 2008 01:56 Go to previous messageGo to next message
santoshorcl
Messages: 24
Registered: August 2007
Location: pune
Junior Member

It difficult to say the performance issue is because of x and y. but you can solve this kind of issues by using few optimizer hints. like you can paralyze your update operation by using /*+parallel()*/ hint and insert using /*+APPEND*/ hint.
Re: Performance issue [message #295316 is a reply to message #295310] Tue, 22 January 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Good and you don't consider that activating parallel may slow down the process?

Regards
Michel
Re: Performance issue [message #295506 is a reply to message #295090] Tue, 22 January 2008 08:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
sure he does, that why he said you can paralyze it.

google search

Definitions of paralyze on the Web:

make powerless and unable to function; "The bureaucracy paralyzes the entire operation"
cause to be paralyzed and immobile; "The poison paralyzed him"; "Fear paralyzed her"
wordnet.princeton.edu/perl/webwn

Paralysis is the complete loss of muscle function for one or more muscle groups. Major causes are stroke, trauma, poliomyelitis, amyotrophic lateral sclerosis (ALS), botulism, spina bifida, multiple sclerosis, and Guillain-Barré syndrome. ...
en.wikipedia.org/wiki/Paralyze

Re: Performance issue [message #295509 is a reply to message #295506] Tue, 22 January 2008 08:43 Go to previous message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
sure he does, that why he said you can paralyze it.

Laughing

(Didn't even notice it, I'm pretty good in reading what I want to read)

Regards
Michel
Previous Topic: Concurrency wait. How I can resolve.
Next Topic: how to specify priority of active sql sessions running in the database
Goto Forum:
  


Current Time: Wed Nov 13 05:15:14 CST 2024