Re: Speed up single INSERT INTO statement?
Consider batching inserts and use larger log file. On 9 May 2012 19:01, "TXVanguard" wrote: > > I need to speed up a single INSERT statement in Derby. > > The statement has the form: > > INSERT INTO table (col1, col2) SELECT a, b FROM > > In my application, it take about 10 seconds to insert 3000 records. > > I have experimented with turning off autocommit, adjusting > derby.storage.pageCacheSize and derby.storage.pageSize, turning off indexes > for the table, and a few other things, but nothing seems to affect the > speed > of the INSERT. > > Are there any other techniques I can try? Would it be helpful to > temporarily turn off constraints for the table? > -- > View this message in context: > http://old.nabble.com/Speed-up-single-INSERT-INTO-statement--tp33763645p33763645.html > Sent from the Apache Derby Users mailing list archive at Nabble.com. > >
Re: Speed up single INSERT INTO statement?
The changes that I found most effective when it comes to speeding up inserts: 1 - Run database with "test" durability 2 - Add all of your data before adding your primary key and foreign key constraints, if possible 3 - Use prepared statements 4 - Execute prepared statements in batches, rather than one at a time When I was optimizing inserts, I got it down to about 10-20 row inserts per millisecond, so you can definitely improve your performance. You may not be able to get anywhere near that performance, though, depending on what is actually in that select statement. It may be worth your time to run the select statement separately from your insert statement so that you can figure out how to speed up each one separately. In my experience, it is often easier to use a vanilla INSERT INTO TABLE (c...) VALUES (?,...), and then have a bit of glue Java code to run the select statement and call the insert statement w/ the appropriate values as the select statement returns data. On Wed, May 9, 2012 at 1:00 PM, TXVanguard wrote: > > I need to speed up a single INSERT statement in Derby. > > The statement has the form: > > INSERT INTO table (col1, col2) SELECT a, b FROM > > In my application, it take about 10 seconds to insert 3000 records. > > I have experimented with turning off autocommit, adjusting > derby.storage.pageCacheSize and derby.storage.pageSize, turning off indexes > for the table, and a few other things, but nothing seems to affect the speed > of the INSERT. > > Are there any other techniques I can try? Would it be helpful to > temporarily turn off constraints for the table? > -- > View this message in context: > http://old.nabble.com/Speed-up-single-INSERT-INTO-statement--tp33763645p33763645.html > Sent from the Apache Derby Users mailing list archive at Nabble.com. >
Re: Speed up single INSERT INTO statement?
Sorry Brett, I'm not responding to your question but enganged by your "Would it be helpful to temporarily turn off constraints for the table?" AFAIK it's not possible to do this. Was it a question or you know how to do it? If so, point me out, please. Cheers, Libor On Wed, May 9, 2012 at 7:00 PM, TXVanguard wrote: > > I need to speed up a single INSERT statement in Derby. > > The statement has the form: > > INSERT INTO table (col1, col2) SELECT a, b FROM > > In my application, it take about 10 seconds to insert 3000 records. > > I have experimented with turning off autocommit, adjusting > derby.storage.pageCacheSize and derby.storage.pageSize, turning off indexes > for the table, and a few other things, but nothing seems to affect the > speed > of the INSERT. > > Are there any other techniques I can try? Would it be helpful to > temporarily turn off constraints for the table? > -- > View this message in context: > http://old.nabble.com/Speed-up-single-INSERT-INTO-statement--tp33763645p33763645.html > Sent from the Apache Derby Users mailing list archive at Nabble.com. > >
Speed up single INSERT INTO statement?
I need to speed up a single INSERT statement in Derby. The statement has the form: INSERT INTO table (col1, col2) SELECT a, b FROM In my application, it take about 10 seconds to insert 3000 records. I have experimented with turning off autocommit, adjusting derby.storage.pageCacheSize and derby.storage.pageSize, turning off indexes for the table, and a few other things, but nothing seems to affect the speed of the INSERT. Are there any other techniques I can try? Would it be helpful to temporarily turn off constraints for the table? -- View this message in context: http://old.nabble.com/Speed-up-single-INSERT-INTO-statement--tp33763645p33763645.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
