Simon Evans some time ago questioned if given the parametric nature of queries generated by ORMs, stored procedures still have a place in developers toolbox? At the end of the day why bother if ORMs seem these days seem to do everything for free? It looks to me that statement that "stored procedures are dead" (although Simon has never said that) has gotten a life of its own and some people made it into a mantra of sorts... Argument, which Simon quotes, that ORM generated parameterised queries are as performant as stored procedures is absolutely valid and from this point of view stored procedures are redundant: the days when we had to implement every single CRUD operation as a sproc are gone. People seem to forget however that using sprocs for performance was not the only reason to use them.
The primary reasons why stored procedures are still worthwhile to be considered is the principle of isolation: personally I strongly believe that as much as we can, we should avoid dependencies between business and database layers. Dave Hanson with whom I work on a project for a large executive recruitment agency posted his doubts on his blog and his views are very close to mine, these days we often find ourselves on the receiving end of these dependencies when the DBA decides to change schema of the database and we have to refactor large portions of program logic to deal with the change. Having said that dependencies are not the only problem with ORM based persistence layer.
First major problem which is a "feature" of all ORMs is the problem of lost updates, as their default mode of operation goes along the lines of:
As any SQL aficionado will immediately recognise, this approach is a potential disaster in a multi-user system. If two of these batches were to execute concurrently, there is a fairly good chance that one would override the changes made by the other and instead of final value of initial balance + 20 we would end up with much less. Surely, this problem can be overcome by implementing optimistic locking, running the entire procedure within a transaction with sufficient isolation level etc but why bother when the problem can be solved easily with running a piece of code along the lines of:
Not only is the second approach simpler, it also works well in a multi-user environment and minimises lock contention.
Yet another reason why stored procedures are helpful is simply the power of SQL which is extremely efficient tool when it comes to handling large sets of data: when writing a stored procedure a developer has at his disposal all the weird and wonderful inventions of SQL including aggregate functions, non-equi joins, common table expressions etc. and the list goes on. On a recent POC for easyjet.com I used SQL tricks which would be simply awkward if not impossible to implement as operations on table based entities. Strangely enough results of those queries were successfully mapped to a set of WCF data contracts using LINQ to SQL mapping attributes because if anything is certain, it is the fact that manually mapping datasets to objects is a thing of the past (unless of course you are hardly pressed for performance which is another story).
Technorati Tags:
SQL,
ORM,
.NET