Re: Sun acquires MySQL

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