Where do you "Load Balance" an ORM in a PHP MVC Application

Question

The Problem: Object models built using an ORM often need to perform multiple queries to perform a single action. For example a "get" action may pull information from multiple tables, particularly when you have a nested object structure. On complicated requests these queries can add up and your database will start blocking long before it would if you were manually writing SQL.

The Question: Where do you load balance the ORM to cut down on the number of queries that need to be made, and more importantly why did you choose this approach? Do you have separate models to load data dependent on context, or do you specify which data should load in the controller? Or something else?

Answer 1

ORM is really there for a good reason -- to speed up your development.

If performance becomes an issue for me, I'd rather implement some caching mechanisms instead of taking a step back and hard-coding SQL.

Answer 2

I recommend using the Domain Model pattern, to provide an interface to data data in an OO-friendly way. As part of the implementation of persistence within your Domain Model classes, it's appropriate to use a mix of ORM and SQL.

For instance, you'll have some simple queries against a single table. Use a convenient ActiveRecord pattern for this. But as you describe, you'll also typically need some complex queries against multiple tables for more complex related data. ActiveRecord is a clumsy solution in this case, so use plain SQL. It's the best tool when you need a complex query with relational operators like JOIN or GROUP BY.

@pestaa mentions caching which is another good tool. Here's another one you can consider: Identity Map. The point is that you should learn multiple tools, and think about which one is the best in any given situation.

Trying to use only one pattern for every situation is like driving your car everywhere in first gear.

Answer 3

A lot of it depends on the ORM, its philosophy and features. But assuming you've got a good set of model classes between your ORM and the rest of your application, you can do the following:

1. Provide methods in your models that provide the right amount of depth for most cases. If your ORM doesn't allow you to specify things efficiently, consider a different ORM (if you have that luxury)
2. Plan and implement caching. Since we're talking in a data-centric context, this means writing/leveraging data caching in your model.
3. Have a plan to split reads from writes. Either in your model or ORM configuration. Especially if reads are your bottleneck, using some replication to create a gang of read-only slaves can be immensely useful. However, if you don't plan for it, you can easily design yourself into a position where it's a pain.