ORM: A Solution that Creates Many Problems

One solution to overcoming the problems that exist between relationship and object-oriented systems is to turn to tools known as object-relational mappers (ORMs), which attempt to automatically map objects to databases.

Many of these tools exist, including the open source nHibernate project, and Microsoft’s own Entity Framework. Each of these tools comes with its own features and functions, but the basic idea is the same in most cases: the developer “plugs” the ORM tool into an existing object-oriented system and tells the tool which columns in the database map to each field of each class. The ORM tool interrogates the object system as well as the database to figure out how to write SQL to retrieve the data into object form and persist it back to the database if it changes. This is all done automatically and somewhat seamlessly.
Some tools go one step further, creating a database for the preexisting objects, if one does not already exist. These tools work based on the assumption that classes and tables can be mapped in one-to-one correspondence in most cases, which, as previously mentioned, is generally not true. Therefore these tools often end up producing incredibly flawed database designs.

One company I did some work for had used a popular Java-based ORM tool for its e-commerce application. The tool mapped “has-a” relationships from an object-centric rather than table-centric point of view, and as a result the database had a Products table with a foreign key to an Orders table. The Java developers working for the company were forced to insert fake orders into the system in order to allow the firm to sell new products.

While ORM does have some benefits, and the abstraction from any specific database can aid in creating portable code, I believe that the current set of available tools do not work well enough to make them viable for enterprise software development. Aside from the issues with the tools that create database tables based on classes, the two primary issues that concern me are both performance related:

• First of all, ORM tools tend to think in terms of objects rather than collections of related data (i.e., tables). Each class has its own data access methods produced by the ORM tool, and each time data is needed, these methods query the database on a granular level for just the rows necessary. This means that (depending on how connection pooling is handled) a lot of database connections are opened and closed on a regular basis, and the overall interface to retrieve the data is quite “chatty.” SQL DBMSs tend to be much more efficient at returning data in bulk than a row at a time; it’s generally better to query for a product and all of its related data at once than to ask for the product, and then request related data in a separate query.
• Second, query tuning may be difficult if ORM tools are relied upon too heavily. In SQL databases, there are often many logically equivalent ways of writing any given query, each of which may have distinct performance characteristics. The current crop of ORM tools does not intelligently monitor for and automatically fix possible issues with poorly written queries, and developers using these tools are often taken by surprise when the system fails to scale because of improperly written queries.

ORM tools have improved dramatically over the last couple of years, and will undoubtedly continue to do so as time goes on. However, even in the most recent version of the Microsoft Entity Framework (.NET 4.0 Beta 1), there are substantial deficiencies in the SQL code generated that lead to database queries that are ugly at best, and frequently suboptimal. I feel that any such automatically generated ORM code will never be able to compete performance-wise with manually crafted queries, and a better return on investment can be made by carefully designing object-database interfaces by hand.