Prepared Statement

[Raja gopal Y]

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

[Paul Sturrock]

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.

[Adeel Ansari]

Statement is compiled by the driver and stored in PreparedStatement object.

[Raja gopal Y]

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]

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]

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]

It is worth reading http://www.theserverside.com/articles/article.tss?l=Prepared-Statments

[Paul Sturrock]

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]

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]

Thanks for all your priceless information