PostgreSQL JDBC - Batch with keygen?

Author

Batch with keygen?

Mike Clements

2005-11-08, 4:12 pm

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Kris Jurka

2005-11-08, 4:12 pm

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

Dave Cramer

2005-11-08, 4:12 pm

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:
>
>
> 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

Andres Olarte

2005-11-08, 4:12 pm

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.

Nelson Arape

2005-11-08, 4:12 pm

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('TABL
EA_PK_SEQ'), 1, 2, 3)");
stmt.addBatch("INSERT INTO TABLEB " +
" VALUES(curval('TABLE
A_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:
>
> ---------------------------(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

Mike Clements

2005-11-08, 4:12 pm

Thanks everyone for all the ideas. I am going the route of stored procedures. This lets me keep my DB generated keys, while also 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 various different 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('TABL
EA_PK_SEQ'), 1, 2, 3)");
> stmt.addBatch("INSERT INTO TABLEB " +
> " VALUES(curval('TABLE
A_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ó:
> you don't
> tables with
> on table A,
> foreign key
> to make the
> going to have to
> generate its own
> use the next
> broadcast)---------------------------
> datatypes do not
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links