• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Where is Prepared Statement actually stored ?

 
Dilish Kuruppath
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What about *pooled* prepared statements?
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Pie
Posts: 4330
238
Chrome Eclipse IDE Google App Engine IntelliJ IDE jQuery Postgres Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Abhilash Java", please check your private messages regarding an important administrative matter.
 
Jobin Mathew
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic