On 24/07/2013 14:58, Merlin Moncure wrote:
On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
<someukdeveloper@xxxxxxxxx> 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@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Some Developer
Sent: Tuesday, July 23, 2013 8:29 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxxx) 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
