This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
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: 11145
    
  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: 7484
    
  18

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: 3606
    
  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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance Improvement through procedure?
 
Similar Threads
What's the best design approach when querying a database in a web app?
Performance when mutiple resultsets are returned from a stored procedure ?
Help!!! Telephone Interview
Unit testing StoredProcedure with HSQLDB
Stored Procedure VS normal Query