The unmentionable subject

Posted by Jeremy Miller on April 6, 2008

I’ve been accidentally cut off from the altdotnet discussion group over the past couple days and I wanted to catch up this morning.  Unfortunately, the first thing I see is a conversation about to Stored Procedure or not.  Not gonna join in or even going to read.  There’s been absolutely nothing new written or thought about the subject of sproc’s for years.  I’m happy to agree to disagree with the sproc advocates and peacefully go on our separate ways.  There was something in the water about 3-4 years ago that allowed some of us in the Microsoft developer camp to convert away from sprocs, but those conditions are apparently gone now. 

Just let it go.

About Jeremy Miller

Jeremy is the Chief Software Architect at Dovetail Software, the coolest ISV in Austin. Jeremy began his IT career writing "Shadow IT" applications to automate his engineering documentation, then wandered into software development because it looked like more fun. Jeremy is the author of the open source StructureMap tool for Dependency Injection with .Net, StoryTeller for supercharged acceptance testing in .Net, and one of the principal developers behind FubuMVC. Jeremy's thoughts on all things software can be found at The Shade Tree Developer at http://codebetter.com/jeremymiller.
This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Gregory Madsen

    I agree that there has been so much written on the subject that it really is getting to be too much focus on the one subject. Better to spend the time building solutions than arguing. For the newcomers, there is plenty to read by doing simple searches. I think what Jeremy is saying is that there has been nothing _new_ written, so there is no point continuing to rehash the arguments over and over again. Let the existing body of explanations and discussion serve for all to read through.
    I am just on the verge of entering into the debate at my new (3 months) job. We’ll see how it goes, but I suspect that I will have to deal with all the old the arguments. I like your idea of just letting it go, and not trying to convert anyone that will not listen, read, or learn. I intend to just quietly introduce the ORM model and see what happens. I am tired overhearing comments to the affect that “the sproc wasn’t updated for some reason…..” etc.

  • John

    lol… I agree on the CRUD thing. I would (almost) NEVER write a proc for basic crud. I’m talking about unusual needs where a proc will run faster or be a better fit for what I’m trying to do. Where I need to shave milliseconds off a call for whatever reason. In the course of two production apps, I’ve probably used three sprocs. But, when thinking about new developers, or at least, new-to-the-conversation developers, I think we all have to be careful to act as if there can never be exceptions to generally accepted principles. To do so is to 1) wrongly close ourselves to new thoughts on old ideas, and 2) make it harder for a reader to handle said exceptions because they’re told that certain “principles” can never be broken.

    Keep up the good work,
    John

  • http://codebetter.com/blogs/jeremy.miller Jeremy D. Miller

    And curse you John for getting me into a sproc argument!

  • http://codebetter.com/blogs/jeremy.miller Jeremy D. Miller

    John,

    I don’t think anybody is arguing with you that sproc’s are performant, the argument has always been that parameterized SQL is good enough now.

    I’d disagree with you about Linq to SQL making sproc’s acceptable. We’ve had the ability to codegen .Net wrappers for sproc calls for a long time, so there’s nothing really new there. It’s still the issue of:

    1.) Why do I want to bloat my code with these things and add to my config management burden? Just let the sproc and code get out of sync and watch things go funny.
    2.) Why do I want to write basic CRUD stuff that an ORM will do for me just fine?
    3.) Do I really want to make my object mapping take longer because of the extra mapping for each sproc operation? Seriously, one object to table mapping, or 4+ for each sproc. If the sproc’s are generated by the ORM tool, what’s the advantage?
    4.) Do I really want using sproc’s to force me into writing procedural code? ‘Cause that’s effectively what sprocs will often lead to.

    And no, sproc’s are no more or less testable than they were before Linq to SQL. You could always mock the DAL, and the actual sproc is still a PITA to test if it has any logic in it.

  • John

    Jeremy, you’re always a reasonable voice. Don’t pull a Bellware with such bold statements as “absolutely nothing new” ;) . Seriously, you’re mostly right. However, I’ve found myself writing a couple here and there now that Linq to SQL makes it so easy to use them. Prior to this, I would never touch them. With Linq, they’re abstracted with a method call, so they’re still testable. And like it or not, they are very performant if written correctly.

  • http://www.blogcoward.com jdn

    You’re not going to talk about it by mentioning it? Interesting.

    One thing to keep in mind though, is that the pool of developers is ever increasing. While you and I might have already gone around and around on topics like this, this topic is genuinely new for other people, and a topic of concern (perhaps in the workplace). Even if they’ve read the Frans’ inspired threads of the past (among others), they still might need to exercise their voices.

    Even if it is annoying to others.

    jdn

  • http://www.storycardsoftware.com Tim Walker

    Stored procedures are terrible, terrible, terrible. They embed business rules in a layer that has not been abstracted, make mocking impossible, violate every sense of layering and more. This is not negotiable, this is bad design creating fragile implementations. Always code to a persistence abstraction. Period. As far as the DBA’s go, let’s face it folks, we’re 5-10 years away from obsoleting RDBMS, there just won’t be a need.

  • http://elegantcode.com Tony Rasa

    arguing sprocs: +1 to just letting the argument go away. The DBAs will never agree, they need job security. Its just not worth the time arguing about it. I prefer just keeping an uneasy truce with them, let them think that they’re so important to the organization, while I just get my work done in the most direct fashion I can manage (usually a mix of dynamic parameterized sql and stored procs). Some day, they’ll all (99% of them anyway) be replaced by a series of very small shell scripts anyway. We should pity them.

  • http://www.slickcode.net Torkel

    sprocs can be useful for specific tasks (like for scheduled database maintenance, big batch updates), but 99.9% of the sprocs I come across are not in that category :( just yesterday I fixed a bug in a sproc that was 300 lines of the same basic query duplicated 10 times with very small variation.

    The Microsoft stored procedure “best practice” recommendation still part of a lot of .NET developers mindset.

  • http://blog.codiceplastico.com Emanuele

    Ops!
    :-|
    Sorry, I started the conversation :-)

  • http://codebetter.com/blogs/jeremy.miller Jeremy D. Miller

    @Matt,

    The historical context is that the .Net community has had endless debates where we’ve argued the usefulness/harm of using sproc’s with little progress or useful discourse. I’m happy to concede that there are still times I would use a sproc (I’ve written exactly 1 from scratch in the last 4 years and modified a couple existing sproc’s that did database maintenance tasks).

  • Matt

    Huh? I lack historical context so forgive that. But conditions are fine to accept that sprocs are marginally useful. I assure you because I have converted away from sprocs in the last few months.