Stored procedures when and how: was: Sun acquires MySQL

On Mon, 21 Jan 2008 21:31:23 -0800
johnf <jfabiani(at)yolo(dot)com> wrote:

> On Monday 21 January 2008 04:47:40 pm Tom Lane wrote:

> > 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.

Yep, the more I read, the more I get confused.
Java loading overhead is a common myth (I can't say if true or false),
and what Tom writes above can find a tentative place in my mind.
But still then I can't understand where plsql should or shouldn't be
used.

I really would enjoy to see some general guideline on how to chose.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it