Re: getGeneratedKeys method - Mailing list pgsql-jdbc

Hi Oliver,

The increment by N aproach, where N < batch size, is
imho very original. It should work but the DBAppender
code has to be compatible with different RDBMSs. The
approach you suggest assumes that we can manually set
event_id, which is not necessarily always the case, or
maybe it is, but I am not sure. More importantly, on
some RDBMS we are using auto-generated keys where it
is not always possible to set the increment value.
(Maybe it is always possible, I'll have to check that
too.)

Anyway, I now know when an increment value other than
1 could be useful.

Thanks again for your help.

--- Oliver Jowett <oliver@opencloud.com> wrote:
> Ceki Gulcu wrote:
> > Hello,
> >
> > Thanks for all the replies received so far. I
> really
> > appreciate it.
> >
> > One important point I failed to mention in my
> original
> > mail, was that we are using a prepared statement
> to
> > perform the initial batch of inserts via
> executeBatch,
> > followed by another prepared statement and
> invoking
> > executeBatch and a third prepared
> > statement+executeBatch.
> >
> > Your suggestions imply the use of regular
> statements
> > as opposed to prepared statements. I don't know
> > whether the performance boost is due to the use of
> > prepared statements or whether it's more the
> > "batching", that is regrouping the SQL statements
> in
> > one transaction.
>
> I initially thought you could batch the inserts in
> the approach I
> suggested, but of course addBatch and executeBatch
> live on a particular
> PreparedStatement so you can't do that.
>
> Ok, how about something like this instead:
>
>    CREATE SEQUENCE id INCREMENT BY 100;
>
> Then limit batch size to 100, and for each batch do:
>
>    SELECT nextval('id')
>    -- let's assume this returns 1000
>
> Next do the actual batch inserts, grouped by
> statement and computing ID
> values yourself based on the returned nextval +
> offset:
>
>    INSERT INTO logging_event VALUES (1000, ...)
>    INSERT INTO logging_event VALUES (1001, ...)
>    INSERT INTO logging_event VALUES (1002, ...)
>    -- ...
>    INSERT INTO logging_event VALUES (1099, ...)
>
>    INSERT INTO logging_event_property VALUES (1000,
> 'foo', ...)
>    INSERT INTO logging_event_property VALUES (1000,
> 'bar', ...)
>    INSERT INTO logging_event_property VALUES (1001,
> 'foo', ...)
>    INSERT INTO logging_event_property VALUES (1001,
> 'bar', ...)
>    -- etc
>
>    INSERT INTO logging_event_exception VALUES (1000,
> 'line 1', ...)
>    INSERT INTO logging_event_exception VALUES (1000,
> 'line 2', ...)
>    INSERT INTO logging_event_exception VALUES (1001,
> 'line 1', ...)
>    INSERT INTO logging_event_exception VALUES (1001,
> 'line 2', ...)
>    -- etc
>
> Because of the INCREMENT BY clause, the generated
> IDs won't collide with
> another concurrent inserter. This should let you use
> both
> PreparedStatement and batch execution I think.
>
> There is a tradeoff between rate of ID consumption
> and maximum batch
> size to be made, but if you're using int8 for IDs
> then ID consumption is
> unlikely to be an issue..
>
> -O





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/