Revisiting the Ad-Hoc vs Stored Procedure Debate
So I stirred up a hornet's nest on a debate I honest did not know was so heated out there, and that being Stored Procedures vs Ad-Hoc queries. I must admit I have learned a lot, amongst other things is not to be as dogmatic about the topic if nothing else. I also think I learned to be a little more clear about what was really griping me at the time, again it was a 20 minutes rant one night.
First let me explain a little more about what I was ranting against, the careless use of ad-hoc queries in user interface and business layers of our applications. I guess since I have been out in the real world for at least 12 years building applications and specifically web applications I know this is not how to write an application using a modern relational database. If nothing else it will leave you wide open to SQL Injection attacks and trust me I have done this just as an exercise to see how common this can be done.
I was taught stored procedures over ad-hoc queries. It naturally divides logic and allows for a more robust security model, etc. I think I still subscribe to these points, but I think we have done much in the last decade to make these points not so absolute.
The next is stored procedures are faster than ad-hoc, well that I was wrong about, and I admit it. They are roughly the same and often negligible in difference these days, at least on SQL 2005. I could not say on the other platforms.
So what has changed my mind? Well honestly it was talking to many smart folks, learning about ORMs and several other things. I do think it is wrong to say never use stored procedures because I think they will do some things better than ad-hoc queries and visa versa.
Finally, I am actually getting tired with rerunning my Code Generation scripts to build the stored procs, DAL and Business objects. I am really starting to like the work an ORM will do for me and I am all for not doing work I can avoid.
So with that I am off to work on a summer project using the Entity Framework, which I know is controversial itself. I had to change my stance a little, maybe it is not as bad as folks are saying. But let me see what I learn and can share with you over the next few months.