Stored Procs vs ... not Stored Procs (ORM)

Over the last few years I had believed that the general consensus was that stored procedures had become largely irrelevant for business logic (CRUD) DB interactions. I believe this would have a lot to do with the large increase in popularity of ORM's, Hibernate and Linq-to-Sql in particular. I understand they may still be necessary for reporting/data import/etc.

However it seems that there are a large number of people still believe stored procedures are the better option. Each to his own I guess but they seem to come up with some strange reasoning behind their decisions which I want to explore. Basically I wanted to outline my own experiences by looking at different aspects and see if I am missing the point somewhere. In general I much prefer to provide access to a database via a service layer.

Note: I'll specifically talk about ORMs here but inline sql is just as applicable (if not as desirable though :) .

Performance For all intents and purposes ORMs are just as performant as stored procs. Batching queries tend to make up for any shortfall in database round trips. Stored procedures that manipulate large datasets can be tuned more effectively. I wouldn't even attempt to do this with code. Basically I feel manipulating large datasets is one of the only reasons to actually use stored procs. This is where the DBA's expertise comes into play.

Security Not sure why anyone would think that stored procedures are more secure than direct table access. Is it that hard to set the correct permissions on DB objects? It always scares me when developers have access to live databases. I used to work in a government department and you needed written permissions from the "business owner" as well as the head of the deployment team if you wanted to get ANY information from a live database/application.

This way we only needed to set permissions on 3 logins (sa, application, reporting).

Scared about SQL injection attacks because those lazy dev's who don't sanitize their input. See Testing.

Loosely Coupled Not entirely sure why people think this is a good idea for a database ie the DBA can change the underlying query/t-sql etc without changing the interface. Great. How often does this need to happen? What processes are in place for testing, version control, etc? Applications and databases are tightly coupled by nature. At the moment, if my business requirements change, I need to make at most 3 changes - DB Script/ORM/Code. I don't want to have to write a bunch of extra update scripts to change a bunch of stored proc "interfaces" as well.

Reuse of Functionality People argue that multiple clients can access stored procs which is DRYer than each client rewriting the same functionality. This scares me. Not to mention the overhead of configuring security for each client, but any shared complicated business logic NEEDS to be pushed down into the the database. Personally I find it much easier to express business rules with OO concepts etc.

Now we have DBAs with the ability to change business rules without needing to go through all that guff such as version control, peer review, comprehensive testing, documentation etc ;) I'd much rather have my rules locked down in a dll where only the qualified development teams have change rights. And yes that does require a recompile. OMG the horror of a recompile!!! With the correct processes in place, this isn't a big deal.

Script Management Personally I find stored proc buckets to be a PITA to manage. There's a reason people prefer to write business rules in Visual Studio rather then Management Studio.

Testing Last time I checked there were far better options for testing applications as a whole. Show me the equivalent stack of Unit/Integration/Acceptance/Regression/Load/Security/etc tools for testing and profiling stored procedure changes?

Portability Wow. I can't believe some people think that stored procedures are good because both a) Reuse of functionality allows multiple clients to access the same database, DRY etc and b) stored procedures can be ported to a different database. Last time I checked there was no standard stored procedure language that could be used across all database. Hell, most SQL isn't even portable. ORMs on the other hand...

Yes I know Linq-to-Sql only supports SQL Server, just use EF instead :)

Seperation of Concerns SoC... huh? I thought we were talking about databases.

Easier Refactoring WTF! Now they're really clutching at straws.

"You don't have to write inline SQL anymore!" I give up...



Another big negative I see is the fact that a lot of devs I know shouldn't be writing stored procedures. Large scale use of stored procs would require far more time from a DBA when a dev could get a lot more done with a lot less effort. Let DBAs use there expert skills where they can be put to good use.