PostgreSQL Performance - Prepared statements vs. Stored Procedures

Author

Prepared statements vs. Stored Procedures

Oliver Crosby

2005-06-21, 8:24 pm

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.

Tobias Brox

2005-06-21, 8:24 pm

[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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Radu-Adrian Popescu

2005-06-22, 3:24 am

> 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.
>

You'll definitely gain some performance if you manage to group several
operations that are executed in a sequence - into a stored procedure. The
principle here is that you'd be reducing the number of round-trips to the
database server.
As an example assume you start a transaction, lock several rows in
different tables for update (thereof), update fields and then commit. If
this is done in a sequencial manner - whether this is perl or java/jdbc or
libpq - you'll require several round-trips to the server and also fetch
several bits and pieces to the application. If this can be rewritten as a
stored procedure that receives the data/parameters it needs in order to
complete its work and does the whole thing in one go you'll definitely see
an improvement as ther will be a single call to the database and you'll
move (much) less data between the server and the application.
On the other hand if you're mostly fetching data I doubt you'll be able to
gain anything from changing to stored procedures.
I believe a good rule of thumb is this: change data, several related
operations, very simple processing involved -> stored procedure. Read data
as in a reporting scenario -> prepared statements. Obviously if you're
reading data in several steps and then aggregate it in the application
then perhaps you need to make better use of SQL :)

I hope this helps,
Regards,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links