By Deane Barker | May 6, 2010 | 6 Comments
I am not a fan of stored procedures. I really dislike them, in fact. I know they have a place, but, in general, they’re insanely over-used. Specifically, I do not believe in using stored procs to wrap simple SQL. If you have a simple INSERT or DELETE, you don’t need a stored proc for this. Seriously — regular ‘ol SQL still works pretty well. The main problem with stored procs is that you’re creating yet another layer of abstraction for your app. And, in most cases, you’ll find that your stored proc layer starts looking a lot like your API layer. Usually identical, in fact. You have a DeleteArticle method to which you pass an ArticleId…which turns around and calls a sp_DeleteArticle stored proc, to which you pass the same ID. If you find that you have a problem in your app, you now have to hunt through yet another layer of stuff. If articles aren’t deleting, is the problem in the front end UI? In the business layer? In the object persistence API? In the data access layer? In the stored proc layer? Worse, SQL is a really obtuse language to do finely-grained logic with. I promise you its unlike the language you’re doing your actual programming with — C#, Java, whatever — so you’ll need to maintain yet another section of your brain for it. Oh, but you have a DBA sitting around at your beck and call to write your SQL? Well, lucky you, first of all…or maybe not. Now you are dependent on some other guy to get you access to your data. Need a change to how the data comes back? Open a ticket with the DBA group, buddy. Additionally, now you’ve gone around your carefully-crafted business API. A lot of the data logic in apps is at the API level, and SQL runs under that. Are you going to duplicate all your logic at the SQL level, or risk anomalies because SQL did something that your API doesn’t allow? This would be different if you got a lot of benefit from the proc, but most of the time I see procs doing nothing but wrapping single SQL statements. This usually doubles the calling code in your API, plus introduces another entire logical layer at the database level. So, what are the benefits? A few:
- If you’re doing some really complex data retrieval which involves temp tables, cursors, or other logical complexity at the pure data retrieval level, then stored procs are great. You don’t want to bring a bunch of data back to the client and use it to just go back and call other procs.
- Stored procs are good for gluing different data stores together and generating data that doesn’t really exist in the returned form anywhere. You can pull in data from all over the place, do some backflips with it, and send it back as a simple recordset.
- If you have security issues, stored procs can be good as well. You can give someone EXECUTE permissions on a proc if their needs are limited and their access should be too. (Though, you can usually do the same thing with a well-written view.)
I’m envisioning a new 12-Step group for recovering stored proc addicts. Will you join me?
Stored procs have their place, but generically they belong in the same category as the goto statement – “considered harmful”.
I knew a DBA who had managed to make it policy that all DB access had to be done via stored procedures. That sounded annoying at best.
The problems you describe come not from using stored procs, but from replicating your business objects 1 to 1 in a relational database. What you need is a datastore that deals with new/changed business objects without having to add/change tables and procs.
Interesting post. While I’d meet you half way in agreeing that much larger performance boosts come from properly defining indexes and getting your application to use the ideal ones at the proper time, I still think you’re dismissing a couple of macro issues:
1) Most web apps are not being built on well-architected Java or .Net layers that might already handle some security and performance precautions. They’re being developed on PHP/SQL frameworks, where there are plenty of ‘scripters’ who hack away at code and who are quite novice when it comes to SQL/database coding (and DB administration? what’s that? Doesn’t GoDaddy back things up every night automatically? That’s enough, so you’re fine. Yeah, right.)
2) The top security vulnerability in recent years is SQL injection, so while it might not be ‘necessary’ to leverage stored procs to gain more in the way of security, whey the heck wouldn’t you use them if you care at all about security?
So, I think it really comes down to a judgment call of: What level of expertise do you have on your development team? Were they computer science majors, or design majors (as many PHP developers seem to have been and then crossed over to it b/c it was fairly approachable) ?
If security and performance matter and if you don’t feel as though you have a solid engineering/architecture team for your applications, I’d enforce the use of stored procs and save a lot of headaches.
so while it might not be ‘necessary’ to leverage stored procs to gain more in the way of security, whey the heck wouldn’t you use them if you care at all about security?
Using a decent abstraction layer like the ones I mentioned eliminates the SQL injection process. If you’re going to add things like stored procs that provide no extra security beyond this, then let’s just not put anything on the internet at all, and stay super-duper safe.
I’ve quoted that article from Frans for a number of years now. Of course, we (not my day job) have been using his LLBL framework since about the time of that article so we kind of bought into that mentality a little. At work, people get crazy with SP usage.
Either way you go, you should try to do it as much as possible one way or the other. A benefit about using SPs is that you can then use tools like Red Gates Dependency Tracker or Search and find all references to the field that you are going to change. If you go the LLBL or NHibernate route, then again it becomes pretty easy to know the scope of a change you are going to make.