db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Pouttu-Clarke <Matt.Pouttu-Cla...@icrossing.com>
Subject Re: AW: Performance question, Closing Prepared statements
Date Wed, 09 Mar 2011 15:51:51 GMT
I have no problems doing millions of inserts with one commit at the end in
embedded Derby.  Please see my blog for some code details on how to make it


On 3/9/11 3:40 AM, "Malte.Kempff@de.equens.com" <Malte.Kempff@de.equens.com>

> Hi bryan,
> Thanks a lot for your hints.
> I am using derby embedded in my application and it is just one table. I first
> thought of doing just one commit for a load, but I guess that is probably not
> so well, since for the initial load there are 101004 records each 514 bytes
> long to be filled. Is there any derby rule or good practice how many commits
> are to be done in respect of how much memory is assigned to the JVM derby
> resides in?
> Or is it better do something like sync-points?
> Best regards and thanks for further hints
> Malte
> -----Ursprüngliche Nachricht-----
> Von: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
> Gesendet: Mittwoch, 9. März 2011 05:30
> An: Derby Discussion
> Betreff: Re: Performance question, Closing Prepared statements
> On 03/08/2011 10:01 AM, Malte.Kempff@de.equens.com wrote:
>> close prepared statement after each operation, because there might be a
>> pooling
>> within derby or derby driver for those statements, or should I
>> rather leave those three kinds of prepared statements (insert,
>> update delete) open till my manipulation is full done or breaks down for some
>> error?
> It is fine to have a number of prepared statements, and to leave
> them open for a while. It is also fine to close them and re-prepare
> them; if the literal SQL text string is identical, Derby has a
> cache which should re-prepare the statement very quickly.
> Sometimes it is *necessary* to close and re-prepare a prepared
> statement, and *not* use the one that Derby has cached, because
> the table structure has changed so much in the meantime that
> a new query plan needs to be chosen. In that case, you can tweak
> the statement slightly (I sometimes add some extra whitespace, or
> change the upper/lower case of a keyword) to force Derby to recompile.
> Regarding the overall performance, if your program is single-threaded
> and is having direct access to the database, I would advise:
> 1) Give Derby as much memory as possible. Use -Xmx and specify as
> much memory as you can without making your system start to page.
> 2) Use the embedded driver rather than the network server driver,
> to shorten the overall data path and reduce the amount of data movement.
> 3) Try to break your work into as few transactions as possible. The thing
> that limits your overall throughput is probably the number of commit's
> that you have to do, so do as few as possible.
> Hope this helps; let us know how your work goes and I'm sure the
> list would be glad to offer more ideas.
> thanks,
> bryan

iCrossing Privileged and Confidential Information
This email message is for the sole use of the intended recipient(s) and may contain confidential
and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution
is prohibited. If you are not the intended recipient, please contact the sender by reply email
and destroy all copies of the original message.

View raw message