You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Farrukh Najmi <Fa...@Sun.COM> on 2006/06/07 17:17:36 UTC
Newbie questions on PreparedStatement in Derby
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.
--
Regards,
Farrukh
Re: Newbie questions on PreparedStatement in Derby
Posted by Deepa Remesh <dr...@gmail.com>.
On 6/8/06, Farrukh Najmi <Fa...@sun.com> wrote:
>
> OK. So I will make sure to explictly close all PreparedStatements in
> finally blocks and not rely on Connection.close() so as to avoid:
>
> http://issues.apache.org/jira/browse/DERBY-210
>
> One more related question. Are there known memory leaks if my
> application also does not close regular Statements (as opposed to
> PreparedStatements)
> and relies on Connection.close()?
Yes. The memory leak applies to regular Statements too. Please close
all statement objects explicitly after use.
Most of DERBY-210 has been fixed by the following sub-task
http://issues.apache.org/jira/browse/DERBY-1103. These fixes will be
available in the upcoming 10.1.3 release.
The list of outstanding issues for DERBY-210 can be seen at
http://issues.apache.org/jira/browse/DERBY-210#action_12370254
Thanks,
Deepa
Re: Newbie questions on PreparedStatement in Derby
Posted by Farrukh Najmi <Fa...@Sun.COM>.
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:
http://issues.apache.org/jira/browse/DERBY-210
One more related question. Are there known memory leaks if my
application also does not close regular Statements (as opposed to
PreparedStatements)
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.
>>
>>
>>
>
--
Regards,
Farrukh
Re: Newbie questions on PreparedStatement in Derby
Posted by David Van Couvering <Da...@Sun.COM>.
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.
>
>
>
Re: Newbie questions on PreparedStatement in Derby
Posted by Stanley Bradbury <St...@gmail.com>.
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.