I promised myself that I wouldn’t ever make another post about stored procedures, but Eric’s post on sproc’s hit a few of my hot buttons on the subject. Four years ago the pre-Agile, VB6/ASP coding me would have fervently agreed with Eric’s pro-sproc stance and I wrote PL/SQL by the bushel full, but today my answer to the sproc question is a firm “no thank you” or at least a “guilty until proven innocent.” Besides, I’m an Oracle guy in a Sql Server shop and I despise T-SQL.
First, some common ground. I think we can all agree that adhoc SQL in code ala ASP circa 1998 is an abomination. I don’t have that much trouble with using sproc’s for CRUD, but then again I think a domain model approach backed by O/R mapping is more efficient in terms of the all important developer time and the domain model approach leads to better code. The newer parts of our applications can accommodate database changes by simply changing the NHibernate mappings. No sproc’s necessary.
So to Eric’s post, first this:
So when I see people in the TDD crowd slamming stored procedures and views it greatly confuses me.
That’s an easy answer Eric, stored procedures make TDD a slower, less productive process. Business logic in stored procedures is more work to test than the corresponding logic in a domain model class. Referential integrity will often force you to setup a lot of other data just to be able to insert the data you need for a test (unless you’re working in a legacy database like ours without any foreign keys;)). Stored procedures are inherently procedural in nature, and hence harder to create isolated tests and prone to code duplication (a duplicated “where” clause is duplicated code and duplication is bad). Another consideration, and this matters a great deal in a sizable application, is that any automated test that hits the database is slower than a test that runs inside of the AppDomain. Slow tests lead to longer feedback cycles. Trust me on this one, a team will be much slower with a 20 minute CI build versus a 5 minute build time.
I’ll make an admission. I bet I haven’t written a full 100 lines of direct ADO.Net manipulation code in the last six months, yet we’ve rolled out a multitude of new functionality in that time frame. I also write very little non-trivial SQL these days. Between a little OO query engine we’ve developed and NHibernate, there’s simply no need to spend time in the muck of data access. Like Jeff Atwood said, stored procedures are the assembly language of the database. I’ll use sproc’s when I have to have a performance gain, but until then they’re nothing but premature optimization.
I think the gap in thinking about sproc’s is about how you see the role of the database in an application — do you talk about the database in passing as a persistence mechanism or is the .Net code merely a way to get data back and forth from the UI and database?. The applications I build are primarily about business rules, not reporting. The database to me is simply a persistence mechanism for our domain objects and messages en route. Maintainability, which is almost synonymous with testability, is king. A large, comprehensive body of automated tests at both the acceptance and unit test level are, in my opinion, the best way to accommodate changes to the code.
Again from Eric’s post, and this is where I got hot under the collar (it’s not directed at you Eric) –
Your DBA team can work completely independently of your programming team and do pretty much whatever they want and your application doesn’t care!
Expletive. NO, NO, NO!!!!! The DBA should most certainly NOT work independently of the programming team. Stored procedures are code, and potentially destructive code at that. If you change a stored procedure you *MUST* integrate and test the stored procedure against the application before it gets anywhere near production. I’ve been burned by this one too many times. The stored procedure code absolutely has to be built and versioned within your Continuous Integration build. I’m sick of bugs caused by a sproc getting out of synch with the code. The idea of a DBA, or a maintenance developer, putting a different version of a sproc directly into the database is a bad, bad, dangerous practice.
The extra configuration management burden is one of the reasons most Agile teams stay away from stored procedures. If I’m relying on NHibernate mappings or parameterized SQL in the code that SQL is going to be built and versioned with the C# code automatically. My risk of having mismatched sproc and C# code is greatly minimized. Our C# assemblies are signed with the CruiseControl.Net build number and it’s simple to spot mismatches The stored procedures are, err, well, uh, I don’t know if that’s the same version that the code was tested against to be quite honest
The absolute worst thing you can do, and it’s horrifyingly common in the Microsoft development world, is to split related functionality between sproc’s and middle tier code. Grrrrrrrr. You just make the code brittle and you increase the intellectual overhead of understanding a system.