Yet Another Post on Dynamic Sql vs. Stored Procs
I continue to be amazed at the typical discussion of "dynamic sql" vs. stored procs. The arguments almost always assume that "dynamic sql" equates to hard-coded sql in your application. This is simply not the case for anyone I know that has ever argued for "dynamic sql". So first, let's all agree that hard-coded sql in your application is a bad practice. Now we can move on and really talk about "dynamic sql" vs. stored procs. Before defining "dynamic sql", lets define stored procs. That may sound silly and unnecessary -- but seriously -- what are stored procs? Stored procs are blocks of hard-coded sql in your database! That's right -- they are hard-coded sql -- just moved to a different physical tier. Like any other piece of code, these blocks of sql must be written (or generated) and maintained. The frequently recented argument that stored procs free developers is absurd since it just moves the burden somewhere else! Its also stated that stored procs allow you to change things without a recompile -- but that's true to a small extent only. Any serious changes in your data-model are also still going to require changes in your app to know how to work with those changes. In other words, stored procs are an API, and if you change the API then you're going to have to change your application too! That said, its certainly true that small optimizations can be made in your stored procs without a recompile, but how often does this really occur? Most optimizations involve things like proper index tuning, the setup of separate file groups, and other database "tricks" that are not related to sql. In fact, if the sql in your stored procs is so poor that it can benefit from optimization then you really need to question if you should be writing any sql in the first place!
Which brings me nicely back to "dynamic" sql -- so lets talk about what "dynamic sql" really is. "Dynamic sql" is sql that is generated at run-time, typically by an O/R mapper, and it is also "parameterized". Note that it is not hard-coded sql, not in your application or in your database -- so there is no sql to write or maintain anywhere! You aren't just moving the burden from your developers to your DBAs -- you are removing this burden entirely. Next, note the run-time piece -- this is critical for some serious optimizations that you simply cannot get with stored procs. That's right, do your stored procs allow you to retrieve data for any criteria and/or sort on any set of fields? Do your stored procs allow you to only retrieve a specific "page" of data, as opposed to all of the data? Do your stored procs allow you update only the fields that have changed? Do your stored procs make it easy to use optimistic concurrency? Do your stored procs allow you to sell your product with support for any database? These are real sql-level optimizations -- and they are made by good O/R mappers, but typically lacking from stored procs! Everyone loves to focus on "performance", but that is typically done with lower-level things like index tuning and file groups, not by tinkering with sql. And in that sense there is absolutely no reason why "dynamic sql" cannot perform just as well, if not better in some cases, than stored procs. Yes, the length of the sql statements are larger for "dynamic sql" than they are with stored procs, but that realistically has little to do with performance when you look at the bigger issues. And yes, stored procs make it "easier" to secure your database, but its actually quite easy to use application roles with "dynamic sql". And one last thing to note is the "parameterized" part of most "dynamic sql" -- this means that you are just as safe from sql injection attacks as you are with stored procs.
Now this does not mean that I believe stored procs are evil and that everyone should use "dynamic sql" -- so please don't assume that. What I am saying is lets please make sure we are all talking about the "right" things. No one has been saying to use hard-coded sql in your applications -- unless you follow all the examples in MSDN, books, and articles that is. :) If you want to save development time and maintenance, then you should either be using an O/R mapper or some serious code-gen (like CodeSmith) -- end of story. And if you really want to optimize your database, then you should get your DBA involved in the design of the database and stop giving him silly stored procs you wrote. Is there really any value to have developers create poor database designs and then expect your DBAs to fix it all by "optimizing" your stored procs? The problems usually are far more serious, and too late, if the database was designed poorly in the first place -- and the best optimizations are usually done elsewhere if its a well-designed database. Again, that doesn't mean that stored procs are bad -- but please don't assume that the job of your highly paid DBA is merely to clean up your crappy design and stored procs. Whether you use an O/R mapper with "dynamic sql" or code-gen with stored procs -- both of these practices attempt to free your entire team from the routine cut-n-paste so that they can focus on real design! And maybe that's the real issue -- for sadly, most developers (and maybe many DBAs), especially in the Microsoft camp, are simply not able to do anything beyond simple cut-n-paste! And if that's the case (which I fear it is), then O/R mappers and/or code-gen is simply going to leave them with blank stares -- since they will no longer have a clue what to do.