I’ve been working a lot with Castle’s Active Record and Ruby on Rails in the last month and as a result have written significantly fewer basic CRUD operations and database access code. It’s been an addictive experience and has caused me to rethink the proper role of hand-written database code (sprocs) within an application.
Although I feel perfectly comfortable in a set-based world writing SQL, it has traditionally been one of my least favorite areas of coding. Besides being relatively repetitive and tedious, at least when it comes to basic CRUD operations, sprocs are much more difficult to handle when it comes to source control, versioning, debugging, and unit testing.
For example, at my last job we were tasked by auditors to come up with a build and deployment process that included version traceability and rollback capabilities. It was pretty easy to put together an acceptable solution for assemblies, since automatically versioning dll’s in a trusted way is simple via the AssemblyInfo and rolling them back is trivial since everything is contained in a manageable number of dlls that simply need to be copied from one folder to the next. When it came to sprocs, however, the best we could offer auditors was some hackery around adding comments about the version at the top of each sproc definition file along with a big disclaimer that there was no guarantee that files were not modified by DBA’s along the way.
At my current job, database code causes us even more trouble because the database is larger and contains more sensitive data (thus making restores more difficult), there is a heavier reliance on shared code, and only the deltas of database code are currently under source control. It feels like I am constantly tracking down some ghost bug that is caused by my local database schema somehow being out of sync with the codebase.
Despite these misgivings, I’ve dutifully followed the traditional Microsoft recommended best practice of funneling all data access through sprocs up until recently because sprocs were supposedly faster, more secure, and provided a beneficial layer of abstraction.
Although I have heard several arguments against sprocs in the last several years, I recently embarked on a more thorough investigation while trying to convince my team to switch over from using an in-house code generation\sproc-based solution for data access to using NHibernate\ActiveRecord. Here are a few resources I found that present good counter arguments against the conventional sproc wisdom.
- Stored procedures are bad, m’kay?: This classic post that was written by Frans Bouma of LLBLGen fame in 2003 spawned quite the flame war on the topic. Most notably, Frans counters the “Sprocs are faster because they are compiled” argument by quoting passages from the SQL Server BOL documentation that clearly suggest otherwise. He also counters security arguments by pointing out that parameterized queries prevent SQL injection just as much as sprocs and that assigning permissions to views and roles provide just as much protection as assigning execute rights on sprocs.
- Who Needs Stored Procedures, Anyways?: This is also an older post from Jeff Atwood where he nicely summarizes the negative aspects of SQL when compared with a traditional coding languages and came up with the quotable phrase “Stored Procedures should be considered database assembly language: for use in only the most performance critical situations”. I definitely think that some developers are reluctant to embrace ORM’s for the same reasons that many old C++ programmers scoffed at the idea of letting the CLR garbage collector manage memory for them instead of manually doing it themselves with raw pointers.
- Why I do not use Stored Procedures: Jeremy Miller dismisses performance arguments by declaring them instances of premature optimization and elaborates on all the problems caused by sprocs when it comes to maintainability, testability, and architecture. He also points out that the touted benefit of allowing DBA’s to make changes is actually a dangerous practice since it represents a breaking API change from the application’s point of view and thus should go through thorough regression testing before any DBA should be allowed to make changes.
- Foundations of Programming – Part 6 – NHibernate: Besides offering a nice introduction to NHibernate, Karl Sequine provides a nice summary of the historical sproc debate, including a counter point for the increased network traffic argument, which he says is a moot point since most traffic occurs between a app and database servers sitting on the same internal GigE networks where bandwidth is fast, plentiful, and free.
- DotNetRocks ORM Smackdown: For a more balanced debate, listen to this podcast episode (or download the transcripts) where Oren Eini and Ted Neward face off over the value of ORM’s in the software industry. Be sure to check out the commentary on the episode in the comment section of this Ayende post as well as the rebuttal in this post by Ted Neward.
In my opinion, one of the strongest denunciations of traditional sproc dogma comes Redmond itself, which seems to be straying from its original sproc recommendations in favor of a more more dynamic SQL generation world-view with its recent release of LINQ to SQL and the Entity Framework.
If you follow the open source world or program in some language other than .NET, then you’re bound to feel a little smug right now because ORM’s have been around for a long time. In fact, I have a vivid memory from 6 years ago of a co-worker who was fresh from the Java world being dumb-founded that Microsoft didn’t have any ORM solution. He was used to using HIbernate and the thought of manually mapping database tables to domain objects was hard for him to grasp. Even in the .NET open source world, I’ve been reading blog posts that sing the praises of NHibernate and IBatis.NET, two popular .NET ORM ports, for several years.
On one hand, Microsoft’s entrance into the fray is good news for ORM enthusiasts since it means that a larger audience of developers will begin to see the technology as legitimate. On the other hand, Microsoft clearly has some catching up to do in this space, so you might want to think twice about starting off with Microsoft’s offering rather than one of the more proven open source or third party alternatives.
If you are a .NET developer and new to ORM’s, then I recommend starting out with Castle’s Active Record, which you can learn in less than an hour by reading this Getting Started with Active Record tutorial. My co-worker’s were reluctant to try NHibernate because of the perceived learning curve and the plethora of mapping files required, but they quickly agreed to use Active Record after only a short demo.
If you are a POCO purists, which means that you want to keep your domain objects free of any non-business related concerns (such as persistance), then you’ll want to follow the repository pattern using the ActiveRecordMediator class rather than inheriting from ActiveRecordBase like the tutorial shows. Some of the more experienced ORM users seem to see ActiveRecord as more of a gateway drug to NHibernate and ultimately prefer to forgo the conveniences offered by the ActiveRecord layer in favor of the increased flexibility and loosely coupled design offered by dealing directly with NHibernate instead.
Regardless of the approach taken, I definitely no longer believe that sprocs should play any significant role in any application. The current mandate in the software industry is to strive to lower costs by increasing developer productivity and ORM’s clearly help to do this by eliminating the need to write and maintain countless simple CRUD sprocs.
It’s definitely time for all of us .NET developers to abandon our convention sproc wisdom and start playing catch-up with the rest of the industry when it comes to using ORM’s.
Popularity: 31% [?]
Comments(2) 
whether the buzz was more hype or more reality was to experience it for myself.
I’m a little bit tired today because I stayed up late the last couple of nights working my way through the 
