File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Where is Prepared Statement actually stored ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Where is Prepared Statement actually stored ?" Watch "Where is Prepared Statement actually stored ?" New topic
Author

Where is Prepared Statement actually stored ?

Dilish Kuruppath
Greenhorn

Joined: Dec 21, 2005
Posts: 6
I tried to find the answer to this question in the internet and couldnt find a satisfactory answer.

Everybody tells that, a prepared statement is a precompiled statement and the database neednt compile it again. All fine. But where is this compiled prepared statement kept - in the JDBC layer or in the database layer ?
Sachin Dimble
Ranch Hand

Joined: Dec 07, 2005
Posts: 100
Hi Dilish,
All the precompiled statement got stored on database server itself the main advantage of this is to reduce the network traffic, ranchers correct me if am wrong?

With Best Regards,
Sachin.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

It should be stored on the database, however some databases don't support PreparedStatements so they are simulated in the Driver.
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
What about *pooled* prepared statements?


There is no emoticon for what I am feeling!
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Jeff Albrechtsen:
What about *pooled* prepared statements?


Most poolers that handle PreparedStatements do so purely on the java side; they're merely caches that hold a set of objects, just like any other cache. In that case, the driver/db does whatever it does for an open PreparedStatement, times the number in the cache.

However, there's no reason a pooler that is part of an enhanced/extended driver couldn't do it differently, with the pool being maintained in the DB somehow. I don't know of one that works that way though; it's not something I've looked at much.

Some DBs maintain an internal cache of previously parsed statements and will optimize when a new statement's SQL is found in the cache. properly speaking, that's not a pool though, and it operates for all SQL, (for the one I know best) not just PreparedStatment and not just JDBC.
Abhilash Java
Greenhorn

Joined: Jun 25, 2010
Posts: 1
Hi

I think it stores in DB itself in cache/buffer. (Somebody correct me if I'm wrong.) As I could see in API, A Prepared statement is given an sql statement as parameter when it is created [like con.prepareStatement(
"your query")] and it immediately sends to DB for compilation much before we execute the statement.

This means that when the PreparedStatement is actually executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first as it's already compiled during creation.
Devaka Cooray
ExamLab Creator
Saloon Keeper

Joined: Jul 29, 2008
Posts: 3019
    
  35

"Abhilash Java", please check your private messages regarding an important administrative matter.


Author of ExamLab ExamLab - a free SCJP / OCPJP exam simulator
What would SCJP exam questions look like? -- Home -- Twitter -- How to Ask a Question
Jobin Mathew
Ranch Hand

Joined: Oct 17, 2006
Posts: 83
Hi Stu and all,

"Most poolers that handle PreparedStatements do so purely on the java side; they're merely caches that hold a set of objects, just like any other cache."

I hear that for Prepared statements the query execution paln is reused and no query parsing and syntax checking is happening at DB end.
If the PreparedStatements are managed at JAVA/Application side, how this is possible?

Thanks.
Rojan punn
Greenhorn

Joined: Nov 29, 2011
Posts: 17

Hi All,

Sorry for the delayed Reply.
Prepared statements are managed by DBMS.
When JDBC driver pass a query first time DBMS parse the query (check sysntax and check for correctness) and creates an execution plan for the query.
In case of a prepared statement this execution plan in kept in the DBMS system memeory and use for the later executions.When driver excute the query next time the previously created plan is serached and found by the DBMS and when GO command executed bt the driver the query will be executed.
I n case of a normal query the execution plan is not retained in the memory (i think the jdbc driver flush the plan out of the memory each time a query executed eg.alter system flush shared_pool)
that is the reason the prepared statemnts are very good performer with bulk insert/udate query
 
Consider Paul's rocket mass heater.
 
subject: Where is Prepared Statement actually stored ?
 
Similar Threads
pre-compile and prepared statement
Prepared Statement
How does the database know to store prepared statement
prepared statement....
Out of Memory Exception