Date: Tue, 21 Jun 2005 22:40:57 +0200
From: Tobias Brox <tobias@nordicbet.com>
To: Oliver Crosby <ryusei@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Prepared statements vs. Stored Procedures
Message-ID: <20050621204057.GI22746@oppetid.no>
References: <1efd553a05062112462c30e9ff@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1efd553a05062112462c30e9ff@mail.gmail.com>
Organization: Group Nordicbet
User-Agent: Mutt/1.5.9i

[Oliver Crosby - Tue at 03:46:03PM -0400]
> I'm hoping someone can offer some advice here.
>  I have a large perl script that employs prepared statements to do all its 
> queries. I'm looking at using stored procedures to improve performance times 
> for the script. Would making a stored procedure to replace each prepared 
> statement be worthwhile? If not, when could I use stored procedures to 
> improve performance?
>  Thanks in advance.

My gut feeling says that if you are only doing read-operations there are
none or almost none benefits with stored procedures.

One argument we used for not looking much into stored procedures, was
that we expect the database to become the bottleneck if we get too much
activity.  At the application side, we can always expand by adding more
boxes, but the database, beeing the hub of the system, cannot easily be
expanded (we can tweak and tune and upgrade the whole box, and
eventually at some point we believe we will need to put old data at a
separate database, and also make a replica for heavy report queries)

If you have loads of data going from the database to the application, a
little bit of light processing done on the data, and then data going
back to the database server, then I guess stored procedures would be
better.