Re: Why are stored procedures looked on so negatively?

2013/7/25 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2013/7/25 Luca Ferrari <fluca1978(at)infinito(dot)it>:
>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>> <someukdeveloper(at)gmail(dot)com> wrote:
>>> The added advantage of removing load from the app servers so they can
>>> actually deal with serving the app is a bonus.
>>
>> Uhm...I don't know what application you are developing, but I don't
>> buy your explaination.
>> While it is true that you are moving CPU cycles from the application
>> server to the database server, you will probably end with the
>> application server waiting for the database to acknowledge (and
>> therefore not serving requests) and usually the computation is not
>> that heavy for an online transaction (it would be better to do it as
>> batch if that is really heavy). Therefore this is not an advantage for
>> me.
>> Again, the only reason to use database facilities (like stored
>> procedures) is to arm the database so that even a different
>> application/connection/user will interact following as much business
>> rules as possible.
>>
>> Moreover, please also note that one reason developers tend to avoid
>> database facilities is that they are using some kind of
>> stack/orm/automagical library that does not allow the usage of deep
>> features in sake of portability.
>>
>>
>>
>>>
>>> I'm not planning on creating a complex application in the database in its
>>> own right, just augmenting what is already available with a few time savers
>>> and (a couple of) speed optimisations for commonly carried out tasks.
>>>
>>
>> I don't understand the "time saving" argument: you have to implement
>> the logic either in the application or the database, so let's say the
>> time of the implementation is the same. The only advantage of the
>> database is the code reuse. But take into account that there are
>> drawbacks, like debugging that is not always so simple.
>>
>> Luca
>
> I could be wrong, but the main advantage you gain by using stored
> procedures is what Luca says: unique data access interface.
> Just that.
> I don't think you'll save a single CPU cycle by moving logic from
> "application" to "DB" (or the other way around).
> That logic need to be implemented (and run) on either part.
> The only saving would happen if you push the logic straight to the client.
> And keep in mind than not all PLs are the same and have the same effectiveness.
> So, for example, instead of INSERTing rows from program, you could
> SELECT from a stored procedure which will do the INSERT possibly with
> the very same checks you would do in the application. Only put
> together in a single place. The stored procedure.
>
> Finally, I fear this is kind of "religion" war. So feel free to follow
> any or establish your own.
>
> The bottom line here is: PLs are OK. It just depends on what you do and how.

+1

exactly

Pavel
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general