Neil Tiffin <neilt@neiltiffin.com> wrote:
> 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.
>
> Application architecture is a specific software engineering discipline. These
> types of generalizations come from coders who don't really understand
> application architecture and/or databases. There are specific reasons to put
> code in the database server, application middleware, or the application. To
> make this decision, much more must be known that what has been presented in this
> thread.
+1
> For example, if you want to maintain data integrity, then you really want to use
> very specific table definitions with foreign keys, defaults, and constraints.
> While this is not related to stored procedures, application coders try to shy
> away from these (like they do stored procedures) because it makes working with
> the database harder. It forces the data to be correct before it comes into the
> database. When foreign keys, defaults, and constraints are not enough to ensure
> data integrity then stored procedures should be used. The question is, how
> important is your data and how much time do you want to spend correcting it
> after it enters the database?
Agreed.
> The next reason is performance.
I'm going to skip the rest of this well-reasoned and well-written
response to give just a couple data points on this.
When working as a consultant, one client was doing everything
client-side and engaged me to fix some performance problems. In
one case a frequently run query was taking two minutes. As a
stored procedure the correct results were returned in two seconds.
This same client had a report which ran for 72 hours. A stored
procedure was able to return the correct data in 2.5 minutes,
although it took another 10 minutes for the client side to process
it into the output format.
Stored procedures are not a panacea, however. Writing in a
declarative format is, in my experience, much more important. I
saw one case where a SQL procedure written in imperative form,
navigating through linkages a row at a time, was on pace to
complete in over a year. Rewritten in declarative form it ran in a
few minutes. As a side benefit, the declarative form is usually
10% to 20% the number of lines of code, and less buggy. For
retrieval of complex data sets, the big thing is to learn to write
SQL which specifies *what you want* rather then trying to specify
*how to get it*.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
