Thursday, March 16, 2006

To stored procedure or not to stored procedure?

I've just been reading this post by Frans Bouma 'Stored Procedures are bad m'kay' where he rebuts some of the usual arguments about why you should use stored procedures; security, performance, encapsulating database access. I've been doing enterprise application development with SQL Server for around ten years now and every application I've worked on we've used stored procedures. I was told that stored procedures are good on my SQL Server 6.5 course all those years ago and have never really questioned that view. And I'm not alone. Pretty much every developer I've met over the years has shared this belief that stored procedures are god's own data access tool. On my current project, any suggestion not to use them would be met with horror. Hmm, but I'm begining to question this view, just like Frans Bouma. A couple of things are begining to sway me away from procs. Firstly, Frans makes a good case for rebufing the security and performance concerns, but I think more importantly there is the shift of emphasis in application development away from the relational data model towards the object oriented domain model. This shift is much more advanced in the Java world than it is the Microsoft camp, but us lot are begining to listen to the Java people. If you start to believe that the database should be merely a data persistence tool then the whole API argument with lots of business logic in the procs starts to look less viable. Instead the Elephant in the room these days is the often quoted 'object relational impedence missmatch'. Maintaining all that data access code and all those CRUD stored procedures becomes a major headache. On my current project I've mitigated this to a certain extent by building a code generator that builds all our CRUD procedures and .net wrapper code for us (probably re-inventing the wheel to a certain extent, now that I've discovered Code Smith) but it's still a headache. I haven't used NHibernate or any other OR tools, but I'm getting keener to try. Anything that automates all that boring data access stuff has to be worth a try. So, stored procedures? Well I wouldn't be at all surprised if I'm not using in them in a year or so.

No comments: