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 Amir Michail <am...@gmail.com> on 2008/11/29 03:11:18 UTC
Why close ResultSet & PreparedStatement?
Hi,
Is there any point in calling the close method on ResultSet and the
close method of PreparedStatement?
Amir
--
http://b4utweet.com
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail
Re: Why close ResultSet & PreparedStatement?
Posted by Bryan Pendleton <bp...@amberpoint.com>.
>> Is there any point in calling the close method on ResultSet and the
>> close method of PreparedStatement?
>
> BTW, I'm using embedded mode. Why won't the garbage collector take
> care of this?
In general, the garbage collector does take care of this.
But there have been bugs in the past, which caused Derby to fail to
collect result sets and statements. For example, DERBY-210, which is
mostly fixed, but still doesn't perfectly collect all resources:
https://issues.apache.org/jira/browse/DERBY-210
And, as with most garbage-collected items, by closing them yourself
you can accelerate and simplify the process, thus using fewer
resources overall.
thanks,
bryan
Re: Why close ResultSet & PreparedStatement?
Posted by Amir Michail <am...@gmail.com>.
On Fri, Nov 28, 2008 at 9:11 PM, Amir Michail <am...@gmail.com> wrote:
> Hi,
>
> Is there any point in calling the close method on ResultSet and the
> close method of PreparedStatement?
>
> Amir
BTW, I'm using embedded mode. Why won't the garbage collector take
care of this?
Amir
>
> --
> http://b4utweet.com
> http://chatbotgame.com
> http://numbrosia.com
> http://twitter.com/amichail
>
--
http://b4utweet.com
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail
Re: Why close ResultSet & PreparedStatement?
Posted by Emmanuel Cecchet <ma...@frogthinker.org>.
derby@segel.com wrote:
>> Amir Michail wrote:
>>
>>> Hi,
>>>
>>> Is there any point in calling the close method on ResultSet and the
>>> close method of PreparedStatement?
>>>
>>> Amir
>>>
>> You might later change the database to one where it matters much more.
>>
>> Mark Thornton
>>
>
> Huh?
>
> I'm sorry but your answer doesn't make any sense.
>
Mark answer made a lot of sense. Things that may not have a side effect
with Derby (especially in embedded mode) can have a significant effect
with other databases. Try to leave ResultSets and PreparedStatements
open with Oracle and we'll see how far you go.
> To answer Amir's question... you close the ResultSet and the
> PreparedStatements to remove any overhead as a result of their
> instantiation.
>
> Actually its pretty rare that you want to close the prepared statement since
> one would use a prepared statement for the life of the application/service.
> (The point of a prepared statement is that you use the statement once and
> you use it over and over ...)
>
Looks like you have never done batch updates.
Note that a PreparedStatement is attached to a connection, so if you are
using connection pooling, keeping the same PreparedStatement throughout
the lifetime of the application will certainly interfere with the
purpose of the connection pooling.
My 2 cents
Emmanuel
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet
Re: Why close ResultSet & PreparedStatement?
Posted by Mark Thornton <mt...@optrak.co.uk>.
derby@segel.com wrote:
> Ok...
>
> With respect to *other* databases, you have timeouts which will drop the
> database side of a result set or connection so the resources are recovered.
> Commercial databases will clean things up. (Although its always better to
> clean up your own mess) If you close the database connection, you'll trigger
> the clean up process.
>
You are over optimistic. It is depressingly common for applications to
fail if ResultSet's aren't closed aggressively. While Oracle is the
usual whipping boy for this behaviour, it is by no means unique.
Mark Thornton
RE: Why close ResultSet & PreparedStatement?
Posted by de...@segel.com.
> -----Original Message-----
> From: Mark Thornton [mailto:mthornton@optrak.co.uk]
> Sent: Saturday, November 29, 2008 4:42 PM
> To: Derby Discussion
> Subject: Re: Why close ResultSet & PreparedStatement?
>
> derby@segel.com wrote:
> >> Amir Michail wrote:
> >>
> >>> Hi,
> >>>
> >>> Is there any point in calling the close method on ResultSet and the
> >>> close method of PreparedStatement?
> >>>
> >>> Amir
> >>>
> >>>
> >>>
> >> You might later change the database to one where it matters much more.
> >>
> >> Mark Thornton
> >>
> >
> > Huh?
> >
> > I'm sorry but your answer doesn't make any sense.
> >
> > To answer Amir's question... you close the ResultSet and the
> > PreparedStatements to remove any overhead as a result of their
> > instantiation.
> >
> >
> The resources required by an open ResultSet depend on the implementation
> of the database and drivers. It is fairly low for embedded Derby but
> considerably higher for some other databases. If there is a chance that
> the database used may be replaced by something else (Oracle, SQL Server,
> etc), then being careful about closing objects like ResultSet can be
> more important.
>
> I agree that the overhead of PreparedStatement's is usually not great.
>
> Mark Thornton
Ok...
With respect to *other* databases, you have timeouts which will drop the
database side of a result set or connection so the resources are recovered.
Commercial databases will clean things up. (Although its always better to
clean up your own mess) If you close the database connection, you'll trigger
the clean up process.
With respect to the prepared statements, its not an issue of overhead. Its
an issue of why one uses a prepared statement. The idea is that you prepare
a statement once, deal with the overhead and then reuse the statement
multiple times.
Its not that you have "minimal" overhead, but that the concept is that
you're preparing a statement once to be used until your application
dies/ends/etc ...
Re: Why close ResultSet & PreparedStatement?
Posted by Mark Thornton <mt...@optrak.co.uk>.
derby@segel.com wrote:
>> Amir Michail wrote:
>>
>>> Hi,
>>>
>>> Is there any point in calling the close method on ResultSet and the
>>> close method of PreparedStatement?
>>>
>>> Amir
>>>
>>>
>>>
>> You might later change the database to one where it matters much more.
>>
>> Mark Thornton
>>
>
> Huh?
>
> I'm sorry but your answer doesn't make any sense.
>
> To answer Amir's question... you close the ResultSet and the
> PreparedStatements to remove any overhead as a result of their
> instantiation.
>
>
The resources required by an open ResultSet depend on the implementation
of the database and drivers. It is fairly low for embedded Derby but
considerably higher for some other databases. If there is a chance that
the database used may be replaced by something else (Oracle, SQL Server,
etc), then being careful about closing objects like ResultSet can be
more important.
I agree that the overhead of PreparedStatement's is usually not great.
Mark Thornton
RE: Why close ResultSet & PreparedStatement?
Posted by de...@segel.com.
> Amir Michail wrote:
> > Hi,
> >
> > Is there any point in calling the close method on ResultSet and the
> > close method of PreparedStatement?
> >
> > Amir
> >
> >
> You might later change the database to one where it matters much more.
>
> Mark Thornton
Huh?
I'm sorry but your answer doesn't make any sense.
To answer Amir's question... you close the ResultSet and the
PreparedStatements to remove any overhead as a result of their
instantiation.
Actually its pretty rare that you want to close the prepared statement since
one would use a prepared statement for the life of the application/service.
(The point of a prepared statement is that you use the statement once and
you use it over and over ...)
Not sure what Mark means by "changes to the database".
HTH
-Mike
Re: Why close ResultSet & PreparedStatement?
Posted by Mark Thornton <mt...@optrak.co.uk>.
Amir Michail wrote:
> Hi,
>
> Is there any point in calling the close method on ResultSet and the
> close method of PreparedStatement?
>
> Amir
>
>
You might later change the database to one where it matters much more.
Mark Thornton