Re: Why are stored procedures looked on so negatively?

On Tue, Jul 23, 2013 at 5:29 PM, Some Developer
<someukdeveloper@gmail.com> wrote:
> 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 think that mostly speaks to the method you used for finding things
to read.  This is a well known holy war.

>
> I don't understand this argument.

That is a conclusion, not an argument.  You didn't give us the
argument behind the conclusion!

> 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.

Sorry, I don't get this at all.  Whether you use a stored procedure or
not, the database needs to be told what to do by the outside word, and
needs to return the result to the outside world.  So you can not get
rid of that minimal round trip, no matter what, unless your database
becomes solipsist.  Now, if the application-side code needs to make a
lot of round trips to the database in order to implement one logical
unit of work, that is a different matter and stored procedures could
help there (but so could consolidating the round trips into a fewer
number of more sophisticated SQL--which is often but not always
possible).

One of the very annoying uses of stored procedures I see is insisting
that all access goes through them, with no direct access to the
underlying tables via ordinary SQL.  They have now replaced one of the
most successful, powerful, and well-known data access APIs ever, with
some home grown API that is probably half-baked.  Sometimes a case can
be made for that (particularly for large bureaucratic organizations,
or intensely regulated ones) , but don't expect it to be free of
consequences.

> 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?

Are you a one man shop, and always will be?  If so, i think it is
mostly a matter of what you prefer developing in, and what you are
most used to developing in.  If you hire someone to help you out, do
you want that person to be able to do interesting (and perhaps bad)
things with the database through SQL, or do you want them to be mostly
restricted to changing the font and color of the web page showing the
results?  A case could be made for either way.

Cheers,

Jeff