Can’t We Just Stop Living In The Past?
Posted by Davy Brion on August 5th, 2008
So we were about to start working on a new project at work, and everything was looking good. We were planning on using NHibernate for our data access, but today the client asked us to implement a “standard” data access layer with stored procedures.
Much like a Vietnam veteran who’s suddenly reminded of his traumatic experiences in the war, i too was speechless. Not only that, i was shocked. Chills going down my spine. Didn’t all those anti-war protests teach us anything? Why must we revert to all this useless violence? How many innocent developers must suffer needlessly due to these delusions? Why are the wrong people once again making the decisions?
I seriously don’t get it… i’ve been trying to comprehend this for a few hours now, and i just can’t. Now sure, i could just “let it go”, do my job and not worry about it. Then again, if i were someone like that, i probably wouldn’t have a technical blog and you probably wouldn’t be reading my posts anyway. See, i’m pretty passionate about software development, more specifically, about trying to do it in a good way. And when i’m subjected to ignorant constraints like these, i shiver. I tremble. I get pissed. I vent.
Still, in this case, there’s really nothing else we can do for various reasons i won’t get into here. A “standard” data access layer with stored procedures is what they want, so that is what they will get. It’s their money, after all. But still, what a stunning display of ignorance and shortsightedness. It’s 2008. This myth that stored procedures are the most secure and best performing way of designing your data access layer really needs to be put to rest. I sincerely doubt that any of the much-touted benefits of going with a stored-procedure data layer is actually still relevant in this day and age. Not to mention the fact that it is simply a waste of money. There are much more cost-effective ways of developing software these days and they really don’t necessarily mean you’ll end up with inefficient data access. Perhaps some day, we will no longer be confronted with delusional constraints that are only based on sheer ignorance. I for one, can’t wait. This might actually be one of the few good things about Microsoft’s upcoming Entity Framework. Perhaps even the IT dinosaurs will start to realize that maybe ORM isn’t all that evil or difficult.
August 5th, 2008 at 2:14 am
Did you have a chance to discuss this at all with the client?
Were you given any opportunity to educate the client on the pitfalls of this approach?
Were you given any reasons for the clients choice of data access strategy?
And finally wtf is the client doing dictating architectural concerns to the vendor!?!
I feel your pain, I am lucky enough to work somewhere where the boss says “Do it” and I get to choose how. Definitely curious to see your responses to the questions above (particularly the last two).
August 5th, 2008 at 3:35 am
Sad but true, there is a myth around store procedures that the people don’t let go easily. The most sad part here is the customer maybe doesn’t know certainly why he/she want store procedures in the application
Bye Davy
August 5th, 2008 at 5:12 am
Why can’t you use NH’s stored procedure support and get the best possible outcome given the situational contstraints? Also, I’d be willing to try to find out the root cause for your client wanting to have a “standard” data access layer. Are they going to be writing applications against your data layer? Do they want to create another front-end that talks to the stored procedures directly? If you have the reason, you can then either understand where they’re coming from and deal with it, or offer a better solution using your preferred tools. Interesting blog, BTW.
August 5th, 2008 at 7:04 am
@Neal Blomfield:
There wasn’t really a lot of room for discussion. The client in this case is an IT department of a large corporation. They also develop applications and this project will eventually end up being maintained by them. They initially said they preferred stored procedures, but we proposed the NHibernate-based approach last week. They had never heard of it. So they finally let us know that they decided against the usage of NHibernate because it wasn’t part of their standard toolset and that their developers would have to learn NHibernate once the application goes in to maintenance mode. You know, the old “we can’t have developers learning new stuff that we don’t know on our watch!” mentality.
@Dario Quintana:
yea, i really get the feeling that they simply don’t know any better… which is really a shame.
@Geoff Bennett:
When we proposed the nhibernate approach, the asked us if it worked with stored procedures. I told them it was possible, although not recommended. But at that point, they really didn’t have a clue as to what NHibernate was or what it does. And the “it’s an object-relational mapper” answer didn’t seem to help them either. So they looked into NHibernate, thought about it for 4 days and then decided against it.
August 5th, 2008 at 10:13 am
I personally keep surprised by posts like yours that ADO.NET + sprocs is an outdated approach. There is at least one simple argument which directly leads to a decision in favor of ADO.NET + sprocs:
-> You need to comply with security standards and you do NOT want to allow the application/process user read/write access to all db tables, but only to a set of controlled sprocs (I am not going to speak about all specific attacks which might take advantage of read/write access to all db tables etc.).
There are some additional ones:
-> The Database team wants to be able to change db scripts at runtime (in case of performance issue for example, locking etc)
-> Learning to use NHibernate and solve the problems with this tool adds complexity and takes time, which in some cases may be comparable to the effort of writing routine ADO.NET code (hopefully using some convenience wrapper).
August 5th, 2008 at 10:28 am
I don’t see the point in repeating the same tired security arguments regarding stored procedures so i’m just gonna refer you to this post:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
August 5th, 2008 at 11:23 am
We had exactly the same issue on a project last year. They had not heard of NHibernate and had all the same reasons for using sprocs – particularly their db guys. Fortunately, we had been left alone to design the app for the first few months, so we won the day mostly by default and the “it’ll cost you to change everything we’ve done so far” argument. It was a close run thing though and they were never happy with the decision – it came round for re-review every couple of months!
I therefore sympathise with your position – it’s a wide world of opportunities out there…
August 5th, 2008 at 11:45 am
I’m assuming that you are still using an object model for the domain? Or are they wanting the whole application is a transaction script manner? If it is transaction script and a simple domain, then fair enough. Maybe Active Record would be better, but it’s the client’s money and the client’s choice.
If you are going to have a domain model, then it’s not just about the way you actually access the database (dynamic sql vs. SPs), it’s about all the functionality that you will now have to implement which comes out of the box for an ORM like NHibernate.
You’re going to need to keep track of the objects that are in memory so that if you access them again you are not going to get another copy and have concurrency issues – you need to implement an Identity Map. You’re going to have to keep track of the status of objects so you can tell if they have changed – Memento / Snapshot patern. As well as dirty objects, you need to keep track of newly created and deleted objects – you need to implement a Unit of Work pattern. You’ll have to handle lazy loading of objects / collections – proxy.
Not only does all of this need to be implemented, it needs to be tested. On top of this you’ll probably have a lot of boring, repetitive code mapping the values from the DataReaders / DataSets to your business objects or DTOs. This is a helluva lot of infrastructure before you’re even tackling the value-added business code!
You say this to a client and they might start to question the need for the object model!! “Too complicated, too much work, we never needed all these things before.” And they’ll be right, most business applications I’ve seen tend to get round these issues by ignoring them. Work in a procedural manner, get all the data sent to the database and put all your logic there. Which works fine until… well… it doesn’t. As soon as you get any level of complexity at all you have a big unmaintainable ball of mud… Then you’ll probably decide to get consultants in who’ll say you need a domain model and an ORM…
August 5th, 2008 at 2:50 pm
Store procedures are more performant than SQL queries. If you need high performance you need stored procs.
Don’t believe everything you read on the internet. Afterall Elvis is alive http://www.elvis-is-alive.com/ and Bush is a nice guy http://uncyclopedia.org/wiki/George_W._Bush
August 5th, 2008 at 8:17 pm
@Michael Smith:
there’s no way i’m gonna write semi ORM functionality on top of stored procedures
, the budget wouldn’t really allow that either so i’m affraid it’ll probably be transaction script and pretty simple objects, probably just flat DTOs even
August 5th, 2008 at 11:10 pm
@Peter:
it totally depends on the usage scenario i suppose… no doubt, some stored procedures will be faster than their equivalent regular queries. However, i seriously believe the performance picture can differ tremendously for most typical business applications.
Can stored procedures give me the same amount of flexibility when it comes to reuse of queries, while at the same time allowing me to minimize the amount of network latency i have to deal with (which is a killer for performance as you undoubtely are well aware of) and have it all be usable in a sane, and productive manner? Some of my recent posts pretty much prove that those goals are indeed achievable. But with stored procedures? I seriously doubt it.
And what about the claims regarding performance that Frans Bouma makes in his post? Sure, that post is 5 years old now… and i am certainly no SQL Server expert, but considering the popularity of dynamic sql these days, would that make it more or less likely that Microsoft has improved their performance? Be very sure to keep the long term goals of Entity Framework in mind before you answer that question
Btw, the people who claim that stored procedures are what you need to use if you need high performance usually post their statements on the internet as well… why on earth should i trust them?
August 5th, 2008 at 11:23 pm
Hear hear.
Face it, the people who use an ORM in their daily development life are a minority.Most of the time they are the same one’s that live in the alt.net world, developers who look at other tools than the standard MS ones, accidently they happen to be your readers.
August 5th, 2008 at 11:37 pm
@Benny Michielsen:
I think that’s an entirely different discussion though… but i’d like to hear how you’ll think about it in about 2 years from now, after Entity Framework has been out for a while
August 5th, 2008 at 11:51 pm
Don’t get me wrong here I use NHibernate and wouldn’t want to go back, luckily the company where I work is quite open minded.
The only thing the Entity Framework can do at the moment is raise awareness, that there is another way of doing things. Most people I know personally in the industry (the one’s that went through the same education) are still using their hand written ado.net data layer. I’m not saying it’s bad but the power and ease of development you get when using NHibernate is so much better. But why am I even saying this, I don’t need to convince you
.
August 7th, 2008 at 3:06 pm
Davy,
Your story sounds very familiar. What always strikes me the most is the fact that opponents of NHibernate are afraid of learning it. However, those people don’t have a problem with learning awful, mastodontic Microsoft API’s like the WebForms page lifecycle for example. Who, besides Scott Guthrie and a few others, fully understands that piece of crap. It doesn’t seem to bother anyone however. Asking for a decent ORM, then all hell breaks loose.
Anyway, my condolences. Best of luck
August 7th, 2008 at 4:57 pm
HAHAHA sorry Davy, I tremendously feel your pain cause I am in the same position and even worst. Worst cause they decided to dont use any Business objects or Entities but rather to use DataSets and do all of the communication doing DataSets (ahhgggg kill me) After using NHibernate I feel I am going back to the Cave Man era haha.
August 8th, 2008 at 3:22 pm
Hi Davy,
Have you consided using SubSonic?
Its a very light ORM, you just point and shoot really, would be very easy to handover at the end of the project. It can generate a very simple typed stored procedure layer for you and would take away some of the pain.
Alternatively, how about using CodeSmith, there’s plenty of pre made templates to generate a very simple ORM for the stored procedures. At the end of the project you could hand over the classes and they could continue to use CodeSmith if they wish or make them themselves.
August 9th, 2008 at 3:16 pm
@Ralph,
no, haven’t considered SubSonic… haven’t actually played around with it either. It wouldn’t really matter anyway, the client is extremely cautious of every approach they aren’t used to and they don’t want their developers to have to learn something ‘radically’ different when the application goes into maintenance on their end.
August 13th, 2008 at 12:50 am
I give another vote for subsonic. Then at least it’d ease some of the pain, plus they’d get their sprocs so they’d be happy. I’m with Ralph though, at least do something to wrap the sprocs in something so that you don’t have to deal with it.
Btw, the argument for performance or security is a clear tell of someone that can’t get over that their program executes .0001 second slower and is willing to sacrifice development time and architecture and screw things up for the rest of us. Beware.