Re: Stored procedures when and how: was: Sun acquires MySQL

On Jan 22, 2008 2:24 AM, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> 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.

It's fairly trivial to test performance of functions vs. raw
statements, or just about anything going on with the server. The
benchmarking tool, pgbench, allows custom sql which is great for
things like this. It would have shown you that functions themselves
are not the reason why your application was not running quickly. My
seat of the pants guess (I don't do java) was that your problem was in
the jdbc driver somewhere. When using a high level database framework
like jdbc or ado.net, it can be difficult to figure out exactly what
is going on with the database at times...I tend to avoid them.

merlin