From: | "Radu-Adrian Popescu" <radu(dot)popescu(at)aldratech(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Prepared statements vs. Stored Procedures |
Date: | 2005-06-22 07:24:15 |
Message-ID: | 52490.193.138.218.24.1119425055.squirrel@www.aldratech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Kjell Tore Fossbakk | 2005-06-22 07:45:22 | Re: Querying 19million records very slowly |
Previous Message | Colin Taylor | 2005-06-22 06:12:34 | slow simple update? |