Re: [PERFORMANCE] Stored Procedures

On Fri, Jan 20, 2006 at 07:50:23PM +0100, Rikard Pavelic wrote:
> >>Hi,
> >>
> >>Will simple queries such as "SELECT * FROM blah_table WHERE tag='x'; work
> >>any
> >>faster by putting them into a stored procedure?
>
> >
> >IMHO no, why do you think so? You can use PREPARE instead, if you have many
> >selects like this.
>
>
> I tought that creating stored procedures in database means
> storing it's execution plan (well, actually storing it like a
> compiled object). Well, that's what I've learned couple a years
> ago in colledge ;)

My college professor said it, it must be true! ;P

My understanding is that in plpgsql, 'bare' queries get prepared and act
like prepared statements. IE:

SELECT INTO variable
field
FROM table
WHERE condition = true
;

> What are the advantages of parsing SP functions every time it's called?
>
> My position is that preparing stored procedures for execution solves
> more problems, that it creates.
> And the most important one to be optimizing access to queries from
> multiple connections (which is one of the most important reasons
> for using stored procedures in the first place).

Ok, so post some numbers then. It might be interesting to look at the
cost of preparing a statement, although AFAIK that does not store the
query plan anywhere.

In most databases, query planning seems to be a pretty expensive
operation. My experience is that that isn't the case with PostgreSQL.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461