Re: Stored Procedures Performance: Java vs. PL/Pgsql

Daniel.Kastenholz@myTUM.de (Daniel Kastenholz) writes:
> Hi,
>
> is there a rule of thumb or an estimate which language provides the
> best performance for stored procedures?
>
> I'm currently using PL/Pgsql, and wondering
> - to what extent these stored procedures are actually precompiled
> and/or optimized
> - if Java might provide better performance, or if parameter
> transformation and VM calls eat up the performance gains (if any)
>
> I'm aware that these questions can't be answered in a "universally
> valid" fashion, but I would appreciate your experiences.

The sorts of things I'd expect Java SPs to do better would be the
handling of complex arithmetic computations.

Notably, you can use native Java types to calculate things; doing the
equivalents in pl/pgsql would require iterating across sets of
interpreted SQL statements.

If your code is not "rich" in that, then Java won't provide advantage
in that area.

A place where I'd expect Java to be an anti-optimization (as compared
to pl/pgsql) is that there's little opportunity for the query
optimizer to penetrate into the code to be able to optimize usage of
the stored procedure.  As pl/pgsql is a pretty thin veneer atop SQL,
there is more that the query optimizer can do, analytically...

I'm speaking analytically here; I haven't used Java SPs.  I have used
various other SPs, though, and this sort of result does normally hold
true.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www3.sympatico.ca/cbbrowne/emacs.html
(1) Sigs are preceded by the "sigdashes" line, ie "\n-- \n" (dash-dash-space).
(2) Sigs contain at least the name and address of the sender in the first line.
(3) Sigs are at most four lines and at most eighty characters per line.