ORM tools. They’re great. They’re evil.

In the right hands, used the proper way, they can be a very powerful, very efficient, Rapid Application Development tool. It’s all about how the ORM tool is used – if it is abused or misunderstood, then very bad things will happen.

The biggest problems with ORM tools are that if you use a tightly coupled version of ORM, then the database schema will be dictated by the ORM tool (and the database will end up resembling an object model, not a relational database); and that the SQL generated by these tools is typically very inefficient. Problems include:

1. ORM tools are designed to work with a multitude of database platforms, and the code that they generate has to work on all of them. This leads to features in one database platform which may not be utilized in order to make generic SQL code. The end result is that the generated code doesn’t work on any of them particularly well. For example, SQL Server supports windowing functions and the CROSS APPLY operator (both very important for removing row-by-row operations); MySQL and PostgreSQL do not.
2. The lack of ability to perform additional tuning on the generated SQL code.
3. Implicit conversions causing indexes to not be properly utilized, if they are utilized at all.
4. Improper use of the IN clause.
5. Use of correlated sub-queries instead of JOINS on queries.
6. ORM tools are prone to creating parameter sizes of the length of the actual data being sent, not the size of the column storing the data. This results in each query getting its own execution plan, instead of reusing them.
7. nHibernate will attempt to update NULL columns in the database if the corresponding .net class is not set to use the nullable type also. Not only can this result in garbage data (NULL values being replaced with default values), but in the case of dates it can generate errors, since the default value for a .net date is out of range for the datetime data type in SQL Server.
8. I/O increase due to “chattier” applications. (More, smaller transactions; possibility of frequent requests to verify structure.)
9. I/O increase due to loading larger data sets more frequently.
10. Security: ORM tools require full access to the data. (Procedures only require execute rights on the procedures themselves.)
11. ORM tools do not consider the data when generating a query.
12. Improperly applied query hints that cause excessive locking.

The second point needs to be looked at again. Every call to SQL deserves to be deliberately designed to perform the best; when using ORM tools, you are restricted to what that particular tool generates that is capable of being run on any database platform. To fix a poorly performing query generated by an ORM tool, you must attempt to tweak it in the tool, and then an application release would be required to implement it.

With all of this in mind, I would recommend that if we are going to use an ORM tool, that all data access should be performed via stored procedures. (And if you’re only going to use stored procedures, then where is the benefit in using an ORM tool in the first place?) Anything other than simple queries are prone to being generated inadequately; conversely the simple CRUD operations that ORM tools could do properly are extremely simple to script out and to utilize as stored procedures. Problems with a query can be corrected by a simple SQL update script.

My final thoughts:

1. Over the course of years, there have been plenty of programs written to generate application code. These programs have never been successful. What is it that has convinced so many that SQL code can be generated that fully utilizes its engine any easier than generating application code?
2. ORM stands for Object Relational Mapping – a mapping of an object model to a relational model. Its intent is not to replace a properly designed relational database with an object database. In short – your web page design does not dictate the database design.
3. I do believe that in the future, there will be powerful tools to interact with the database that will shield the developers from the intricacies of the database, and yet please the database administrators with the performance. Unfortunately, that day is still a long way off.