The 411: Stored Procedures, Views, and ORM

Saturday, June 02, 2007 -

Prolog
Think back to your high school/early college days. It's Friday night and you call your buddy Jimmer wondering what's happening.

"I hear Worm's gotta a keg and these Italian exchange students will be there! We're headin over there in 30 - you in?"

So you get your stuff together, trying to wear something that makes you look cool and most importantly that you didn't try to look cool. You hop in your mom's old Nissan and head over to Jimmer's. Two hours later after everyone's lagged hard, you end up at Worm's and there's no one there but him and 2 other guys, and they're asleep.

"Yah I couldn't get the keg - this dick at the liquor store nabbed my ID and told me I'd get arrested so I took off. What a #$%#in @!#hole... but I hear Jonah's havin some people over and these Phi Delts from State are comin too. I hear they're bringin a keg - and man those State dudes don't drink swill so you know the keg's gonna be good - Coors Light or somthin... and the Italian's are headed over there too."

Out the door you go, in pursuit, wondering what a Phi Delt is and why they don't drink swill. Two hours later, when both keg and Italians fail to materialize, you realize the night's a bust and you should have just stayed at home, playing Warcraft or watching the Colbert report.

WTF Are You Doing?
Being a developer is like trying to find a party on Saturday night - sometimes you have the time of your life, other times it's a complete waste of time and you end up fried, wondering WTF you were doing with all your time. The ultimate kegger-carrot has to be the shiney new data access approach- the "new way" of doing things that brings you that much closer to your data, proves a bunch of other people wrong, and sheds that much time from your day.

Despite the direction this post is taking - no - I'm not trying to pimp SubSonic. I got to thinking about all the approaches to data access tonite over dinner and I was sort of struck by all the different paths I've taken over the years and just how hard it is (in terms of LOC) to pull data from a DB. Ya'd think that by NOW, 10 years into this whole data-driven web thing we'd have figured it out. Perhaps settled the debate and come up with some common way of sending data from DB to page.

For some reason this most fundamental of needs has been ridiculously inflamed, mused over, conjured upon, and obfoobarscated to the point of religiosity. And as I sit there with my Moa Sandwich in front of me, I had a big WTF moment.

So let's set the stage. There are 3 main camps of data access: The ORM Nuts, The Generators, and the Out of The Boxers. Lets take a look at each shall we?

ORM Sucks
ORM - or Object Relational Mapping is the concept that a table in your Database is akin to an object representation in Object-oriented programming. I don't like ORM cause I've seen what it does to people. I've lost friends to ORM, and I've had my brushes with it too. It's not easy to walk away from...

ORM Makes sense for simple cases - a Product persists itself to a Product table. Same with an Order and a User. Generally these tables correspond almost completely to the object they persist.

The problem comes in when you consider that a database is, well, a database and usually complies with (or should comply with) rules of normalization at some level. As your database grows and becomes more complicated (which happens as you build out your project right?), this model breaks down completely. If you're an ORM person, ask yourself how much code you need to write to support a many to many relationship...

To blow this completely up let's reverse the logic here and see if we can apply OOP design principles to a database. The first thing we need is a table called "Object", with a field for an ID and a Description. Then we need to throw Relational Theory out the window, and proceed to build one of the ugliest databases imaginable...

Rails guys go so far as to say "I don't want any logic in my database", meaning that all things (even DRI) belong in code- and theoretically that's fine. It's when people actually begin to use your application that your theory decides to crash and you end up with a Twitter mess :). Am I saying this model won't scale? Of course not - you can get anything to scale as long as you have a big enough server right?

Kill Your Code Generator (or Do You Need Stored Procedure Intervention?)
I worked on a project for a very long time where we took a set of existing Code Smith templates and reworked them for our project needs. When run, these templates would reach into our DB, pull out the table structure, create a set of SPs to wrap each table (CRUD, Find, etc), and then create the code to use each SP.

OK, you know who you are and I bet you're thinking to yourself "Oh shit - yah I had an application like that and it was really a PITA". It's OK... we're here for you. Anyway back to my boring story...

As the system grew we ended up with 283 stored procedures, 269 of which did nothing but simple SELECT, UPDATE, INSERT, and DELETE. I asked the lead architect once why we use SPs, and he said to me "I've always used SPs". I love working for guys like that.

When I asked him why we use so many damned SPs he gave me the standard answer: "SPs are faster and more secure. They protect against injection and I don't want people writing inline SQL in my application". Bah.

