Re: Why are stored procedures looked on so negatively?

I have a DB that relies heavily on recursive stored procedures that tap reflexive tables that store hierarchical data.
Theseprocedures are called from queries and return record streams.  Temp tables are used to store collected data as the
procedureruns up/down the hierarchy.  And many other stored procedurea are called by the recursive procedure along the
wayto identify the correct hier path(s) to follow.  These things run amazingly fast.  Attempts others haev made to do
thisin perl-DBI were shaping up to be so slow that they were discarded early on, merely on that issue alone.  
 

In another DB, I use stored procedures in triggers, again, recursive and working with hierarchy, only this time,
inserting/updating/deletingrecords along the way.  Again, fast as compared with the external "competition", but the
matterof data integrity is another winner here because it makes it impossible for users at the SQL prompt to screw up
thehierarchies with singular DML calls.  The hierarchies end up being correct by construction.  Ironically, a different
grouptried to implement this without triggers, sps or even PG (they used MySQL).  And it's been nothing but
headaches...poor performance and broken hierarchies all the time.  When they asked me to port my PG triggers/sps to
MySQL,I hit walls that involved... 1) inability to defer constraint checking (for foreign key constraints),  2)
inabilityto leave cursors open in recursive calls (globally vs locally scoped cursors), and no support for "record"
datatypes.    
 

For me, the question is more along the lines of why I can't or shouldn't use stored procedures over external code, the
defaultbeing sps.
 

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: Thursday, July 25, 2013 11:09 AM
To: Steve Atkins
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why are stored procedures looked on so negatively?

2013/7/25 Steve Atkins <steve@blighty.com>:
>
> On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper@gmail.com> wrote:
>>>
>>
>> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the
workload on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but
itwill save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app
serversserving 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
(increasinglyexpensive) 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
tothe 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 -
significantlymore expensive are SQL queries - and these queries you will send from procedures and from application
servertoo. I can say so good written stored procedures has zero negative effect on server performance - more it has
positiveeffect due elimination network latency it decrease lock times.
 

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

Regards

Pavel


>
> Cheers,
>   Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To 
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


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