Stored Procedures - slower?

I had an app that was doing groups of 14 inserts into some related tables, each as a separate SQL call. I wrote a stored procedure to do all 14 inserts and now the app makes a single call to the stored procedure, doing the same thing with a single round trip to the database. The end result: it's SLOWER!

I've confirmed by analyzing tables & rows that both techniques do the exact same thing, but doing it with a single call to a stored procedure consistently takes 1.47 times as long - that is to say, it has about 68% of the throughput.

This is done using a JDBC CallableStatement to invoke the stored procedure.

This is so "impossible" I've been testing all day only to verify it. Any ideas?

> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Mike Clements
> Sent: Tuesday, November 08, 2005 8:12 AM
> To: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Batch with keygen?
>
> 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(at)postgresql(dot)org
> > [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Nelson Arape
> > Sent: Tuesday, November 08, 2005 4:50 AM
> > To: pgsql-jdbc(at)postgresql(dot)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
> >
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> so that your
> message can get through to the mailing list cleanly
>
>