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 ?
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.
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.
"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?
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
subject: Where is Prepared Statement actually stored ?