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?
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
Joined: Aug 25, 2009
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?
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.
Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
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.