pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPrepared statements vs. Stored Procedures
3+ messages / 3 participants
[nested] [flat]
* Prepared statements vs. Stored Procedures
@ 2005-06-21 19:46 Oliver Crosby <ryusei@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Oliver Crosby @ 2005-06-21 19:46 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Prepared statements vs. Stored Procedures
@ 2005-06-21 20:40 Tobias Brox <tobias@nordicbet.com>
parent: Oliver Crosby <ryusei@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tobias Brox @ 2005-06-21 20:40 UTC (permalink / raw)
To: Oliver Crosby <ryusei@gmail.com>; +Cc: pgsql-performance
[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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Prepared statements vs. Stored Procedures
@ 2005-06-22 07:24 Radu-Adrian Popescu <radu.popescu@aldratech.com>
parent: Oliver Crosby <ryusei@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Radu-Adrian Popescu @ 2005-06-22 07:24 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-22 07:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-21 19:46 Prepared statements vs. Stored Procedures Oliver Crosby <ryusei@gmail.com>
2005-06-21 20:40 ` Tobias Brox <tobias@nordicbet.com>
2005-06-22 07:24 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
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