| Mike Clements 2005-11-08, 8:25 pm |
| 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 t
rip 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@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements
> Sent: Tuesday, November 08, 2005 8:12 AM
> To: pgsql-jdbc@postgresql.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.
>
>
> your batch
> your desire to
>
> ---------------------------(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
>
>
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|