Re: [PERFORM] Prepared statements vs. Stored Procedures
> 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
Re: [PERFORM] Prepared statements vs. Stored Procedures
[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 [EMAIL PROTECTED])
[PERFORM] Prepared statements vs. Stored Procedures
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.