Bad ORM is infinitely worse than bad SQL

Author: 35 Comments

This is a bit of a rant, and I do apologize. It seems that lately I'm confronted more and more with ORM implementations that may look right on the surface, but that are a killer for your application. In the end, it's always a case of bad ORM usage, lack of knowledge from a developer, ... but I believe that just strengthens my point: it's very easy to write bad ORM, whether it's Doctrine, Propel, ...

Edit: this is getting a proper discussion at the Reddit-thread as well, be sure to check it out.

Hey, this looks like it might work

In an ORM world, it's very easy to write something like this (I say easy, I do not say right).

getAllCompanies();
    $totalValue = 0;
    foreach ($companies as $company) {
        // For each company there is, retrieve the total value of all their orders
        $orders = $company->getOrders();
        foreach ($orders as $order) {
            $totalValue += (float) $order->getValue();
        }

        echo "This company made us ". $totalValue ." euro already.";
    }
 ?>

If you've done PHP for more than 3 months, the above will give you shivers down your spine. It looks like perfectly valid code, it even makes sense to read it. But in reality, it probably does something like this.

value;
        }

        echo "This company made us ". $totalValue ." euro already.";
    }
 ?>

The above snippet of code will get progressively worse as your application grows. Why? Because as you have more entries in the "company" table, your first foreach-loop will grow since you iterate over each entry in the table. As a result of that, even more queries in the "order" table are being executed. So every time your customer base grows (more entries in that "company" table), the code above will get slower and you'll hurt your database with more and more (simple) queries as you iterate each table for each row.

Bad ORM is too easy to write

My problem with ORM is that it's too easy to write bad code. It's very easy to use all the default mappings and just do "SELECT *" in the background. Every ORM-system gives you the ability to write custom SQL queries, but that sort of defies the point of ORM in general, hence hardly anyone does it. As more and more developers use only ORM to create applications, they lose their touch with the database interaction, the queries behind it, the reasoning of why to use a certain kind of query, the performance impact of an INNER or OUTER joins, ...

The example above, causing many small queries, could also be replaced with one efficient query to give you the same result.

SELECT SUM(o.value) AS TotalValue, c.name AS CompanyName
FROM company AS c
LEFT JOIN "order" AS o ON o.companyid = c.companyid
GROUP BY o.companyid;

And it's not even a difficult query.

The lack of visibility

If you take a look back at the 2 code examples above, I think it's sort of obvious that the 2nd example -- where the SQL queries are shown -- shows you very quickly that this is bad code. It shows you the SQL queries that will be performed, and if you think logically for 1.5s you'll be aware that it is a bad thing to write. While the ORM example looks perfectly valid and the performance bottleneck may not immediately be clear.

And that just may be my biggest frustration with ORM: as a developer, you lose focus on the underlying SQL queries. Some may claim that it's not important, that it is exactly the reason why ORM is gaining so much attention. But if you're serious about tuning your application, you need knowledge of the SQL that is being performed. You need to know how to write efficient queries with complex JOIN's, GROUP BY's and aggregate functions such as SUM(), AVG(), ...

Oh you, just master ORM!

Yes. You don't write stupid code if you master your tools, and that includes ORM frameworks as well. I'm sure there are people out there that write perfectly performant ORM-related code, it seems I have just yet to find them.

If you disagree with me, prove me wrong.

Comments
  1. Posted by Michaël Rigart
  2. Posted by Frank Marien
  3. Posted by Nicolas
  4. Posted by Frisian
    • Posted by Matti
      • Posted by Dean De Block
      • Posted by Chris
      • Posted by Peter
  5. Posted by a
    • Posted by Matti
  6. Posted by Bernard
  7. Posted by CurtainDog
    • Posted by Chris
      • Posted by CurtainDog
    • Posted by thomas kyte
      • Posted by CurtainDog
  8. Posted by rob
  9. Posted by Weng Fu
  10. Posted by Anonymous
    • Posted by Matti
  11. Posted by Anonymous
    • Posted by Matti
  12. Posted by Noah Yetter
  13. Posted by Blaise Kal
  14. Posted by David Rudder
    • Posted by Nicolas
      • Posted by David Rudder
  15. Posted by Justin
    • Posted by Justin
  16. Posted by wesen
  17. Posted by Carsten
    • Posted by David Rudder
  18. Posted by Johannes
    • Posted by Mattias Geniar
  19. Posted by Rodger

Add Your Comment