tuning large numbers of of insert/updates

[Jon Tirsen <jo...@tirsen.com>]

I'm looking for advise on tuning import-style transactions with large
numbers of inserts and updates. Except for the usual (auto commit off,
prepared statements) I can find very little in the online
documentation. For example: Will JDBC batching give any benefits when
using an embedded server? Is there some way of temporarily suspending
index updating? Any other suggestions? Any online resources to look
at?

Thanks for any help.

Cheers,
Jon



----
New from ThoughtWorks: Mingle, an Agile project management application.
Mingle. Project Intelligence. Powerfully Simple.
More at http://studios.thoughtworks.com

Re: tuning large numbers of of insert/updates

[Stanley Bradbury <St...@gmail.com>]

Jon Tirsen wrote:
> I'm looking for advise on tuning import-style transactions with large
> numbers of inserts and updates. Except for the usual (auto commit off,
> prepared statements) I can find very little in the online
> documentation. For example: Will JDBC batching give any benefits when
> using an embedded server? Is there some way of temporarily suspending
> index updating? Any other suggestions? Any online resources to look
> at?
>
> Thanks for any help.
>
> Cheers,
> Jon
>
>
>
> ----
> New from ThoughtWorks: Mingle, an Agile project management application.
> Mingle. Project Intelligence. Powerfully Simple.
> More at http://studios.thoughtworks.com
>
Hi Jon -
I haven't tested batching statements with the embedded driver but I 
can't imagine it would hurt.  If you test this would you please post 
your results to the list?

As you suggested, for large quantity inserts dropping the indexes then 
recreating them after the process completes can help.  Using the IMPORT 
procedure is also a good way to perform inserts.  Much of the work 
performed involved with large quantity inserts and updates is space 
allocation.  This can minimized by preallocating space for the table by 
setting these properties before the table(s) is/are created:

derby.storage.initialPages
derby.storage.pageReservedSpace

HTH