Bad ORM is infinitely worse than bad SQL

Want to help support this blog? Try out Oh Dear, the best all-in-one monitoring tool for your entire website, co-founded by me (the guy that wrote this blogpost). Start with a 10-day trial, no strings attached.

We offer uptime monitoring, SSL checks, broken links checking, performance & cronjob monitoring, branded status pages & so much more. Try us out today!

Profile image of Mattias Geniar

Mattias Geniar, March 30, 2012

Follow me on Twitter as @mattiasgeniar

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.



Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.