I think that's the standard answer for most people who have an Stored Procedure Addiction. Stored Procedures can kill if you don't get control of them. They can actually crawl out of the DB and crawl into your dreams. I once had... nevermind.

Here's some sobering news on these beasts:

• Stored Procedures DO NOT RUN FASTER on post SQL 6.5 systems. Yes, the pre-compilation step did save a lot of time back in 1998, but not anymore.
  
• Stored Procedures DO NOT OFFER ANY BETTER SECURITY THEN YOUR CODE DOES. I got into this with J Sawyer (Microsoft) once while we were doing some work on the CSK, and he kept telling me that a good defense is a "deep defense" - meaning that you can set security levels on stored procedures so that only certain roles/users can execute them. I asked him why you wouldn't put that in code and he said "you could, but security at the DB is better and your code only stops people from the application vector". He has a point about the application vector, but if someone got in through my "server vector" or my "Port 1433 Vector", the last thing I'd be worrying about is how I protected my SPs. J's a Super Smart Dude but this line of argument, to me, seemed silly.
  
• Stored Procedures ARE CODE, and they are code you need to support. The project I mentioned above actually generated procedures that we never, ever used. In fact it's safe to say that over 30% of the SPs we generated we never, ever used. Do you write code that you don't use? If you're thinking of defending this practice, please take a second to slap yourself...

I love Code Smith like I love good beer. Actually that's not true - but I am in some serious like with Code Smith so don't get me wrong there. It has been abused, however. Seriously abused (this goes for MyGeneration too).

Code that you generate is STILL CODE. You need to know it and support it or it's no good.

Out Of The Box: Just Gimme The Damn Koolaid
Finally we get to the folks who just use what MS gives them: SQL Datasource with a DataSets. This is fine and they work OK, plus hey, it's really visual :). There really is no way better to get your application to talk to your DB faster than to drag the table onto a blank ASP Web Form and watch Visual Studio do it's magic...

But consider the result:

• You have SQL living on your ASP page. Not just in your code, ON YOUR PAGE. The declarative model is fine and all, but SQL on the page is a tad bit much for anyone's eyes.
• There is absolutely no logic layer here to abstract the data calls. When you change a field in your application, all your pages break.
• Have you considered what these WYSIWYG controls are actually doing under the hood?
• Who uses a GridView to edit data anyway?

Encapsulation and abstraction are at the core of what we do. The Rails DRY mantra (Don't Repeat Yourself) is perfect here because this model of talking to your database is redundantly redundant and repetitive.

Imagine each little SQL Datasource as if it's a zit, blemishing the pages of your application. Take a second and squeeze out each one, and clear up that messy complexion.

OK Smart Guy, What's Your Answer Then?
Use your noggin, and follow the path of least resistance. In the extremes lie the danger (or as Obi Wan says - "only the Sith deal in absolutes"). I like to read the Tao a lot - it keeps my feet on the ground and helps me understand Phil Jackson's Triangle Offense. One quote I think says it all:

The mark of a moderate man
is freedom from his own ideas.

It's easy to toss quotes around and get all philosophical (this is a far stretch from where this post started with kegs and Italian exchange students but hey, it's a ride we took together), so let's put some rubber on the road shall we?

• There are times when abstracting your tables into objects makes a lot of sense, and it makes your code cleaner and easier to maintain.  But there are an equal number of times when this model falls short. Stop trying to fight it - use the DB for what it's good for (DRI and data handling) and let it do the heavy lifting. Your goal should always be performance - don't sacrifice extra connections just to satisfy the ORM design model.
  
• Views are your friend. They REPRESENT, and you can query them easily. Think of the code you'd have to write in an ORM model to get something like a summary thread list for a forum app - you'd want to show the name of the thread, the number of replies, and the date/author of the last reply. How do you do this with ORM? The answer: lots of code. Or you could write a simple view and have it done with in no time.
  
• Stored Procedures are just that: Procedures. Use them for what they are good for - executing a number of SQL calls or performing some light logic in your DB. It's up to you how much logic you want in code versus SPs, but generally a good rule of thumb is that if it takes more code than SQL, put it in an SP.

So to wrap this up - each approach is valid, but no approach is final. It's too tempting to fall into a DAL religion and think "it's the only way". Never is there only one way in anything code-related. Open your approach to use SQL Server in every way - even check out Functions! They can save you so much time it's unreal - and that's what this all boils down to: saving time, creating less code, and offering value.