Tuesday, June 12, 2007

Use ORM For Better Performance

This is not something I would have though a few years ago. It is something I learnt after working on many different projects, some using an ORM layer like Hibernate, Entity EJBs, or JDO, some using JDBC approach via Spring Templates or custom frameworks. Many projects that use ORM have performance problems, that don't seem that common with projects using JDBC. But the size of the database model of ORM projects is often much bigger than the one of JDBC projects (which actually makes sense). If you have only a few queries to do, why bother with ORM? This would be complexity for nothing.

But for most enterprise projects, the size of the database model is quite big, and the model itself can be complex (many relations between many tables). With this kind of model, ORM is more efficient. It is faster to develop with, creates less bugs due to string misspelled, or types badly read. It is also better performing. Doing 1 giant query to retrieve everything in 1 step is not faster, especially if you don't always need all the information retrieved. In a complex model, many cases are specifics, only useful in 10% of the cases. The temptation is high with a JDBC approach to do one giant query, because it is substantially longer (and more work) to do N queries.  With ORM, it is a bit the opposite, by default N queries is easier to do. The problem is that N(ORM) tends to be very high if one is not careful with the mapping to avoid the N+1 problem. However it is simpler to reduce the number of queries by joining tables, rather than splitting queries, ORM performance optimization feels more natural.

Martin Fowler tends to be also pro ORM in its "Domain Logic and SQL" article. He also mentions something interesting about SQL query optimization:

It's also worth pointing out that this example is one that plays to a database's strengths. Many queries don't have the strong elements of selection and aggregation that this one does, and won't show such a performance change. In addition multi-user scenarios often cause surprising changes to the way queries behave, so real profiling has to be done under a realistic multi-user load. You may find that locking issues outweigh anything you can get by faster individual queries.


In the end it is up to us to make ORM or JDBC approach perform. JDBC provides much more direct access to database, and in benchmarks (always simple database models) or in theory it should be faster. But in the real world, I argue that ORM optimization is simpler and therefore, often ORM projects will perform better.

4 comments :

  1. A big plus with using ORMs in my view, is the nice loosely coupled evolution of your domain model and persistence model side by side. Using Hibernate annotations allow you to have your database model map nicely to the domain model. In case you are allergic to annotations being used in POJOs, you can go for XML anyway.

    ReplyDelete
  2. For what it's worth, we use Hibernate in a large enterprise project with a large model running against DB2/400. I think we've got a good design going, careful to avoid blowing our proverbial foot off with this powerful tool.

    Recently, we were doing some intensive database query investigation...and Hibernate queries (& related ops) were VERY fast. In fact, all of the problematic ones were hand-written.

    ReplyDelete
  3. rondeth, I had the same experience with an EJB 1.1(! +extensions) project. Tweaking generated queries was relatively easy (although not that good to deal with when using EJB bastards). Hand written queries were the most problematic ones for performance.

    I would definately not advocate EJB < 3 use (because it makes the dev cycle so much longer for so little benefit), but Hibernate, any day!

    ReplyDelete
  4. I agree with your article and many of the comments. However, an ORM is never perfect and should not be trusted to always produce the most efficient SQL statements.
    http://soastation.blogspot.com/2009/09/sanity-check-your-orm.html

    ReplyDelete