Object Relational Mappers Series - The Most Egregious Issues

This is the next post on my series on ORMs.  I'm going to start with my two biggest problems with ORMs, dynamic SQL and debugging.  Most data architects who hate ORMs will cite performance as their Number One ORM concern.  I disagree.  Debugging is the bigger problem.  Let's see why.  

Dynamic SQL vs Stored Procedures

This is my biggest complaint with using an ORM.  For the most part ORMs always generate dynamic SQL.  They generally have a query cache that they will use to lookup common queries they execute.  This is different from their data caches...discussed later.  Why dynamic SQL?  Simple, the ORM mapper will read the database schema and create business objects basically for each table, then try to associate them together using the DRI in the database or manual relationships a developer creates with an their ORM's GUI. 

So what's wrong with dynamic SQL?  Seriously, I don't see a problem with it...to a point.  For simple queries where the ORM is populating a drop down box this is a fine solution.  For complex cases where I need to run a few queries and pare down the results to meet a requirement, then dynamic sql becomes problematic.  Why?  Simple, the "tool" is creating the dynamic sql and the developer has very little control over the generated SQL.  What happens when it doesn't perform?  What happens when it doesn't perform only after I deliver it to my largest customer?  Do I want to deliver a new, compiled version of my app because I need to code "WITH (NOLOCK)" in a single query...or can I quickly fix the issue by compiling a new stored procedure? 

In my mind this is the biggest reason to really limit your use of an ORM. 

ORM proponents will tell you that you can use stored procedures in only two cases...as the base data source object for a business class, which is never recommended...and as an alternative to when dynamic SQL just won't perform.  In fact, most ORM advocates will say that dynamic SQL will almost always perform better than stored procedure code.  I've actually seen this documented!!!  That's really going out on a limb!

If you think about it, a stored procedure is kinda like dynamic sql anyway...right?  ORM vendors quote websites that refute the common DBA myth that stored procs perform better than dynamic SQL.  I would agree with that...there is nothing inherent in a stored procedure that isn't in dynamic sql for most of the RDBMS vendors.  In some, such as Oracle, a good case can be made that procedures that return result sets are hugely cumbersome to write and maintain.  Given a query running as dynamic sql vs one that is encapsulated in a stored procedure and parameterized...both will run about the same regardless of RDBMS.  Some DBAs will make the case that network bandwidth is better for a stored proc and the ORM vendors will refute that too.  I agree with the ORM vendors...network bandwidth is rarely a cause for concern. A few milliseconds might be gained, but I really wouldn't worry about it.  

But the ORM vendors are missing the point.  The issue isn't dynamic sql performance vs stored proc performance.  The issues are flexibility and maintainability.  If the tool generates the dynamic sql and I'm suffering performance problems because my join is suboptimal...how do I solve it?  At a minimum I am looking at redeploying the bits for the customer...that takes time.  With a procedure I just edit it right on the server and I'm done.  Granted, this violates good change management practices...but I'm not sure a customer cares when their app is now unusable after an upgrade because a critical component doesn't perform.  And this is what the ORM vendors proclaim...more ultimate benefit for the customer. 

Let me repeat the point one more time...my biggest complaint is that the dynamic SQL is being generated in the tool and is difficult to change.  When a bug or performance problem is found often the change requires redeploying a new binary.  With a stored procedure I just make my change and recompile. 

Why would you want to express your database schema in another tier in your app...and then have to maintain it? 

When the ORM vendor tells you the benefits of dynamic sql...don't refute them...they are correct...but they are correct for the wrong argument.  The issue, again, is really more like...if the tool generates dynamic sql that doesn't perform...how do I fix it?  Is dynamic sql really "dynamic" if I am constrained by the ORM vendor?  Is that constraint always a good thing?  Make sure the issue is framed correctly. 

To summarize, ORM tools and their dynamic sql are great for the simple stuff.  If you find you are writing complex ORM queries (a future post will have examples of this)...or you can foresee performance problems...or you need to introduce intermediate data processing (temp tables, table variables, cursors, whatever)...or you suffer from parameter sniffing ...or you find yourself using views/indexed views heavily to encapsulate business logic, and then are constrained because you can't implement complex branching logic in a view (but you can in a stored procedure)...or you need to denormalize and "carry keys" to child tables for performance and the ORM tool isn't doing the joins correctly...or you require multiphase transactions...or complex business rules...then perhaps you should evaluate in these cases what exactly the ORM tool is buying you.  The ORM vendors want you to focus on good business logic and less on database code...that is a noble purpose...but if you have problems with complexities the ORM isn't handling and you are constantly working around them...I'm not sure the ORM tool has met its goal. 

Debugging

The ORM tool is building dynamic SQL for you under the covers.  How easy will it be to debug every possible "version" of SQL code the ORM could create?  We've all seen cases where our SQL passed all unit tests and QA and then failed at a customer because they had null values where we didn't anticipate and 3 valued null logic is difficult to understand and test.  How will the ORM handle this?  Can you be sure you've tested every possible piece of SQL the ORM could create for the business entity? 

More to come...