On Monday 21 January 2008 04:47:40 pm Tom Lane wrote:
> Guy Rouillier <guyr-ml1@burntmail.com> writes:
> > Unfortunately, I think the stored procedure implementation in PG itself
> > introduces significant overhead. See thread "Writing most code in
> > Stored Procedures" from August 2007. I converted an application from
> > that BigDBMS we are not allowed to mention to PG. Code is Java, stored
> > procs were written in PL/Java. On the exact same hardware, I couldn't
> > get any where near the throughput I was getting in BigDBMS. The procs
> > are trivial - just wrappers for insert statements. After I exhausted
> > all alternatives, I replaced the stored proc invocation in the code with
> > inserts. Then, PG was able to achieve the same throughput as BigDBMS.
>
> I doubt that what you were measuring there was either procedure call
> overhead or java computational speed; more likely it was the cost of
> calling back out of java, through pl/java's JDBC emulation, down through
> SPI, to re-execute the same INSERT that you then decided to execute
> directly. In particular, if pl/java's JDBC doesn't know anything about
> caching query plans, performance for simple inserts could be expected to
> go into the tank just because of that. (Whether it actually does or
> not, I have no idea --- but I would expect it to be a lot less mature
> than the mainstream JDBC driver for PG, and that took years to get
> smart about prepared queries ...)
>
> Without knowing where the bottleneck actually is, it's unreasonable to
> assume that it would hurt a different use-case.
Tom,
I have read several of your post on store procedure performance. Why not give
us your take on what works and what does not.
--
John Fabiani