From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Stored Procedure Performance
Date: Tue, 11 Apr 2006 09:56:27 -0400
Message-ID: <87acasjjw4.fsf@wolfe.cbbrowne.com> (raw)
References: <416F947BF5BA9E40B5F9AA649422212E074DE930@ex-mail2.internal.rmplc.net>
sdale@rm.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@~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/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: Stored Procedure Performance
In-Reply-To: <87acasjjw4.fsf@wolfe.cbbrowne.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox