When to use Stored Procedures (and when not to)

Okay.  A few weeks ago, I had a discussion with someone who was passing himself off as an expert.  He started heckling me because I had recommended not using stored procedures on a project.  Of course, he immediately assumed that I didn’t want to use SPs because I didn’t know how to write them.  Bzz, wrong.

I asked him why he felt we should use SPs.  All he could come up with was, “Everyone uses stored procedures” and “I’ve used stored procedures on every project that I have ever worked on” and “It is an industry standard”.  These were all ways of saying “I don’t really know why, but I am going to try to make you feel dumb for disagreeing with me”.  Naturally, I was not impressed.

So, I started explaining to him the real reasons that we would want to use SPs.  I think that is where I lost him.

I learned these reasons from two master DBAs from Oracle.  Mind you, I’m not a big fan of Oracle, but these two guys really knew their stuff!  Here is what I learned about Stored Procedures:
Pro

  • Stored procedures typically run faster than raw SQL, but are equivalent in speed to parameterized queries
  • Stored procedures typically reduce your risk for SQL injection attacks
  • Stored procedures are good for multiple queries, cascading inserts/updates/deletes or large multi-query calls to a DB
  • Stored procedures allow a separation of the data layer from the program.  If you have a large team, you can assign all stored procedure coding to one (or more) person(s) so the other developers can concentrate on other coding
  • Stored procedures allow a very granular application of security to a database. eg. for a table such as “security”, you can ban read/write permissions to the table but create a SP that can check login credentials or it can omit SSN, and other sensitive info from the result set based on a person’s role within the DB.

Con

  • Stored procedures are just as fast as parameterized queries but are not any faster after the first run.
  • Stored procedures do not protect you from SQL injection attacks if you execute SQL as a string, within the SP.  For example, using the Exec() command or OpenQuery().  Both of these COMPLETELY nullify any benefit of using stored procedures.  They are pure evil. If you are using Exec() or OpenQuery() within a SP, you should just use raw SQL, so your colleagues are not under the mistaken impression that there is any benefit from this query/command being in a SP.
  • If your team is not very large and you don’t have a full-time person, whose only job is to code-up SPs for you all day long, then you probably won’t benefit from the separation of duties that come with SPs.  After all, if all of your programmers have to learn TSQL or PL/SQL, you’ve really only added another language and platform for your developers to master.
  • The separation of layers is a two-edged sword!  On the one hand, if you are using automated testing tools, you can test all of your data layer separately from the rest of your code.  That is good.
    I have had some people tell me how great it is that you can alter or deploy SPs without having to recompile your app or go through a deployment.  Okay, just think about how evil that can be.  No really, take a moment to think about the concept of somebody altering a SP without going through proper channels or release cycles, etc.  Pure evil!
    Worse still, is the fact that the SP code is usually COMPLETELY separated from the rest of the code in your program.  You cannot step-debug into it. The SP code often is the last place that you would suspect a bug or tampering, etc. Which makes it as evil as a DB trigger.  There is a distinct benefit from having your code all in the same place.  That is, you can easily see it and review it.  When you deploy it, it all gets deployed together.
  • SQL is a weak programming platform.  It is a carry-over from COBOL.  Think of how you do the following things in .NET or Java { configuration, error handling, logging, threading, tracing, security, interfaces, inheritance } then think about how you do them in a SP.  Ummm… SPs don’t handle these very-well, if at all.  So, wouldn’t it make more sense to do your programming on a platform with strong features/tools for developers?  That is why technologies like LINQ, NoSql, and ORMs are so popular.  Because they eliminate or conceal the data layer and its crusty old ways. 

I have been burned on these cons a few times in my career.  It has been enough to make me apprehensive of SPs.  I DO NOT automatically choose to use SPs for every query in every project, because it means a much heightened level of responsibility and maturity on the part of everyone on your team.  Most people are not aware of these pros and cons, so they don’t realize the headaches that they can cause.  Anyone who is not aware of this, is likely to be the guy who screws up your stuff.

Just be aware that SPs don’t come from magical pony land where everything is always good and there is never any evil.  No sir!  They come from the real world, where there are trade-offs.  If you are not going to reap the benefits of using SPs, then you are not compelled to use them.

Quick list of knowing for sure that you will benefit from the (exclusive) use of Stored Procedures:

  1. Your users will connect directly to the database and run their own queries and you need to limit their access to some rows and columns and configuring this at a table level will not be adequate.
  2. You have a very large team and one of the people on your team is an expert at writing stored procedures.  This person(s) will focus on all of the DB level stuff.
  3. You are very-much into automated testing and are fully committed to writing automated tests to confirm that your DB level (SPs, etc) work perfectly.

Beyond these 3 reasons, the choice to use SPs is an opinion or superstition.

Of course, I’m willing to consider other reasons.  Go ahead and post them below.  Don’t bother entering meaningless conjecture please.

Follow up: Okay, some of you offered some really good feedback.  So, I wrote a follow-up article that addresses some less-relevant (but still meaningful) things to consider as well.

Advertisements

About Tim Golisch

I'm a geek. I do geeky things.
This entry was posted in Architecture, Database, Methodology. Bookmark the permalink.

5 Responses to When to use Stored Procedures (and when not to)

  1. derp says:

    Interesting read. It seems that one potential benefit that SPs can bring is minimizing the need to duplicate code when the same result set is needed in different places, compared to pasting raw queries around. As you said, it’s always a game of trade-offs.

  2. Nacho says:

    Really really good and funny as well 🙂

  3. Completely agreed… Great post!
    I use SPs in such tasks that has scheduled to run once a day… If I need to run these tasks in other moment just execute the SP, this way I try to don’t repeat my logic business.

  4. John Lauro says:

    Definitely some good points to consider, but I don’t understand your point on configuration, error handling, logging, and threading and even interfaces as they are all things are easier, or at least just as easy to do with stored procedures. If done right, they are actually a pro for using stored procedures.

    • Tim Golisch says:

      I see your point. If these are done within reason, these certainly don’t make programming more difficult. Once you get out-of-hand, it is another story.
      I have worked with Oracle DBAs who recommended that my team put as much programming/logic as possible into Stored Procedures. I’m even talking business logic (all of it). Of course, that is a pretty extreme example, but I’ve seen plenty of business logic embedded in SPs and I’ve seen days (or even weeks) get lost on trying to debug them.
      In general terms, TSQL and PL/SQL are not very strong programming platforms when compared to .Net or Java. If you maintain reasonable boundaries, you will be fine and you will experience great performance.
      However, the more you push your logic into your database (TSQL and PL/SQL), you will sacrifice maintainability for performance. It can be a reasonable trade-off, as long as you need the performance and you are able to find people who are cheaper than simply buying more hardware and electricity.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s