When to use stored procedures, and when not to (part 2)

In an earlier post, I talked about when to use stored procedures and when not to. In that post, I omitted a few reasons because some of them were less significant and some could be pros or cons, depending on your situation. For the sake of being thorough (as-in, “yes, I did consider that”), let me present a few common arguments with some reasons why they don’t really matter very much, or “it depends”.

A few extra (but lesser) reasons to NOT use stored procedures

  • Code Bloat – If you require a SP for every DB call, that will mean (at a minimum) 5 SPs per table (read row, list, insert, update, delete). If you want to filter, conditional sort, etc, the count goes up. Have you ever seen a DB with hundreds (or thousands) of stored procedures? Was it helpful or did it seem out-of-control and downright messy? In OO languages, you can wrap this bloat inside of a class. DBs offer namespaces/schemas, that don’t roll it up, but can group it by 1 layer, just like VB6 did, yippee (yawn). Oracle does handle this a little more gracefully. The down-side is that you would have to use Oracle. 😉
  • SPs are not compiled – This allows people to easily “inspect”/snoop-at the code and alter/tamper-with it. You might never notice that it changed. Of course, this can be prevented by encrypting your SPs (if you know how to do that). I don’t know of anyone who encrypts SPs.
  • Version control – What percent of dev teams are able to manage/version-control their SPs? I dare you to try. This is a problem that EVERY dev team struggles with. Your process needs to be very tight for this to be effective.
  • Complex searches – Have you ever tried to make a big ol’ search screen in an app and have it pass params into a SP? Could you accomplish this without resorting to OPENQUERY (for a nice little injection attack?). I have seen it done, but it was awful and fragile. A raw SQL query, (in your code), would have been way simpler and equally safe.

Neither/Both/it-depends/it-doesn’t-matter

  • Security – SPs can be a nice wrapper around a DB to allow you to manage DB security in a more granular manner. However, this almost never happens. (Can you name 2 places that do it?) Mostly, this is because YOUR APP is your security wrapper. People do not go directly to your DB. They don’t want to. If your users go directly to your DB, that is another story.
  • Consolidation/reuse – Commonly performed actions can be wrapped in a SP to ensure consistency and code reuse. However, this is what {Web Services, SOA, ORMs, data-layers/tiers, etc.} are meant to do. It is a two-edged sword because when you write a (shared) SP, now you have multiple systems that rely on “consistent” behavior. In other words, now you can’t update your shared SP without updating every app that touches it. Why has Microsoft moved-on from DLL hell to COM hell, to GAC hell? Because of this issue. Mac and *nix won’t even do DLLs (for this reason). You won’t know how bad it gets till it is too late. Caveat emptor.
  • A DBA can inspect your SP contents …and tune them and the DB separately without digging into your code. Do I really need to go over the risks of somebody else “tuning” your stuff (when the mood hits them) or the incompleteness of tuning a DB simply based on the fraction of code contained in the SPs? I find it hard to believe that anyone believes this is a better approach than using SQL Profiler or the tuning wizard (or equiv for Oracle, etc)?
  • SPs can define complex/custom types …that are not supported in ORMs or OLEDB. Which is cool, but good luck supporting that in your app. (obvious red flag words: “complex”, “not-supported”).  The few people who fall for this, end up regretting it eventually.
  • Embedding SQL into your code, tightly-couples your code to your model – That is a red herring. Your app is already tightly coupled/bound to your DB. Anyone who thinks you can change a DB without changing an app is fooling himself. In your entire career, you might get away with it once or twice, but it will burn you eventually. The DB is the foundation of an app. You change the foundation, you affect everything above it. There are no reasonable ways to avoid this. Especially not via SPs.
  • Enforcing best practices – That is a nice idea, but don’t just enforce best practices at your DB layer. Do it in your code and DB layer. If they are together, then review them together. If they are split, then do them twice or N-times (for each layer). Enforcing best practices in an inconsistent manner is an oxymoron. Don’t you think?
  • Porting to another platform – First, who simply moves from SQL server to Oracle or vice-versa? You think you can do this without a re-write? I’ll take that bet and give you odds. Porting to another DB is not any easier (using or not-using SPs) because you still need to review/edit every DB call. Whether it is in code or in SPs, you need to check the same amount of code. In SPs, they just seem to be more separated (as long as they really are, and there are no stragglers).
  • Cleaner code – It is true that your code can look pretty ugly with SQL statements peppered throughout. That is why your app architecture should use a data layer or ORM. When your SQL is in a data layer/tier, it is easier to review or test via NUnit or equiv. The ugliness needs to go somewhere. Putting it in a SP doesn’t actually make it disappear.
  • No need to recompile, redistribute – First, recompiling an app should not be a BFD. (Second) Neither should re-deploying an app. If it is much harder (enough to make you want to use SPs everywhere), then you really should switch to a web app or read-up on “click once deployment” or how to make a “boot-strap” for your app. They are wonderful. Also, do you honestly think that having all of those individual SPs will make it easier to deploy/redistribute? To me, it has always been a couple hundred (or thousand) individual points of failure. If you get one of them out of synch, the whole deployment is a failure. It is hard to believe that anyone could find this to be easier than having it all in one bundle (your code).

With any of these, your mileage may vary. Some of these statements might be totally invalid under certain circumstances, depending on team culture, experience, dev tools, app support, database vendor, size, age, performance, architecture, etc.

The rest is opinion or dogma. Did I miss anything else?

Advertisements

About Tim Golisch

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

2 Responses to When to use stored procedures, and when not to (part 2)

  1. John Lauro says:

    We automate our version control on the database. An export of the database schema is done automatically daily and imported into version control system. A history of any table or index changes along with any stored procedures are also caught in the export. Any proposed changes are branched off the current schema, and even if that process is bypassed the history of the structure along with when and what changed can’t be skipped… Some functionality can be table driven, and it might be worth exporting specific tables too, but obviously you wouldn’t want the data of every table in your version control system…

    • Tim Golisch says:

      Mr. Lauro, I must say that I am quite impressed by your process. It sounds like the kind of system that every DBA dreams of implementing, some day. In fact, I hope your description inspires others to strive for such purity and excellence. I’m already trying to think of how I would implement it here, where I am. Thank you for showing that air-tight version control is not unachievable, if you have a good plan and the determination to accomplish it.

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