Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared Statement

 
Raja gopal Y
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Statement is compiled by the driver and stored in PreparedStatement object.
 
Raja gopal Y
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for all your priceless information
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic