Tag Archives: database

ORM’s tradeoff

30 Sep

The other day a fellow colleague was in the midst of working on one of the more mundane tasks a developer can do, a tabular report, when a discussion arose around the way the representation of the report data. It occurred to us that we were dealing with was sets of data merged into lists; the precise domain of SQL. Ruby, our language of choice, has great native support for sets and lists. So, why even bother using ActiveRecord? Just like object oriented programming, ORM has proven easy to grok and extremely useful, but can start to become a crutch or security blanket. For example, how many times have you spent reverse engineering a SQL statement into your ORM’s representation of it? ORM is an incredibly useful abstraction albeit a leaky one.┬áThis is hardly a new thought, but it is one that should be brought up every once and while lest it becomes a cyclical debate the likes of centralized vs client/server computing. The question we eventually had to answer was: what cost are we willing to pay for this abstraction?

As much as one may not want to admit it, eventually you will be faced with the stark reality that your ORM is actually getting in the way and you will need to write the SQL yourself. This is a direct result of the leaky-ness of ORM. It may be inefficient queries, constant metadata queries, or just the creation of large object graphs that will turn and punish your blind trust in the framework. In order to plug this hole most every ORM uses the same last resort, the option to pass raw SQL to the DB. In ActiveRecord there is connection.execute(), Hibernate has session.createSQLQuery() and LINQ for C# has ExecuteQuery(). Now, you may think this is useful for those little esoteric queries like ‘SELECT 1 FROM DUAL’, but that’s easily done with an ORM. It’s when you start to select large datasets that most ORMs start to crumble and you need the precision of execute() to get the job done.

There are alternatives to diving down into raw SQL. You could retrieve simple object graphs and combine them in the application tier. You could even roll your own DAO and return sets and lists. You could embrace NoSQL and just marshall objects right out of the database. This, however, would ignore what SQL is great at, set operations.

So why are we, the Bird Herd, willing to pay the price of the ORM abstraction? First, we want all our logic in the same place. We are early stage and in constant flux, and when the business logic changes it’s a comfort to know that we have just one context in which to work.

Second, ORMs tend to be easy to use and we can work quickly. 95% of what we do does not involve crazy joins across alarge complicated schema, or the kind of query where selecting a column we may not use incurs meaningful performance penalties.

Finally, ORMs have dealt with the harder problems like locking, transactions and connection issues for a long time. These issues are pretty much a solved problem and we have better things to spend our time on. We are a rapidly growing firm seeking to innovate in our market; we are not out to reinvent the wheel, we want to replace it.