Re: Why are stored procedures looked on so negatively?

On 24/07/2013 14:58, Merlin Moncure wrote:
> On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> On 24/07/13 14:21, Gauthier, Dave wrote:
>>>
>>> I find stored procedures to be a God-send.  The alternative, external
>>> code, is the risky, difficult and often poorer performing approach to the
>>> problems sp's solve.   What better way to interact programatically with your
>>> database than WITH your database?
>>>
>>> The only people that I see frown upon them don't understand them, are
>>> afraid of them, and so find ways to justify their views about them in
>>> negative terms.  I suppose that's human nature.  But once they get "turned
>>> on" to stored procedures, their views change.
>>>
>>> As for selling sp's to them, especially if they are management, there's
>>> nothing more convincing than a demo.  And a real good way to demo their
>>> effectiveness is through a remote connection, preferrably across a time zone
>>> or two, where the task involves many (hundreds of thousands) of queries that
>>> the external script would have to do one at a time, over the net.  The sp
>>> would just run them inside as part of the sp call, locally, in a tiny
>>> fraction of the time.
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: pgsql-general-owner@postgresql.org
>>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
>>> Sent: Tuesday, July 23, 2013 8:29 PM
>>> To: pgsql-general@postgresql.org
>>> Subject: [GENERAL] Why are stored procedures looked on so negatively?
>>>
>>> I've done quite a bit of reading on stored procedures recently and the
>>> consensus seems to be that you shouldn't use them unless you really must.
>>>
>>> I don't understand this argument. If you implement all of your logic in
>>> the application then you need to make a network request to the database
>>> server, return the required data from the database to the app server, do the
>>> processing and then return the results. A stored procedure is going to be a
>>> lot faster than that even if you just take away network latency / transfer
>>> time.
>>>
>>> I'm in the middle of building a database and was going to make extensive
>>> use of stored procedures and trigger functions because it makes more sense
>>> for the actions to happen at the database layer rather than in the app
>>> layer.
>>>
>>> Should I use them or not?
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
>>> changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>> Thank you all for the responses. I feel better about making use of them now.
>>
>> Now for one final question: I was planning on using plpython2u to write my
>> stored procedures since Python is a language I am very familiar with. I
>> understand that many people would want to use plpgsql instead but it'll be
>> quicker for me to do it in Python.
>>
>> Will there be much of a performance difference between the two at all? Are
>> there any very convincing arguments that will make me use plpgsql instead or
>> does it not really matter?
>
> plpgsql is generally the fastest/easiest language for a certain (but
> important) class of operations. it runs closer to the SQL execution
> engine and automatically plans all your queries (which can be a pretty
> big deal for certain types of coding).  all error handling is native
> (so that you don't have to catch a python exception and peek into the
> sql aspects of it for appropriate handling) which is a bigger deal
> than it appears on the surface.  also it's good to exercise your SQL
> skills.
>
> whichever way you go, good procedure practices generally involve
> approximating scripted SQL to the extent possible.   also you should
> separate routines that read from and write to the database (and try to
> keep as much code as possible in the read side).  make sure to mark
> routines immutable/stable as appropriate.   another underutilized
> function decoration is STRICT -- it's very fast when it fires and can
> save you a lot of debugging headaches.
>
> merlin
>

Thanks. I'll be sure to bear that information in mind.