Friday, December 26, 2008

The great dead horse of ORM vs Sproc

I'm not the first to dive in this at all, but here is my pov. I'm only mentioning this because I still see it cropping up a lot on various blogs and message boards as if there was an actual valid controversy.

Let me recap the basic sides of the argument:

ORM side
  • This is the haven of coders
  • Automatically creates your sql for you so you don't have to worry about handcrafting sql
  • uses normal source code so you get easy refactoring and source control
Sproc side
  • Position of pretty much all DBA's, some coders typically from a more traditional development software development model.
  • Been recommended model for development for a couple of decades (since stored procs came into existence )
  • Optimal performance

Now with those points in place if you were a neutral observer (which is a mythical creature on this subject), you would think i'd come out on the side of sprocs with the benefits in performance and it's proven history...and you'd be wrong.

The above is how I'd say a majority of people I've talked to involved in software see things, but reality is I'd put refactoring and source control as being OVERWHELMINGLY important and performance as being secondary and rarely your limiter.

Finally, I think it's intellectually lazy to just go with what's "been done in the past" regardless of existing realities and what changes today brings.

So lets go blow by blow:

Source Control

Without source control we're left never making changes unless we absolutely have to. This has subtle awful effects on software being reused and growing to match what end users want and need to use.

Source control is one tool that helps software change be safe and thoughtfully done. Sprocs while you can keep the scripts in source control to make or alter the sprocs, this means the dba or whoever is editing them has to be manually disciplined or a third party utility has to make the process seamless.

In my experience this does not work without constantly standing on someones head (including your own) that you always check in sproc changes. In the end, everyone is reluctant to change the application.


So lets say a customer or end user comes to you and requests a simple name change of a column in the database. There may be many reasons for this, sometimes it's for a report, sometimes its compliance with a regulation, sometimes it's someone with power who is whim driven. Regardless you now have several ways of dealing with this.

With the orm approach, you renamed the property in your orm class and since most IDE's have integrated mass rename where all references are updated in one click, all you're left with is renaming the table column (or just mapping the new column name in your orm class).

With sprocs you're in a whole different world. Now there is no auto-renaming tool I'm aware of for sql servers (Redgate may have something expensive) and then I doubt there is one for mysql, mssql, db2, etc. So not only do you to renamed code that you've got to change and test your udf's now that may depend on that column name.

In some cases this could be very quick, but in some practical cases I've worked with this just leads to no one being willing to enact such a change or they just cheat the system a bit and only rename the property or column in one place that makes it appear it was changed.

Things get worse as the issues become more complex however I'll assume you get the idea, again this leads to being reluctant to change the application.


Performance is nice, but if you REALLY need it, sprocs will not be what makes the difference. I say this with a couple of caveats, depending on your ORM library bulk updates, deletes, and inserts are going to be slow..very slow, but they have to be REALLY bulk. Otherwise scalability in the truest sense is gotten by application design thought through at a systems point of view using messaging systems and growing horizontally.

Lets say sprocs were somehow 25% more performant than hand sql/orm, they're not, but if they were imagine say you had an widget seller app that could process 700 transactions a second.

The widget seller company comes out with a buy one get one free coupon, now transactions are regularly over 100,000 a second. If you were using sprocs or not, you're done for.

However, if you've written an app that uses a messaging protocol and can be spread out over multiple datasources (or even one datasource that's clustered), with several web servers and several middle ware maybe actually able to scale to that 100,000 a second transactions regardless of if you use sprocs or not.

Scaling out and the use of asynchronous transactions are the actual key way to achieve scalability...everything else is a fake wedge issue.

Most Importantly

Generally speaking anything that gets in the way of giving the customer what they want is very very bad. It leads to applications getting scrapped repeatedly because either the customer has replaced you.

If you're lucky, you've convinced them that yet again they're wrong and did not properly know at the very beginning exactly what they wanted, please deposit another 40k in development to generate the app you desire...this time it'll be perfect we promise.

Heavy use of sprocs are hard to justify when it comes to the maintenance side of things.


Ultimately I think this does come down to a real valid pull between what dba's have been taught is there job and what agile developers needs are. With the proliferation of agile growing daily (and I'm certain the quality of the agile attempts decreasing as it becomes more and more "the thing to do") it's natural and logical to see this red herring happen over and over again.

Are there other things to consider in this argument? Oh definitely but I've yet to find anything that tops maintainability and adaptability as key reasons to avoid sprocs.

This argument I'm certain will continue indefinitely, and I'll in the near future have to call some 2000 line business logic heavy stored proc that takes 10 minutes to read and then setup on a test database to make sure i'm understanding what my changes will do, but I'll go out of my way to document and then see if i can move it all to unit tested, source controlled, good old boring bog standard code.

No comments: