PreparedStatement Persistance

[Daniel Morton <dj...@yahoo.com>]

Over the past few threads, someone mentioned that the
PreparedStatements are cached on the database even
when the connection that created the
PreparedStatements are closd... I was discussing that
very issue with an associate of mine a couple of weeks
ago, and he assured me that the Oracle and DB2 people
he knew told him that the PreparedStatements did not
persist between connections.  Is that incorrect, or
does Derby just handle it differently?

Thanks,

djm 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: PreparedStatement Persistance

[Craig L Russell <Cr...@Sun.COM>]

On Apr 5, 2006, at 10:31 AM, Daniel John Debrunner wrote:

> Daniel Morton wrote:
>> Over the past few threads, someone mentioned that the
>> PreparedStatements are cached on the database even
>> when the connection that created the
>> PreparedStatements are closd... I was discussing that
>> very issue with an associate of mine a couple of weeks
>> ago, and he assured me that the Oracle and DB2 people
>> he knew told him that the PreparedStatements did not
>> persist between connections.  Is that incorrect, or
>> does Derby just handle it differently?
>
> PreparedStatements are not cached in Derby, however the compiled  
> plan of
> the statement is cached across connections, and can be shared across
> connections. It is this compiled plan that takes the significant  
> amount
> of time to create.
>
> PreparedStatements are really a wrapper around the compiled plan that
> maintans state specific to that statement and connection.
>
> Most databases have this ability to cache the compiled plan, to avoid
> re-compilation across connections.

I agree completely with what Dan says here (he is the expert).

I would just like to add that this is an example of making sure to  
ask the right question. If you ask about the PreparedStatement  
instances being reused between Connections, then you may be concerned  
about memory, footprint, garbage collection, and other issues. If you  
ask about the performance of reusing query statements across  
Connections, that's a completely different issue, as Dan explains.

There is yet another subtlety and that is that the cached compiled  
query plan is a back end concept, and doesn't necessarily translate  
to anything on the front end where jdbc lives.

Craig
>
> Dan.
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!

Re: PreparedStatement Persistance

[Daniel John Debrunner <dj...@apache.org>]

Daniel Morton wrote:
> Over the past few threads, someone mentioned that the
> PreparedStatements are cached on the database even
> when the connection that created the
> PreparedStatements are closd... I was discussing that
> very issue with an associate of mine a couple of weeks
> ago, and he assured me that the Oracle and DB2 people
> he knew told him that the PreparedStatements did not
> persist between connections.  Is that incorrect, or
> does Derby just handle it differently?

PreparedStatements are not cached in Derby, however the compiled plan of
the statement is cached across connections, and can be shared across
connections. It is this compiled plan that takes the significant amount
of time to create.

PreparedStatements are really a wrapper around the compiled plan that
maintans state specific to that statement and connection.

Most databases have this ability to cache the compiled plan, to avoid
re-compilation across connections.

Dan.

Re: PreparedStatement Persistance

["David W. Van Couvering" <Da...@Sun.COM>]

My understanding is that the Derby prepared statement cache is portable 
across connections.  It is usable by any connection to the same database 
-- the prepared statement text is compared with existing cached 
statements and if it alreday exists then it is reused.

But it's not my area of expertise; there are others who can confirm or 
deny what I say here.

David

Daniel Morton wrote:
> Over the past few threads, someone mentioned that the
> PreparedStatements are cached on the database even
> when the connection that created the
> PreparedStatements are closd... I was discussing that
> very issue with an associate of mine a couple of weeks
> ago, and he assured me that the Oracle and DB2 people
> he knew told him that the PreparedStatements did not
> persist between connections.  Is that incorrect, or
> does Derby just handle it differently?
> 
> Thanks,
> 
> djm 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com