I am starting to develop an application and I am considering to use stored
procedures. Is there a performance penalty for implementing many DB
operations with stored procedures? For example, if my app has 50 tables, is
it a good idea to write store procedures for inserting, deleting and
updating data for all of them? What if, additionally, I include stored
procedures for information retrieval (SELECT statements)? What would be a
good criteria to decide how far to go with stored procedures?

With respect,
Jorge Maldonado

Search Discussions

  • Jean-Yves F. Barbier at Mar 3, 2011 at 9:28 pm

    On Mon, 28 Feb 2011 14:00:24 -0600, JORGE MALDONADO wrote:

    I am starting to develop an application and I am considering to use stored
    procedures. Is there a performance penalty for implementing many DB
    operations with stored procedures? For example, if my app has 50 tables, is
    it a good idea to write store procedures for inserting, deleting and
    updating data for all of them?
    External orders have more overhead: time of transmission, then
    compilation and execution (AFAIK, both possibilities are left in cache after
    1st processing), stored procs jump the 1st step.

    The main advantages of stored procs are (IMHO):
    * With good permissions, stored procs rights & users management, nobody can
    issue external orders to do the same,
    * Users can't override your security (in order to avoid OID dependency, I
    use my own users system in which I can revoke one in 2 clicks - by disabling
    the CONNECT permission.)
    What if, additionally, I include stored
    procedures for information retrieval (SELECT statements)?
    For this one, I'm not sure: I kept complicated SELECTs that return a single
    row as a result stored, while keeping simple SELECTs on the client side.
    What would be a
    good criteria to decide how far to go with stored procedures?
    The complexity of what can be done with them and the invisibility of sensitive
    procedures because of user's rights (or not) upon them, tables & columns.
    Every big proprietaries databases also have them.

    HTH
    JY
    --

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 28, '11 at 8:00p
activeMar 3, '11 at 9:28p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase