My philosophy towards stored procedures has shifted so much over the past five years that I’m not sure I have a strong opinion on them anymore. At one point I would have told you with great fervor that you had to be doing data access through stored procedures to have a good application architecture, now I’m not quite sure. On the other hand, I’ve seen developers write hundreds of lines of procedural code to do set-based manipulations that would have been relatively simple to express as a SQL statement.
For me today the decision to use stored procedures comes down to the particulars of the application, the comfort level of the rest of the team, the relationship with an application DBA (if he/she exists), and the relative pain or ease of configuration management for database code. For whatever reasons, a lot, if not most, shops do a terrible job with configuration management for database code and structure. As a consultant I worked on an engagement with a client that went through way too much manual process rigamarole just to get a stored procedure moved into the development server. Of course, they handled the database code moves in an unrelated process separate from the code, allowing all kinds of bad things to happen.
I spent a lot of my early career writing Oracle PL/SQL, so I’m pretty comfortable with using and writing stored procedures in general. And to be honest, I think a lot of ex-DNA developers (victims) like me are inclined to write sproc’s because they were often easier to change and deploy than COM(+) DLL’s, especially with a clustered server architecture. Case in point, my current employer has a deep VB6/ASP/Sql Server background and stored procedures in T-SQL run rampant throughout our architecture. Developers who aren’t comfortable with object oriented programming will often use stored procedures as an extensibility mechanism (I don’t like this, but it does work).
When I moved to a shop doing TDD that was mostly stocked with J2EE veterans I was a little shocked at their disdain for stored procedures. In their world order, stored procedures were evil. Not very many of them could explain exactly why stored procedures were problematic, but they all knew it for established fact. Later on I started to understand their point of view. J2EE has a multitude of proven persistence mechanisms that isolate middle tier code from the database and eliminate a lot of manual SQL creation. In most cases, there was also a fear that using stored procedures would inevitably lead to business logic in the database. A general discomfort with relational databases probably explains a lot of the hostility towards stored procedures in some developer circles.
For many people the most important reason was that stored procedures are inherently more work to test than plain old objects. Just as importantly, unit tests that touch the database are slower to execute. A domain model approach coupled with some kind of metadata-driven persistence mechanism can lead to much easier unit testing and faster development overall. Stored procedures often simply interfere with persistence strategies.
Some O/R tools support stored procedures, but it’s often more trouble than it’s worth. If you want both O/R mapping and stored procedures, I’d actually recommend rolling your own tool. Writing a complete solution like NHibernate is hard, but creating a just good enough custom tool isn’t that bad. Reflection in .Net is pretty easy to use, and the System.Convert class makes type coercion relatively painless. If your domain model is small, just hard code the database mapper classes and move on with your life.
Enter the DBA
Another obvious factor in an organization’s attitude towards stored procedures is the relative strength and influence of DBA’s. Developer/DBA conflict can be even more harmful than the spitball fights between developers and testers. If a DBA is the only person allowed to write SQL, or has to approve every piece of SQL, you’re going to go slower. I hate having this kind of territorial bottleneck in the development process, but when you’re stuck with this situation my advice is to:
- Keep your DBA engaged in the development work of the rest of the team. Don’t let them work in a vacuum. It helps tremendously if the DBA actually has a stake in the success of the project.
- Aggressively use mocks or stubs to build business logic and user interfaces so you aren’t stuck waiting on a DBA. This also has the advantage of firming up the data access requirements before engaging the DBA.
- The DBA must follow the exact same configuration management practices as developers. Ideally, you want a DBA to check any changes into source control and monitor the continuous integration build and fix any problems from their check-in’s. Stored procedures are just code and should be treated as code. Make sure you help the DBA out with check in policies because it is a different world for most of them. It’s also cool as a way to give the DBA’s some visibility into the project at large. I’m plenty paranoid about this one because of some early issues on a project last year when the DBA decided to start optimizing some PL/SQL and broke the application for a while.