Re: Why are stored procedures looked on so negatively? - Mailing list pgsql-general

On Jul 23, 2013, at 7: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
themunless you really must. 

Application architecture is a specific software engineering discipline.  These types of generalizations come from
coderswho don't really understand application architecture and/or databases.  There are specific reasons to put code in
thedatabase server, application middleware, or the application.  To make this decision, much more must be known that
whathas been presented in this thread. 

For example, if you want to maintain data integrity, then you really want to use very specific table definitions with
foreignkeys, defaults, and constraints.  While this is not related to stored procedures, application coders try to shy
awayfrom these (like they do stored procedures) because it makes working with the database harder.  It forces the data
tobe correct before it comes into the database. When foreign keys, defaults, and constraints are not enough to ensure
dataintegrity then stored procedures should be used.  The question is, how important is your data and how much time do
youwant to spend correcting it after it enters the database? 

The next reason is performance.  Stored procedures can in certain circumstances dramatically increase or decrease
performanceof both the client and the server, network traffic, and application response time.  But which one is most
importantin your application?  The consensus does not know.  The best practice depends on the type of multitasking the
applicationis performing, the type of client, the client coding environment, the locations of the data being processed,
thelocking requirements, the concurrency requirements, the capacity of the servers and clients, the network topology,
theexpected response time for the activity, etc.  It is not at all uncommon to think that a stored procedure should be
inthe database server and to have performance testing show that it is better in the application and vice versa.  Keep
inmind that as the database becomes loaded, these performance issues may change and any decisions you make on a
developmentdatabase with only partial data may not prove out in the final application. 

There may also be reasons to normalize/denormalize data in the database, but present a different view to the
application. This should, if done correctly, make the application code simpler to maintain and understand.  What is
yoursupport experience level?  No experienced DBAs, this is probably a bad idea.  Relatively inexperienced application
coders,this is probably a really good idea. 

Sophisticated applications may even have more than one database server.  One update server and multiple read only
serversis very common in the environments I work in.  Since the update server is not burdened by providing all of the
readonly data, it has much more capacity to handle stored procedures.  Some of our environments see 80 or 90% of the
loadas read only.  This is the network topology part. 

Another example, if the result of a procedure is one number, but requires 15 columns, from 200 rows the question is, is
itfaster to do it on the server and only put one resulting number back on the network, or should the system get all 15
columnstimes 200 rows worth of data and put that on the network for the client to analyze?  The answer is, well it
depends? Well, maybe not for this example, but hopefully you get the point.  Now if part of the procedure requires data
thatcomes from a GUI table or user entered data that only resides in the application, then the situation changes. 

Wherever you put the code, you should have specific reasons for doing so and for high performance applications it is
notappropriate to generalize that all the code should go exclusively into the database or the app. 

Neil