Re: getGeneratedKeys method - Mailing list pgsql-jdbc

Ceki,

Couple of things:

1) alot of persistence layers use a GenerateKey interface to generate
the key ahead of time, that way you can use multiple implementations for
whichever db you use.

2) MSSQL for sure will not let you insert any value into the serial
column.

3) there's yet another option for serial values in postgres which is
cacheing, if you were going to increment by 10, you may wish to cache by
100 which means that for each connection it will only have to write to
the backend 1 time for every 10 fetch's

Dave
On Thu, 2004-05-27 at 09:50, Ceki Gulcu wrote:
> 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
>
> !DSPAM:40b5f32a168261639319129!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561