On 07.12.2012 16:53, Pavel Bortnovskiy wrote: > Thank you, Knut. > It has been my assumption that creation of PreparedStatement is expensive. > Thus I create cached Prepared Statements (such as INSERT, UPDATE, DELETE, > SELECT COUNT(*) FROM <table>) and then use them. Would it be an improvement > in my design to create multiple PreparedStatements for those (assuring proper > addition of objects and execution)? Would you still recommend caching and > reusing them or creating them anew? Please also note that as long as the statements are textually the same, i.e. arguments are given dynamically as "?" , Derby is able to use its internal cache to avoid SQL compilation for more than the first thread, even if each of the thread has a created a separate PreparedStatement in its own connection. Cf. http://db.apache.org/derby/docs/10.9/ref/rrefproperstatementcachesize.html
Thanks, Dag > > For instance, if I have 3,000 records to insert, I could split them into > 6x500 batches, create 6 PreparedStatements and have each of them execute > their corresponding batches in parallel. > > Thank you, > Pavel. > > -----Original Message----- > From: Knut Anders Hatlen [mailto:knut.hat...@oracle.com] > Sent: Friday, December 07, 2012 7:01 AM > To: Derby Discussion > Subject: Re: concurrent execution of Statement's .addBatch() and > .executeBatch() > > Pavel Bortnovskiy <pbortnovs...@jefferies.com> writes: > >> Hello: >> >> Is it safe to call .addBatch() and .executeBatch() methods from >> multiple threads on the same PreparedStatement? >> >> Simple example: batching and executing a large number of the same >> statements (with the same PreparedStatement) by using ExecutorService. > Hi Pavel, > > Both addBatch() and executeBatch() do their work in a block synchronized on > the connection, so in theory it should work to have many threads adding > batches to the same PreparedStatement. I don't think it has been heavily > tested, though. > > And if the threads set any parameters on the PreparedStatement, your > application needs to synchronize the threads manually. For example, if two > threads call > > ps.setInt(1, id); > ps.setObject(2, value); > ps.addBatch(); > > on the same statement, you need to add synchronization to ensure that the > addBatch() call in one thread doesn't end up using one or more parameter > values set by the other thread. > > -- > Knut Anders > > Jefferies archives and monitors outgoing and incoming e-mail. The contents of > this email, including any attachments, are confidential to the ordinary user > of the email address to which it was addressed. If you are not the addressee > of this email you may not copy, forward, disclose or otherwise use it or any > part of it in any form whatsoever. This email may be produced at the request > of regulators or in connection with civil litigation. Jefferies accepts no > liability for any errors or omissions arising as a result of transmission. > Use by other than intended recipients is prohibited. In the United Kingdom, > Jefferies operates as Jefferies International Limited; registered in England: > no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, > London EC4V 3BJ. Jefferies International Limited is authorised and regulated > by the Financial Services Authority.
