Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Date: Fri, 13 May 2005 10:07:23 -0700
From: David Van Couvering <David.Vancouvering@Sun.COM>
Subject: Re: error when turning off auto commit
In-reply-to: <4284DCA2.1080600@sbcglobal.net>
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4284DECB.9060102@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6)
 Gecko/20040113
References: <42832271.8050707@visual-i.co.uk>
 <42838DA6.7090309@debrunners.com>
 <42848A05.1040105@visual-i.co.uk> <4284A58C.30109@debrunners.com>
 <4284DCA2.1080600@sbcglobal.net>

I thought there was a bug where not closing statements accumulated 
memory, but it's just a rumor for me, not a direct experience.

David

Mike Matrigali wrote:

> Does anyone know if it is expected in derby to accumulate memory in
> a single transaction if you don't close statements or resultSets?
> I am pretty sure not closing ResultSets is a problem, I am not sure
> about Statements.
> 
> Daniel John Debrunner wrote:
> 
>> Chris wrote:
>>
>>
>>> Hi,
>>>
>>> to demonstrate the problem, i've written some code which approximates
>>> the code i'm using.  (attached)
>>>
>>> I'm running Java 1.4.2 with the JVM parameter -Xmx64m.
>>>
>>> If you run the test() method,  the memory used by the JVM  rises to
>>> above 150mb until the following exception happens:
>>
>>
>>
>> Thanks for the test case.
>>
>> I noticed that you are not using PreparedStatements for the query or the
>>  insert. Using PreparedStatements will make your program much faster, as
>> the statement does not need to be recompiled every time. This is true
>> for all JDBC drivers.
>>
>> See
>>
>> http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705 
>>
>>
>> E.g.
>>
>> PreparedStatement psq = conn.prepareStatement("SELECT id FROM test WHERE
>> id = ?");
>> psq.setInt(1, id);
>> ResultSet rs = psq.executeQuery();
>>
>>
>> Your code uses a new Statement object to execute every statement, which
>> can also be avoided since Statement objects are reuseable. Though I
>> would recommend PreparedStatements.
>>
>> Also your code never closes the Statement or ResultSet objects it
>> creates, this may be contributing to the problem.
>>
>> Applying the above techniques may allow you to progress past this issue,
>> though it should still be seen as a bug.
>>
>> Dan.
>>
>>
>>
>>
>>
>