Debate: O/R Mapping or Code Generation
Posted by: Paul Wilson on September 28, 2004 @ 03:45 PM
Should you use O/R (Object-Relational) Mapping or Code Generation to handle your Sql and data access in your next project? I'm going to assume that you at least want to use one of these techniques -- if not then see my blog (http://weblogs.asp.net/PWilson). I'm going to also assume that we do not have to go over the performance arguments yet another time -- both stored procedures and parameterized dynamic sql are compiled, cached, and reused. So back to O/R Mapping vs. Code Generation -- what are the relative pros and cons, and when should you choose one or the other?
First, what is the main difference in these techniques, keeping in mind that some O/R Mappers do use some Code Gen too? Both O/R Mappers and Code Gen create most of your Sql and DAL for you, so they both reduce the code you need to create. The difference is that O/R Mappers create the Sql at run-time, and use their own internal DAL, while Code Gen produces Sql, along with your DAL, at compile-time. Also, note that although both techniques can use stored procedures or dynamic Sql, typically O/R Mappers prefer parameterized dynamic Sql while Code Gen prefers stored procs.
So, it mostly comes down to: what are the relative advantages and disadvantages between your Sql and DAL being generated at run-time vs. compile-time?
1. Sql that is known at compile-time (Code Gen) can be more optimized if necessary, while Sql that is generated at run-time (O/R Mappers) can be more flexible to the situation at hand.
2. A DAL that is generated at compile-time (Code Gen) can be customized to your heart's desire, while the internal DALs of most O/R Mappers allow you to easily target multiple databases.
3. Stored procedures do provide a little more security, and thus it should be noted that O/R Mappers typically use parameterized dynamic Sql, while Code Gen typically prefers stored procs.
I think these three points are mostly indisputable, although correct me if I'm wrong, and they are very much at the heart of the decision you must make between O/R Mapping and Code Gen. So the answer should be "simple" -- use an O/R Mapper if you need flexibility in your filter/sorts and databases, while use Code Gen if you need complete control over customization and optimization and security!
Unfortunately, its often not that "simple", so lets look at why that may be. First, everyone thinks that need the utmost customization, optimization, and security, but is that really true? Most applications make heavy use of grids and/or trees -- which often cause us to accept very undesirable UIs, or some limitations at least, not to mention performance that is often terrible -- but do we rebel and create our own? The answer is almost always NO -- these tools are "good enough" to let us focus on our real project, instead of having to write our own grids and trees. I think the same can be said for most of your data access needs -- it takes a lot of effort to Code Gen really solid code that is actually flexible enough and optimized enough to warrant the huge amount of effort -- whereas O/R Mappers are "complete" tools that are "good enough" in most cases.
Also, for all the debates that have been made about performance and security, I again think that most of the arguments forget the "real" issues, and only tend to focus on issues that are actually quite minor. For instance, most performance optimizations will always be made with indices, file-groups, or other low-level database tuning, not tinkering with raw Sql, and most performance issues are usually due to bad designs elsewhere. As for security, using an application role with only read and write permissions is typically more than enough for security, as long as you protect against attacks like Sql inject, which O/R Mappers do if used correctly. Anyone doubting this should seriously look under the covers at successful enterprise applications, like SAP, and the upcoming MS Business Framework, which require flexibility as well as performance and security.
Note that I'm not trying to say the answer IS O/R Mapping, for I truly do believe its a choice between flexibility and control -- I'm simply trying to point out that most people think they need more control than they really do. You can use most O/R Mappers out of the box, just like grids and trees, while you will have to spend some extra effort into Code Gen -- and the more control you demand the more of an investment Code Gen will require of you. Does your business really justify that much of a time commitment into Code Gen, not to mention the extra testing and training that will be typically be required for any custom solution when you compare it to a packaged solution, like most O/R Mappers. And will your team have any junior programmers that will change your generated code -- only to find out that it gets wiped out the next time you have to do a Code Gen?
It should be obvious that I think most projects would be better off with the flexibility of O/R Mappers, instead of the greater control that Code Gen can offer, but I truly do believe there are some exceptions -- I just think they are rare. For instance, I have actually worked on a project that required me to write my own grid -- so I can appreciate that there are some projects that just won't work with any out-of-the box tool, but I also know from that same experience that the increased testing, training, and maintenance was a continual issue! That particular project demanded something custom, but I don't want to do it again if I can help it, and I also believe that some minimal changes in our business requirements could have allowed us to build a more stable product much quicker if we had not had these demands, regardless of how "right" they might have been.