Re: Stored Procedure Performance

sdale(at)rm(dot)com ("Simon Dale") wrote:
> <p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
> font-family:Arial'>Event with the planning removed, the function still performs
> significantly slower than the raw SQL. Is that normal or am I doing something wrong
> with the creation or calling of the
> function?<o:p></o:p></span></font></p>

I'd expect this, yes.

You're doing something via "stored procedure logic" that would be done
more directly via straight SQL; of course it won't be faster.

In effect, pl/pgsql involves (planning once) then running each line of
logic. In effect, you replaced one query (select * from some table)
into 90 queries. Yup, there's extra cost there.

There's not some "magic" by which stored procedures provide results
faster as a natural "matter of course;" the performance benefits
generally fall out of two improvements:

1. You eliminate client-to-server round trips.

A stored proc that runs 8 queries saves you 8 round trips over
submitting the 8 queries directly. Saving you latency time.

2. You can eliminate the marshalling and transmission of unnecessary
data.

A stored proc that runs 8 queries, and only returns summarized
results that all come from the last table queried will eliminate
the need to marshall and transmit (possibly over a slow link) the
data for the 7 preceding queries.

The case that you tried can benefit from neither of those effects;
your stored procedure eliminates NO round trips, and NO
marshalling/transmission.
--
(format nil "~S(at)~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/rdbms.html
Rules of the Evil Overlord #228. "If the hero claims he wishes to
confess in public or to me personally, I will remind him that a
notarized deposition will serve just as well."
<http://www.eviloverlord.com/>