db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Newbie questions on PreparedStatement in Derby
Date Wed, 07 Jun 2006 16:08:32 GMT
Farrukh Najmi wrote:
> My application uses Derby 10.1 in a derby client that is a servlet 
> running within a Servlet Container (typically Sun Java Application 
> Srever 8.2 a.k.a AS or Tomcat 5.0.x).
> We use PreparedStatement quite a bit. I would like to know what we can 
> do to maximize reuse of PreparedStatement optimization plan within Derby.
> Whose job is it to do PreparedStatement pooling?
> Is is Derby's or the container (AS / Tomcat)?
> What can we do do maximize leverage of PreparedStatement caching in my 
> configurations?
> BTW We do not typically close our PreparedStatement basedon the 
> possibly faulty assumption that this would remove the 
> PreparedStatement form the PreparedStatement cache. We instead rely on 
> Connection.close() to take care of doing whatever closing is
> needed to be done. Is this a bad practice?
> Above practice has led us into trouble with the following known Derby 
> issue:
> http://issues.apache.org/jira/browse/DERBY-210
> It seems the workaround is to close all PreparedStatements explicitly.
> If we do that will it remove PreparedStatement that is being closed 
> from the PreparedStatement cache?
> Thanks for any guidance on this.
Hi Frank -
Plans in the PreparedStatement cache in Derby are only removed when they 
age out, that is: the plans will be maintained in cache for use until:
  Additional space is needed for a newly created PreparedStatement  AND
  The plan is the Least Recently Used in the cache

It is unlikely that a PreparedStatement that has seen recent use will be 
removed for the cache.  So, to put it briefly, let Derby cache the plans 
and DO close the PreparedStatements when it is no longer needed by the 

View raw message