Hot Dorkage

I have always had reservations about locking myself into a strict Object Relational Model scheme for the same reason I have been allergic to Windows. I was never able to articulate exactly why but my friend Juozas Kaziukėnas has brought it into sharp focus in his latest blog post on Doctrine.

Here’s the deal: I played with and studied Doctrine and I found it to be the most flexible and powerful of all the data mapping systems I have seen. Plus, Zend Framework is moving to it, so eventually I think it will be completely integrated with Zend Framework whether I like it or not. I had some concern due to alarming memory usage statistics reported when using Doctrine. But it seems that (as is often the case) the bad performance is the result of programmer ignorance, and not inefficiency of Doctrine itself.

Juozas addresses memory usage in his latest post. Of course you can alleviate some problems with smart cacheing and you can alleviate others (probably even more) by doing the appropriate queries in the first place. Here is his simple example that totally focuses the problem for me:

Ignorant Code:

$user = Doctrine_Core::getTable('User')->find(1);
foreach ($user->Comments as $comment)
{
   print $comment->NewsItem->title . '';
}

The code above is totally inappropriate use of lazy loading. It is getting the “main” record and then lazy loading each hasMany in a loop with a separate query. If you already know you’re going to be iterating over the comments, go to the bother of doing an explicit join and include them in the initial query so you get all the data you want in one go like this:

Good Code:

$query = Doctrine_Query::create();
$query->from('Comments C')
        ->innerJoin('C.NewsItem N')
        ->where('C.user_id = ?');
foreach ($query->execute(array(1)) as $comment)
{
   print $comment->NewsItem->title . '';
}

When I saw this I thought, “Of course, that’s exactly it!” Juozas knows what he’s doing — he is a Doctrine expert. Most coders aren’t. What ORM’s do is analogous to what code generation systems do: They generate queries (code) that gets the job done but unless you are aware of the underlying reality, it will do it in a way suboptimal way. I have already seen bad hand coded SQL springing from bad schema design. Now you can have a perfect schema and still have bad SQL that you never really even see. It’s just another way to divorce coders from the underlying reality of SQL, the same way DreamWeaver can strip the C out of CSS. Sure, it allows a lot more ignorant yobbos to do things without pissing all over their hands, but what is going to be the performance and Total Cost of Ownership (TCO) of that system once you get some load on it!!

Back in the pre-ORM days I hand generated each query and tried to make SQL do as much work as possible. I would envision the result set I wanted, then come up with a query (usually with multiple joins) to deliver it. The only time I ever ran into performance issues with queries was just once with a very complex query with subqueries where I did the joins in a suboptimal order. I was able to get a hundredfold increase in performance by a simple EXPLAIN that told me exactly how to fix it.

Ah well, I look at it as an opportunity. Don’t get me wrong, I think Doctrine is a great tool that will speed initial development. I’m much happier to use it after seeing this blog post. I’d be willing to hazard a guess that most of the reported performance issues are due to stupid queries such as this example, and Doctrine is powerful enough that they can be fixed. So after the kiddies have tromped through and completely pegged your application’s memory with first attempt queries, talk to ME (an old school DBA who actually knows how queries work) and I can probably squeeze an order or two of magnitude of performance out of it.