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 ofthe statement is cached across connections, and can be shared acrossconnections. It is this compiled plan that takes the significant amountof 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:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
