This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes PreparedStatement and StoredProcedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement and StoredProcedure" Watch "PreparedStatement and StoredProcedure" New topic
Author

PreparedStatement and StoredProcedure

marlajee Borstone
Ranch Hand

Joined: Jun 26, 2008
Posts: 35
Hi
I have few questions from Java stored Procedure and PreparedStatemnt .

As we know whenever executing some query in Java(JDBC) code, there four steps are involved :
1.parse the incoming SQL query
2.compile the query
3.plan/optimize the data acquisition path
4.execute the query

While using PreparedStatement, first time thes all steps gets executed. But, the 4th steps gets executed from next time onwards; and hence it is faster.


Q1)
But, what happens in case of StoredProcedure which we call by using CallableStatement ? Are these four steps involved in CallableStatement also ? Whether, similar to PreparedStatement, only the 4th step gets executed from second time onwards?

Q2)
Performancewise, which one we can judge as faster ?

Is it the right criteria to select either PreparedStement or CallableStatement (StoreProcedure) (?):->
When the query is simple, use PreparedStatement; and when the query is complex (doing multiple operations in one go) use StoredProcedure ().


Q3)
Do these both (PreparedStatement & StoredProcedure) gets stored ins DataBase side ; or in Java. I believe there is no any place in Java to get it stored. So is it stored in DataBase or in any WebServer (if it is running on webserver) ?

I would highly appreciate if any one can help to clearly understand the practical use and difference in StoredProcesure and PreparedStatement.


Regard,
[ September 17, 2008: Message edited by: marlajee Borstone ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30353
    
150

Note that we have a JDBC forum for questions like these. I'll move this thread for you.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30353
    
150

1) CallableStatement extends PreparedStatement. So yes, the same steps occur. The difference being that "the query" is the call to the stored procedure rather than the actual SQL statement. Rendering step 3 trivial - "run the stored proc" is the plan.
2) It depends on what the CallableStatement does. If it is one statement that is the same as the one the PreparedStatement, the performance should be comparable. If the CallableStatement would have required multiple PreparedStatements, it will be faster as you will save on network traffic. However, you should not start out choosing based on performance. You should choose a design and then only change once you have a proven performance problem. It is often only a small portion of the application that is a bottleneck. I think having maintainable code is more important than trying to pigeonhole the code into a situation it doesn't fit.
3) A PreparedStatement is stored in memory (database driver). the database also stores some recent execution plans but this isn't the one people talk about when they say PreparedStatements are cached.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement and StoredProcedure