> 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