On 03/08/2011 10:01 AM, malte.kem...@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
