db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Farrukh Najmi <Farrukh.Na...@Sun.COM>
Subject Re: Newbie questions on PreparedStatement in Derby
Date Fri, 09 Jun 2006 06:53:23 GMT
David Van Couvering wrote:
> Yes, Stanley's answer is my understanding too.  Please do close 
> prepared statements.

OK. So I will make sure to explictly close all PreparedStatements in 
finally blocks and not rely on Connection.close() so as to avoid:


One more related question. Are there known memory leaks if my 
application also does not close regular Statements (as opposed to 
and relies on Connection.close()?

BTW recall I run within an Sun AppServer 8.2 environment which does 
ConnectionPooling and there for does not actually
close the Connection on Connection.close().

Thanks for your help.

> 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.


View raw message