Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Where is Prepared Statement actually stored ?

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ?
 
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What about *pooled* prepared statements?
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 6871
1312
IntelliJ IDE jQuery Eclipse IDE Postgres Database Tomcat Server Chrome Google App Engine
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"Abhilash Java", please check your private messages regarding an important administrative matter.
 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 17
Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic