db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Van Couvering <David.Vancouver...@Sun.COM>
Subject Re: Newbie questions on PreparedStatement in Derby
Date Wed, 07 Jun 2006 18:18:45 GMT
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.
> 
> 
> 


Mime
View raw message