wood burning stoves 2.0*
The moose likes JDBC and the fly likes Stored Procedure VS normal Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored Procedure VS normal Query" Watch "Stored Procedure VS normal Query" New topic
Author

Stored Procedure VS normal Query

Anil Chandran
Greenhorn

Joined: Apr 11, 2005
Posts: 15
Hi All,

I have often heared that SP will improve performance when compared with similar query.Both SP and query as executed from Java ( CallableStmt and prepared Stmt respectively)

But any idea like howmuch % of performance is improved and what all are the factors added to this improved performance?

Thanks
Anil


I started out with nothing..I still have most of it ...
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
There's nothing magic about stored procedures (SPs). The actual executed SQL is going to take whatever time it's going to take, whether it's executed in an SP or executed via JDBC. Where an SP can sometimes improve performance is in cutting off the back-and-forth between the driving Java program and database. For example, if you have purchasing application and need to update a dozen tables for every sale, you might consider witing an SP that takes the sale record, does ALL the work, and returns.

If your application is suitable for that type of division of the work, and you're willing to code in the SP language (which often isn't as nice as Java) then an SP can be just the thing. But merely moving a simple query into an SP doesn't make anything faster.

There are other reasons to consider SPs, such as a way to code features whose implementations have to be non-portable across databases; each supported database version gets an SP with the same parameters and a different implementation. The calling program then doesn't have to care which database it's talking to...
Anil Chandran
Greenhorn

Joined: Apr 11, 2005
Posts: 15
Thanks for the info.
But I am not convinced :-( . may be because of the info "SP will improve perfromance" is drilled to my head.
Shrinivas Mujumdar
Ranch Hand

Joined: Aug 27, 2004
Posts: 328
If you are doing some complex Task related to DB but if you write the individual queries each time the result(temporary) will flow from DB to Appl. & back...Instaed of that give all necessary Params to DB SP & call it only once adv:Go to DB only once & SP is compiled only once & stored on DB side in binary format...which improves the performance


Feel Free to ask if not clear
Shrinivas
Anil Chandran
Greenhorn

Joined: Apr 11, 2005
Posts: 15
thanks..
I got it..
Anil Chandran
Greenhorn

Joined: Apr 11, 2005
Posts: 15
Hi I am back.

Since SPs are stored in DB and thus they are precompiled , will that improve the performance ( Yes, we are building dynamic query inside SP but..)

For normal query we need to do that compilation step. right ?

What you think ?
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Based on what I know about Oracle (and I know quite a lot), no. Other databases may be quite different however.

In a nutshell, the SP compilation is pretty directly analogous to the Java class file compilation; it's mostly all the stuff around the SQL statement that's getting compiled, not the SQL statement itself. Now with an Oracle SP, the SP compiler will validate SQL statements that it can recognize (it won't validate dynamically constructed SQL), but it's only doing 2 things; confirming that the SQL syntax is valid and that any objects referred to exist. However, all this does is give your SP a much greater chance of being successfull at runtime; it does exactly 0 for runtime performance.

Why? Because every single new SQL statement submitted to Oracle (by an SP, by JDBC, by ODBC, by any other mechanism) goes through the same process. It's "parsed" as they call it, sort of like compiled, but different.

Now one of the best ways to optimize your DB activity is to *NOT* submit SQL statements for parse; this is accomplished in JDBC by reusing existing PreparedStatements (which hold a reference to the object in the DB that represents the parsed SQL) with different bind variables (the same thing can be done in stored procedures, with the same effects, however the lifetime of an active SP is usually shorter than a Java program, so SPs are a somewhat more limited in when you can get such a benefit). There are different approaches for getting this benefit from PreparedStatement, depending on all sorts of tradeoffs, as well as what your program is actually doing. Batching and cacheing are common strategies. Some vendor drivers and some connection poolers have mechanisms for prepared statement caching. Batching is allegedly standard JDBC, but the spec left way too much freedom to implementors, so you need to be a little careful in cross-DB situations.
Bruno Boehr
Greenhorn

Joined: Feb 15, 2006
Posts: 17
Anil,

As stu derby points out, SPs will save you a few network roundtrips per call, so your database logic should in general execute faster. But there are other factore to take into account when deciding whether or not go with SPs.

Probably the biggest one is the effect such a decision will have on your development practices. The sad fact is, SPs are considerably more painful to write, test, debug, tune up and otherwise maintain as compared to handling all SQL in the Java code. And this "inconvenience" might far outweigh any benefits you could get on the performance side.

So, unless performance is an extremely critical factor, I would not be looking in that direction. And by the way, a non-SP solution would allow you to leverage very powerful techniques not available in stored procedures, such as in-memory caching, that might help you boost the performance of your data access layer to such a degree that the SP-based version will pale in comparison.


<a href="http://webjavenue.com/" target="_blank" rel="nofollow">Your first website in Java: easier than you think</a>
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
For Oracle, here's a much much more authoritive answer, saying the same thing:
http://asktom.oracle.com/pls/ask/f?p=4950:8:2700709576181465525::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:499097979114

The author of the response is Tom Kyte, a vice-president at Oracle and widely acknowledged expert on the Oracle database. He favors using stored procedures for other, architectural reasons, but not for SQL performance.

Of course, other databases are different, but they operate under a lot of the same fundamental constraints.
Anil Chandran
Greenhorn

Joined: Apr 11, 2005
Posts: 15
thanks all :-)
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Stored Procedure VS normal Query