Yay! A new Stored Proc vs. Dyn. Sql battle!

18 Comments

• Hurrah, another pointless trolling war!
  

• It’s a conspiracy Frans, and you’ve dragged yourself into it, again!
  

• Heh :). Noo, I'm not going to reply with new text, trust me, I've had enough hate-mail related to this topic. ;). Though it's fun to see how others seem to find it necessary to re-hash this battle from the stone-age over and over again :D
  

• You must be tempted though, Frans...
  
  I know I am.
  

• Nah, I've said everything that's to be said from my part. I always point to the article on TheServerSide.NET I wrote (link in the post). It took me a day to write that lengthy text, and it includes what I think about the subject.
  
  But as you can see in that discussion as well, it's a stupid discussion. People argue about a subject they don't want to understand another POV about, mostly because of reasons not explained in their reasoning.
  

• Don't worry Frans, by the end of the week I'm going to put a spam filter on for "stored procedure" and never let the term it into my blog ever again.
  

• Hi Frans,
  
  Did you try searching for the posts - if you had you would have found that I apparently have not removed those posts :)
  
  http://weblogs.asp.net/rhoward/search.aspx?q=stored+procedures&p=1
  
  I believe it's actually the first result on the search results.
  
  I've backed off my original statement of dynamic SQL is all bad (as long as it's parameterized is ok), but I still will always still use and recommend SPROCS first.
  

• I say pick the right tool for the right job. I think the discussion is too broad. If everyone wants to argue, narrow it down. Are you talking about simple crud operations or complex reporting type queries? Is this a web based application or desktop app or a web service? is it a prototype or mega production system? Is your priority code maintence or minimizing development time? or maybe its performance? Are you using an ORM? What type of database server are you running?
  
  A couple of notes:
  
  On performance alot of times another server is a hell of alot cheaper than ten architects debating how to get an extra ounce of performance out of an application.
  
  triggers seem like an easy way to implement a changelog pattern.
  
  .net has declaritive security at the application level.
  
  There are probably situations were running a query on the database would possibly return a fater result. sum(x) comes to mind, although you could put that in dynamic sql. But, compared to return all objects and summming on a field the database solution is faster. Again, this all comes down to what are you trying to do. What runs faster at one time does always scale well.
  
  Those are my 2 cents.
  

• Rob, no I didn't try a search, I clicked on the link in my original post. I'll update the post, thanks! :)
  

• And all those links are just from .NET + SQL Server users. Just think of all the flamewars on the Java ERB + Oracle side of things.
  
  

• A *big* YAWN!!
  
  Seriously, I thought this topic was old.
  

• Scott: indeed... :) Also a killer: mentioning in the newsgroup comp.databases that stored procedures really suck and shouldn't be used :)
  
  Sahil: of course! that's the fun part: people are *still* bickering about this topic as if it's going to change anything :)
  

• Sorry, but it isn't misleading at all.
  
  "One or more SQL statements precompiled into a single executable procedure."
  
  This does NOT imply a compiled and stored execution plan, how would that make sense? It says "procedure", not plan.
  
  What if I alter the indexes after I create the stored procedure? I could then wind up with a sub-optimal execution plan which had been compiled with the procedure.
  
  Also, the execution plan is NOT the only thing that counts, compiling the query into an executable object which can run against an SQL Server instance ALSO generates overhead, which is saved by using a "Stored" procedure, which leads to guess what, "better performance" :-D
  
  Query plans are a separate topic, hopefully this helps to clear things up. You are pointing to a different topic in the documentation...
  

• The bigger challenge is convincing the DBAs of the world to embrace dynamic SQL. With DLINQ/ADO.NET v3, and the current generation of ORM tools, DBAs with a stored procedure heady mindset are seriously causing a lot of people to make architectural mistakes.
  
  .. but what about security? [ToungueInCheek(true)]
  

• Jace: Yes it IS misleading, and I'll explain why: 'compiled' gives people who don't know the fine details the idea that a procedure is a compiled piece of code like a .exe, and any other query is interpreted. (and thus slower).
  
  This is the main issue. Both are interpreted, namely their execution plan is interpreted. To get to the execution plan, the dyn. sql query has to be parsed, the proc is already parsed. However the next time the dyn. query is run, parsing doesn't have to occur, the execution plan is already there and is re-used.
  
  My point against the 'compiled' argument is thus that it's not stored 'compiled' as an .exe, but in post-parse state, which is a tiny advantage, though on a query run, this is often ignorable, unless your query is really complex.
  

• Firstly, in respect to one of the comments which cast aspersions on the scalability of SQL Server, I would like to point out that I have a client with a website that daily attracts 40,000 users within a 15 minute session period querying and rapidly updating a 60GB database. Performance is lightning fast. Reason -- a lot of effort goes into writing efficient stored procedures with optimal data access and carefully chosen indexes. The database isn't merely "an object persistence engine", but is viewed as the solid foundation for performance and scalability, without which no application can scale.
  
  Secondly, I take great issue with this new term "early optimization" and the identification of this as some kind of problem: As a consultant that frequently has to pick up the pieces resulting from developers following the latest trend in framework choices, I can say quite confidently that this "early optimization is a mistake" is simply a cop-out by developers keen to implement version 1.0, but who will not be seen for dust when it comes to dealing with the performance problems 6-12 months down the line.
  
  Put quite simply, if your foundations are shakey, how on earth do you expect to build scalable, high performance code upon them?
  

• Ian: your comment is precisely the reason why this discussion is still
  popping up from time to time: it's shortsighted. You know why? Because
  I have customers who have databases which get millions of row
  mutations/additions a day, using ... an O/R mapper generating SQL on
  the fly.
  
  
  So who's right? Not clear.
  
  
  
  "Put quite simply, if your foundations are shakey, how on earth do
  you expect to build scalable, high performance code upon them?"
  
  So, this website's logic, is that written in hand-optimized
  assembler? C++ perhaps? Or some VM using language? if the latter, why ?
  as it costs performance: handoptimized assembler is still king.
  
  
  Also, SQL generated on the fly can be faster than procs, simply
  because it is made to do the task you want, not a generic CRUD
  operation which has to work in all situations.
  
  
  But now I'm again holding this stupid silly discussion with some
  person who wants to suggest that if you don't use procs you're a stupid
  consultant who doesn't know jack sh*t about software engineering. Sorry
  Ian, but you're very very wrong in that.
  
  
  So end of story. Any comment which continues this stupid discussion
  here won't be published. This discussion should be over, people should
  read up and work together instead of pointing fingers to eachother that
  the other doesn't know anything about software engineering.
  
  STUPID,
  that's the word. I thought it would be funny to have a post about how
  silly the discussion still is. And what do you know? People start the
  discussion again, here in the comments! :-/
  
  

• Frans,
  
  While I agree with most of your opinions on this subject, I think most people (including you and me) engage on pointless religious arguments on SP/Dyn that HURT efforts to program more efficiently by
  - Showing only one side of the story (ok, that's what we humans do by default)
  - Trying to find the one-size-fits-all approach.
  
  You forgot a couple points AGAINS SPs:
  - They are a PITA to include in source control, as does every DB artifact
  - They introduce another layer of complexity/problems, specially for maintainance, which is not needed in most cases
  
  I agree that using SPs for CRUD is useless in MOST cases. But there are cases where you gain in performance and maintainability by using SPs:
  - Server-intensive tasks. While cursors have problems of their own, there's no point on doing 1 million roundtrips for processes that could be done in the server
  - Some complex data update cases are better kept in the server, where they can be "compiled" and tested in better ways
  
  I predict this argument will end in year 2139, when we are finally replaced with robots.