Re: How many Statements to batch

Ellarco wrote:
> I am performing millions of inserts using PreparedStatements and
> executing them in batches. My question is simply how big should my
> batch be?

IMHO there's no general answer to this as this depends on the driver, the
JVM, amount of mem you have available, the database engine, resources on
the db server, DDL, indexes...

> Im assuming that attempting to batch millions of them at a time is
> bad from a GC point of view ... they are going to be taking up memory
> for long periods of time (until the batch "is full") causing the GC
> to lots of slow full (old-generation) collections.

There's a completely different set of problems: if your batch grows too
large, the database transaction handling might choke. The larger your
batches the more likely that you reach limits on the rollback segments
(Oracle) or run into locking issues.

> So has anyone got any research/experience that suggests an upperlimit
> on batch size? Or perhaps its better for the batch to be executed
> periodically rather than when a certain number of statements have
> been added?

This is a typical optimization task. I'm afraid you'll have to measure
your performance and find out the optimum for *your* application case. I
guess the range will be between 100 and several tenthousands - but this is
just a wild guess.

Kind regards

robert

.

Relevant Pages

  • Re: How many Statements to batch
    ... >> I am performing millions of inserts using PreparedStatements and ... >> executing them in batches. ... >> Im assuming that attempting to batch millions of them at a time is ...
    (comp.lang.java.databases)
  • Re: Problem when executing application from batch file
    ... I think that your app maybe ... If you see 4 copies of the MsgBox, then the batch file is executing as ... >> Apparently this is only supported in XP and most likely Windows 2000. ...
    (microsoft.public.vb.general.discussion)
  • Re: Bulk Insert
    ... Make sure that you are executing your batch inside of a transaction, ... way the server will only commit the data once improving performance. ... parameters of the batch, for example for Sql Server 2000 you can pass a max ... Please do not send email directly to this alias. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem when executing application from batch file
    ... My batch file has 4 lines. ... > Apparently this is only supported in XP and most likely Windows 2000. ... >>> returned immediately before they finished executing. ...
    (microsoft.public.vb.general.discussion)
  • Re: xp_cmdshell and vb programs and SQL Server 2000
    ... VB can be launched from batch and terminates without user ... terminate after executing. ... Not working on SQL2000 and Windows ...
    (microsoft.public.sqlserver.programming)