permaculture playing cards*
The moose likes Performance and the fly likes Performance Improvement through procedure? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Java » Performance
Bookmark "Performance Improvement through procedure?" Watch "Performance Improvement through procedure?" New topic
Author

Performance Improvement through procedure?

karthik manick
Ranch Hand

Joined: Aug 25, 2009
Posts: 52
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

Joined: Oct 02, 2003
Posts: 11356
    
  16

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?


There are only two hard things in computer science: cache invalidation, naming things, and off-by-one errors
karthik manick
Ranch Hand

Joined: Aug 25, 2009
Posts: 52
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

Joined: Mar 17, 2011
Posts: 7894
    
  21

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

Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.
 
GeeCON Prague 2014
 
subject: Performance Improvement through procedure?