• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Stored Procedure VS normal Query

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the info.
But I am not convinced :-( . may be because of the info "SP will improve perfromance" is drilled to my head.
 
Ranch Hand
Posts: 328
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks..
I got it..
 
Anil Chandran
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks all :-)
 
reply
    Bookmark Topic Watch Topic
  • New Topic