Thread: 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.
Hi All,
Suppose I have a store proc which accepts 4 input parameters
and I do a CallableStatement.setObject( 1, "..,,," );
and execute the query .
It gives me that number of arguments need 4, but found only 1
(java.sql.SQLException:Incorrect number of arguments for PROCEDURE FHLMC.test1; expected 4, got 1)
during runtime. Should i set the other 3 input parameters to null,
even when I dont want to pass these parameters to the SP.
Thks
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
>
>