Обсуждение: Batch with keygen?

Hi everyone,
I've got a bunch of PreparedStatements doing inserts on tables with
primary keys generated by sequences. For example, insert on table A,
take generated primary key, insert on table B assigning foreign key
generated value for A's primary key.

For performance reasons, I need to batch these commands. But I don't see
how it would be possible to continue using this approach with a batch of
commands, because I need the results of the first insert to make the
second insert. Is there some way to do this or am I going to have to
stop using keygen and instead have my application generate its own keys?

Thanks.

Michael R. Clements
Principal Architect, Actional Corp.
mclements@actional.com
FREE! Actional SOAPstation Developer Version
Web services routing, security, transformation and versioning
http://www.actional.com/sstdownload

On Mon, 7 Nov 2005, Mike Clements wrote:

> I've got a bunch of PreparedStatements doing inserts on tables with
> primary keys generated by sequences. For example, insert on table A,
> take generated primary key, insert on table B assigning foreign key
> generated value for A's primary key.
>
> For performance reasons, I need to batch these commands. But I don't see
> how it would be possible to continue using this approach with a batch of
> commands, because I need the results of the first insert to make the
> second insert. Is there some way to do this or am I going to have to
> stop using keygen and instead have my application generate its own keys?
>

One option would be to tune your sequence generator to your batch size,
consider: CREATE SEQUENCE myseq INCREMENT BY 100;  Then if you fetch a
nextval you know that you are also free to use the next 99 values as well
in your batch statement without touching the sequence.

Kris Jurka

Additionally you should be aware, that using this mechanism once a
connection gets a hundred values, they are consumed even if you don't
use them.

Dave
On 7-Nov-05, at 4:18 PM, Kris Jurka wrote:

>
>
> On Mon, 7 Nov 2005, Mike Clements wrote:
>
>> I've got a bunch of PreparedStatements doing inserts on tables with
>> primary keys generated by sequences. For example, insert on table A,
>> take generated primary key, insert on table B assigning foreign key
>> generated value for A's primary key.
>>
>> For performance reasons, I need to batch these commands. But I
>> don't see
>> how it would be possible to continue using this approach with a
>> batch of
>> commands, because I need the results of the first insert to make the
>> second insert. Is there some way to do this or am I going to have to
>> stop using keygen and instead have my application generate its own
>> keys?
>>
>
> One option would be to tune your sequence generator to your batch
> size, consider: CREATE SEQUENCE myseq INCREMENT BY 100;  Then if
> you fetch a nextval you know that you are also free to use the next
> 99 values as well in your batch statement without touching the
> sequence.
>
> Kris Jurka
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

This looks like a typical example that will benefit from a stored procedure.  Its a bit more work, but much better performace.  You can then batch any ammount of inserts with very good results

Creating your own keys is normally not a good idea.

Maybe I am a bit off, but the old friends curval() and nextval() don't do the
trick? I mean

con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO TABLEA " +
              "VALUES(nextval('TABLEA_PK_SEQ'), 1, 2, 3)");
stmt.addBatch("INSERT INTO TABLEB " +
              "VALUES(curval('TABLEA_PK_SEQ'), 4, 5, 6)");
...
int [] updateCounts = stmt.executeBatch();

Bye
Nelson Arapé
PS: sorry for my English

El Lun 07 Nov 2005 20:01, Dave Cramer escribió:
> Additionally you should be aware, that using this mechanism once a
> connection gets a hundred values, they are consumed even if you don't
> use them.
>
> Dave
>
> On 7-Nov-05, at 4:18 PM, Kris Jurka wrote:
> > On Mon, 7 Nov 2005, Mike Clements wrote:
> >> I've got a bunch of PreparedStatements doing inserts on tables with
> >> primary keys generated by sequences. For example, insert on table A,
> >> take generated primary key, insert on table B assigning foreign key
> >> generated value for A's primary key.
> >>
> >> For performance reasons, I need to batch these commands. But I
> >> don't see
> >> how it would be possible to continue using this approach with a
> >> batch of
> >> commands, because I need the results of the first insert to make the
> >> second insert. Is there some way to do this or am I going to have to
> >> stop using keygen and instead have my application generate its own
> >> keys?
> >
> > One option would be to tune your sequence generator to your batch
> > size, consider: CREATE SEQUENCE myseq INCREMENT BY 100;  Then if
> > you fetch a nextval you know that you are also free to use the next
> > 99 values as well in your batch statement without touching the
> > sequence.
> >
> > Kris Jurka
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

Thanks everyone for all the ideas. I am going the route of stored procedures. This lets me keep my DB generated keys,
whilealso greatly reducing the number of SQL round trips, which will improve performance, all without denormalizing the
schema.

I was hoping to avoid using stored procedures because we support multiple different DBs so it means writing them on
variousdifferent platforms. But it looks like this is the only real option. 


> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Nelson Arape
> Sent: Tuesday, November 08, 2005 4:50 AM
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Batch with keygen?
>
> Maybe I am a bit off, but the old friends curval() and
> nextval() don't do the
> trick? I mean
>
> con.setAutoCommit(false);
> Statement stmt = con.createStatement();
> stmt.addBatch("INSERT INTO TABLEA " +
>               "VALUES(nextval('TABLEA_PK_SEQ'), 1, 2, 3)");
> stmt.addBatch("INSERT INTO TABLEB " +
>               "VALUES(curval('TABLEA_PK_SEQ'), 4, 5, 6)");
> ...
> int [] updateCounts = stmt.executeBatch();
>
> Bye
> Nelson Arapé
> PS: sorry for my English
>
> El Lun 07 Nov 2005 20:01, Dave Cramer escribió:
> > Additionally you should be aware, that using this mechanism once a
> > connection gets a hundred values, they are consumed even if
> you don't
> > use them.
> >
> > Dave
> >
> > On 7-Nov-05, at 4:18 PM, Kris Jurka wrote:
> > > On Mon, 7 Nov 2005, Mike Clements wrote:
> > >> I've got a bunch of PreparedStatements doing inserts on
> tables with
> > >> primary keys generated by sequences. For example, insert
> on table A,
> > >> take generated primary key, insert on table B assigning
> foreign key
> > >> generated value for A's primary key.
> > >>
> > >> For performance reasons, I need to batch these commands. But I
> > >> don't see
> > >> how it would be possible to continue using this approach with a
> > >> batch of
> > >> commands, because I need the results of the first insert
> to make the
> > >> second insert. Is there some way to do this or am I
> going to have to
> > >> stop using keygen and instead have my application
> generate its own
> > >> keys?
> > >
> > > One option would be to tune your sequence generator to your batch
> > > size, consider: CREATE SEQUENCE myseq INCREMENT BY 100;  Then if
> > > you fetch a nextval you know that you are also free to
> use the next
> > > 99 values as well in your batch statement without touching the
> > > sequence.
> > >
> > > Kris Jurka
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 6: explain analyze is your friend
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >        choose an index scan if your joining column's
> datatypes do not
> >        match
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>