wood burning stoves 2.0*
The moose likes JDBC and the fly likes Prepared Statement 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 » Databases » JDBC
Bookmark "Prepared Statement" Watch "Prepared Statement" New topic
Author

Prepared Statement

Raja gopal Y
Greenhorn

Joined: Mar 08, 2005
Posts: 15
Hi..

The Prepared Statement is Prepared once and used to execute the same kind of SQL statemetns.

Can any one pls let me know ""Where is the Prepared Statement Compiled and Stored"".

It's Greate if any one can explain what happens internally.

Regds
Rajagopal


CHEERS !!!<br /> <br />RAJAGOPAL Y
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

On the DataBase. If you have a trace utitlity with whichever DataBase you currently use you can turn it on and watch what happens. The first time a PreparedStatement is called you probably see the actual SQL it is compiled from. Every subsequent time you are more likely to see "execute statment " statements.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Statement is compiled by the driver and stored in PreparedStatement object.
Raja gopal Y
Greenhorn

Joined: Mar 08, 2005
Posts: 15
If it's Prepared on DB Side and Stored, if the DB is running on a different machine, isn't it a performance problem?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Adeel Ansari:
Statement is compiled by the driver and stored in PreparedStatement object.


Are you sure? My understanding is the expensive part of a running a statement against a DB is that the DB's statement engine first need to interpret the SQL, then calculate an efficient query plan, and that's what gets compiled. So the driver itself doesn't do the compilation, it the DB. And true a version of the statment is held in a PreparedStatment object, but that is just used as a key to the actual compiled PreparedStatement held in the statement cache in the DB?
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Prepared SQL statements get compiled in the database only once, future invocations do not recompile them. The result of this is a decrease in the database load, and an increase in performance of up to 5x.
- from http://developer.java.sun.com/developer/community/chat/JavaLive/2001/jl0619.html


Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared."
- from http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/preparedstatement.html


Just for information:
Using a new connection requires a prepared statement to be recreated. Reusing connections allows a prepared statement to be reused.
- from http://www.javaperformancetuning.com/tips/jdbc_prepared.shtml#REF18


PreparedStatement objects are compiled (prepared) by the JDBC driver or database for faster performance, and accept input parameters so they can be reused with different data.
-from www-105.ibm.com/developerworks/./..
[ March 17, 2005: Message edited by: Adeel Ansari ]
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
It is worth reading http://www.theserverside.com/articles/article.tss?l=Prepared-Statments
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Prepared SQL statements get compiled in the database only once, future invocations do not recompile them

Ah right - so we are describing the same process. I understood your previous post as "the statement is always compiled by the driver and only stored in the PreparedStatament object", which reading your other posts seems to not be what you meant.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I read it the same way. Funny when two people disagree with the same answer

Two extra points on PreparedStatements:

1) typically the PS is 'matched' against the SQL String, so you should ensure the String you contruct the PS with is always the same. I believe that in Oracle it is even case-sensitive.
2) In some databases that don't support query caching, support is faked in the JDBC Driver. I think MySQL is an example of this.
Raja gopal Y
Greenhorn

Joined: Mar 08, 2005
Posts: 15
Thanks for all your priceless information
 
 
subject: Prepared Statement
 
Similar Threads
Printing prepared statement to console
Interview question
doubt
Only returning 1 record
Internal implementation of Prepared Statement