Yes, Stanley's answer is my understanding too. Please do close prepared
statements.
David
Stanley Bradbury wrote:
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
routine.
