Re: Why are stored procedures looked on so negatively?

2013/7/25 Steve Atkins <steve(at)blighty(dot)com>:
>
> On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper(at)gmail(dot)com> wrote:
>>>
>>
>> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the work load on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but it will save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app servers serving requests whilst the database gets on with its tasks.
>>
>
> App servers don't tend to maintain much global state, so are almost perfectly parallelizable. If you run out of CPU there, drop another cheap box in the rack.
>
> Database servers aren't. Once you top out a database server your main options are to replace it with a bigger box (increasingly expensive) or rearchitect the application (even more expensive).
>
> I'll always put more work on the cheaply scalable app servers if I can reduce the load on the database. Moving code to the database server for reasons of CPU cost (as opposed to, say, data or business rule consistency) seems an odd approach.

It is false idea.

What is a stored procedure? A few procedural construct and lot of SQL
queries. A procedural code is +/- zero overhead - significantly more
expensive are SQL queries - and these queries you will send from
procedures and from application server too. I can say so good written
stored procedures has zero negative effect on server performance -
more it has positive effect due elimination network latency it
decrease lock times.

Stored procedures is good environment for business logic
implementation or workflow implementation and bad for expensive
numeric calculations - and if you respect this rule, then stored
procedures will be faster always with less server load.

Regards

Pavel

>
> Cheers,
> Steve
>
>
>
> --
> 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