Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Performance Improvement through procedure?

 
karthik manick
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a complex query written in my Java class which takes more time to process and so slowing down my application. If i palce the query in a PLSQL procedure and call the procedure from Java class, will it improve the performance?

Please share your thoughts.
 
fred rosenberger
lowercase baba
Bartender
Posts: 12124
30
Chrome Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
my thoughts would be a) is this really where things are slowing down (i.e. did you use a profiler), b) if the effort isn't too huge, what happens when you try it?
 
karthik manick
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am sure that the query in Java class is the reason behind slowness of my application. I am looking into other options of fine tuning the query. I also want to know if i keep this query in a procedure and call it from java, can help me in improving the performance?
 
Winston Gutkowski
Bartender
Pie
Posts: 10417
63
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
karthik manick wrote:I am sure that the query in Java class is the reason behind slowness of my application. I am looking into other options of fine tuning the query. I also want to know if i keep this query in a procedure and call it from java, can help me in improving the performance?

Unfortunately, without a LOT more information, the answer to all those questions is "maybe". Another possibility might be to use a PreparedStatement.

But you really should take on board what Fred said: The main problem with optimization is that very often we spend time optimizing the wrong thing, so you shouldn't just think that this is your bottleneck, you should be able to prove it.

And you should do that before you spend one second on optimization.

Winston
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just taking one query that doesn't perform well and moving it to PL/SQL in all probability won't help. It will still be the same query.

If you're sure one specific query is the problem, you need to tune that query. Sometimes adding an index might help, sometimes you might need to rewrite parts of the query, sometimes (if you are on Oracle) adding an optimizer hint or fiddling with database statistics can make things better.

Moving processing to PL/SQL makes sense if you can avoid sending a lot of data over the network this way - it means doing the calculation where the data is. Your case seems to be different from this.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic