Stored procedures: good or bad?

Tuesday 9 November 2004 — This is nearly 20 years old. Be careful.

Rob Howard says stored procedures are good. Frans Bouma rebuts with stored procedures are bad. Many many comments ensue.

In my experience, this debate comes down to a mindset. If you are fundamentally database-focused, you will like stored procedures. If you are fundamentally application-focused, you will not. Generally, requirements like portability and maintenance costs will outweigh personal preferences any way. For example, if your application has to run on more than one database, stored procedures will be very expensive to use (because they will have to be re-written for each database platform). Personally, I find writing code in pre-paleolithic SQL extensions to be absolutely abhorrent, so I don’t like stored procedures. Some day, maybe I’ll work on a project where their benefits outweigh their disadvantages.

Comments

[gravatar]
Ian Bicking 12:31 PM on 9 Nov 2004
I think Martin Fowler's description of Database Styles does a good job of explaining the perspectives behind this.
[gravatar]
Jim Dabell 12:55 PM on 9 Nov 2004

It depends on what type of portability you need. If you are going to be writing multiple database front-ends (e.g. web front-end in PHP, C++ front end for the desktop), then you'll want to separate the front-end from the database design with a clear API. Stored procedures are the way to do this.



If, on the other hand, you'll only be writing one front-end, it makes more sense to leave your options open rather than lock yourself into one database.



Either way, you're going to be the subject of lock-in - if you use procedures it'll be the database you are locked into, and if you don't, it'll be the front-end you are locked into. Which is the better approach isn't a matter of technology but the problem at hand.

[gravatar]
Robert Brewer 2:29 PM on 9 Nov 2004
Jim, I'd just add SP's are *a* way to separate concerns. ;) An alternative would be an intermediate layer like an ORM. The problem with most of those currently is that they are language-specific (leading to lock-in again), but they don't have to be. Holy Grails, and all that.
[gravatar]
Jeff Atwood 12:45 AM on 10 Nov 2004
> Some day, maybe I'll work on a project where their benefits outweigh their disadvantages.

Doubt it. Hasn't happened to me yet.

Also, parameterized SQL pretty much kills any of the performance argument. Which was nonsensical to begin with; if I'm not gonna write my entire app in assembly language (for what, I hope, are obvious reasons), why would I want to write my entire app in *database* assembly language?

>Either way, you're going to be the subject of lock-in

I don't think so. Not if my API is a web service. There are so many ways to build a data abstraction layer without the many downsides of procs.

Now, like all assembly language, there are places where procs might make sense. I don't categorically rule it out. But it is a specialized solution for specialized problems. What's really aggravating is all the bad advice people are giving out about this, which is, even more aggravatingly, almost always presented as an generic extreme-- you better use procs, and all procs, otherwise your database performance will be crap! It's just not true.

More on this here:

http://www.codinghorror.com/blog/archives/000117.html

and here:

http://www.codinghorror.com/blog/archives/000121.html
[gravatar]
Ken Hirsch 5:27 AM on 10 Nov 2004
I don't think the comparison to assembly language is apt. The main disadvantages to assembly language are that it takes a lot more lines of code than a high level language and that it is completely unsafe (even more than K & R C :-). Neither of those things is true of stored procedures.
[gravatar]
Jeff Atwood 11:05 AM on 10 Nov 2004
Actually,

- it does take many more likes of PL/SQL (Oracle) to do things I can do procedurally in .NET using built in objects, or better language constructs. It's an extremely primitive language.

- PL/SQL may not crash the database, but you can (and will, unless you do use Oracle's crazy error handling) get some extremely cryptic low-level errors back, and it's very hard to debug or step through these code blocks.

I think it's a perfectly apt comparison, although I admit assembly language is an extreme case.

Add a comment:

Ignore this:
Leave this empty:
Name is required. Either email or web are required. Email won't be displayed and I won't spam you. Your web site won't be indexed by search engines.
Don't put anything here:
Leave this empty:
Comment text is Markdown.