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.


  • Good writeup !

    and I think that perhaps the *REAL* issue is the bad database design problem!!!

    I am working on some stuff right now that I am 99% sure the performance problems come from the first db design having been done by a non-dba who was more used to working in a big firm where the sql was handed to him...

    two key tables have no relation and others that are related have none... and so indexes have to be built and extra code to prevent relational errors....

    I am trying to fix it but have to step with caustion as 4 programs access the sql db and have some odd code embeded in each app.

    one benifit to s-procs at times is this:

    if none of the apps had hard-coded sql then fixing the db would be a lot simpler.

    O/R maping and code gen are things I want to use more of but this is a system written in part in MS J++ part in Sun Java part in C# and now some is moving into Web Services.... so each change has to be tested for each apps embeded logic... a pain!

  • Thanks David. There's no doubt there are ways to use stored procedures in such ways -- but I don't think its typical, nor is it what most of the people debating "dynamic sql" vs. stored procs have in mind. Again, I'm not so much arguing against stored procs as I am for trying to better focus the discussions on the real issues.

  • To me, a true developer/application architect should have the ability to design a decent database from the ground up. I pride myself in being able to do this, but always make it a point to get the design reviewed by a *qualified* dba (let's face it, some aren't...).

    So to Denny's comment, if an application designer is designing a database with no keys and relationships, they probably shouldn't be building software (IMHO) anyway. You MUST be multifaceted to succeed in app dev these days.

  • Paul,

    I won't allow the interaction between my client application and database to be controlled by an O/R mapper. I have never found a piece of code that can look at my database and provide me some type of interface into that database that provides acceptable performance. I know how to setup a database with primary keys, foreign keys, and indexes. My code provides a high performance interface.

    Whenever I have seen an O/R mapper, it has always had some serious performance problems compared to what I can manually write. I have found that the client will pay for this type of performance.

    As for the argument about dynamic sql vs. sprocs, well i don't see enough programmers that know how to properly call sprocs, so I try and stay out of them because they need code that they can support and understand. They can understand dynamic sql. As for the suppossed security problems, well if you just take the input and try and put that into the database, well yeah, there are problems with that and you can get what you deserve. I have some parsing routines that resolve the sql injection security problems.

    For me, a properly designed database combined with proper sql statements provides the best performance and are secure.


  • That's totally fair Wally -- I like a good O/R mapper, but its definitely not for everyone. I do hope you do some code-gen though, which you can do with CodeSmith using your own templates and style.

    That said, while I don't have a problem with anyone doing there own thing (if done well), I do think that good O/R mappers do not suffer significant performance loss -- in some cases they even do better.

  • BTW, I am not trying to trash the Wilson O/R mapper. :-) I have much more confidence in you than in most developers (read 99.5%) and in your capabilities to create a feature rich app.

    I've been dealing with this project that has an O/R mapper and the O/R mapper is driving me crazy. Basically, it is an O/R mapper that they rolled on their own. Yuck. As you can guess, I have a bad taste in my mouth.

    I have tried to stay away from the code generators for two reasons.

    1. Performance reasons as mentioned above.

    2. Problems in making the finetuning type of changes that a code generator does not like me to do.


  • Nice post and a good discussion/description of true dynamic sql. Thanks.

  • I also won't argue either way yet (my jury is still out), But don't dismiss that "move the burden" argument as absurd - moving burdens from one group / person to another is critical to team software development.

    The fact that the responsibility stays in house but moves to a different group can be an asset, not a detriment (or absurdity).

  • Great post. It really got me thinking. I've always been an sp-guy but maybe it's time to at least try another approach.

    Starting a new version of my framework soon and I'll implement ORMapper into it.

    BTW, I'm using CodeSmith for code and SPs, so that's never been a real problem. ;)

  • A good friend of mine had a hard time understanding the concept of dynamic sql in O/R mapping. It took me the whole evening explaining and finally convinced him to read some articles regarding this. :-)

    This is a great post Paul. I think this will clarify some issues regarding "dynamic sql" which some people thinks its hard coded somewhere in the app.

  • Wally,

    I ask you to check out code gen again. You don't have to be stuck with your issues #1 and #2 with code gen. You can always modify what you generate afterwards if you need fine tuning. And especially with something like CodeSmith, the performance of the code-gen'ed code is only as bad as the template you write.

    Certainly it'd be nice if you can code-gen everything in the build process with no touch-ups but chances are you have to, even if you do something like create a derived class to "fine-tune".

    Just my 2 cents

  • In my apps I never user stored procs. I even avoid using views. For these reasons:

    Stored proce make me dependent on the type of database (Oracle, MySQL, SQL Server)

    Part of the source code is stored in stored procs and another part in C# or VB. How do you make agreements across a development team which code to put where?

    Stored proces can only be organized on their filename. How do you control a few hundred store procedures? How does every developer know what each procedure does?

  • Yes, Oracle stored procs are "harder" no doubt. :) They're aren't any tricks that I know of to make it "easier".

  • my feelings exactly ...

    for many members of the camp ... "Copy and paste is NOT code reuse"

  • Paul, one of the problems with the "dynamic sql" debates might be it is always discussed in abstract, at least everything I've read. Maybe it would help if you (and/or Frans) could blog a series of posts that give concrete examples showing a stored proc and how dynamic sql would replace it including the dynamic sql that would be generated and the .NET code that would be used to call it. I'm suggesting a series of blog posts showing many different scenarios. Maybe that would help people better understand the benefits of using dynamic sql? (or give them more ammo to attack the downsides? :)

  • Mike,

    From what I understood, the concept of OR mapping is not to ACTUALLY replace stored procedures. It simply tells the developers out there that "hey, you can also use DYNAMIC SQL when developing your CRUD and its also fast, easy to maintain, etc!" They explained the benefits when using it and of course there also trade-offs. But some people tend to misunderstand the concept of "dynamic sql" because they think creating these queries is by string concatenation. And this post, I believe, is to clarify the misunderstood concept.

    Now the question of dynamic sql as replacement for stored proc? For me its not. We build app depending on the needs of our clients and their "situations". So its always a matter of choice.

  • The concept of dynamic SQL existed way before the O/R mappers became popular. That's why most people relate to dynamic SQL as being queries created by string concatenation and hard coded in the application. And from that perspective it's quite understandable that people don't like dynamic SQL.

    Just because O/R mappers can do dynamic SQL (and in a very controlled way) doesn't mean that people will start think about dynamic SQL as SQL generated by O/R mappers.

    So you have:

    - Original dynamic SQL (that most people relate too)

    - Original stored procedures

    - Dynamic SQL generated by O/R mappers

    And depending where you are coming from you will relate to dynamic SQL differently and that's why the discussion of dynamic SQL vs stored procedures never ends since people are not discussing the same thing. The discussions are even more common now when O/R mappers are out there using "the dangerous" dynamic SQL.

    When it comes to dynamic SQL generated by O/R mappers it's quite different than the hard coding approach since the O/R mapper generates the dynamic SQL in a controlled manner. Based on certain rules it will generate the code in a certain way.

    So even if the end result is dynamic SQL I wouldn't say it's dynamic SQL as most people think of dynamic SQL. It's dynamic SQL generated by an O/R mapper. So you should call it something else than dynamic SQL when entering the classic stored procedure discussion. Call it O/R mapper generated SQL.

  • Magnus,

    What is so "DANGEROUS" about generating SQL at runtime? Care to explain why?

  • Chris Ongsuco: Once again, you did the same thing (discussed in abstract!) I'm thinking we need SOURCE CODE EXAMPLES!! :)

Comments have been disabled for this content.