Doctrine ORM Performance Traps

5 Doctrine ORM Performance Traps You Should Avoid

Benjamin Benjamin 30.06.2015

Doctrine is a powerful Object-Relational Mapper (ORM) for PHP. It increases developer productivity and allows more secure access to your database.

But using any ORM requires careful consideration with regards to performance, especially the critical paths in your application that you want to be very fast.

This blog post discusses performance traps that you should avoid falling into when using Doctrine ORM.

1. Not Caching Metadata and Query Parsing

This is the most important problem to avoid and is mentioned in the ORM documentation about performance: Use caching for Metadata and Query Parsing. It is also easy to fix and requires no changes to your application code, except configuration.

For every access to an entity metadata or a DQL query, a relatively expensive parsing process is triggered. Both are build to be fully cacheable and you should make use of this in production applications.

See the documentation on caching for details on setup for different caching backends.

2. Complex Fetch-Joins in Doctrine Query Language

One of the Doctrine's most powerful features is the Doctrine Query Language (DQL), but you should know about the cases where it can cause performance problems.

I have seen a tendency in many developers towards reducing the number of SQL queries at all costs. This often increases the cost of converting database rows to objects (called Hydration) considerably. There is a trade-off involved between reducing the number of queries by using "Fetch Joins" and hydration performance.

Consider this query for both companies and its employees:

SELECT c, e FROM Acme\Entity\Company c JOIN company.emplyoees e

The fetch-join part is defined through the alias e in the SELECT-clause, causing Doctrine to load both Company and every Employee entity together.

Using a fetch-join for a to-many association here, results in the company parts of the query being repeated over and over again while retrieving all the employees. This is the nature of the relational model and it doesn't fit very well with objects here.

Skipping parts of a row is expensive for Doctrine, because it has to detect the row and skip it, performing unnecessary transformations all the time.

You can avoid fetch joins by efficiently fetching the data yourself after the first query:

<?php
$dql = "SELECT c FROM Acme\Company c";
$query = $entityManager->createQuery($dql);
$companies = $query->getResult();

$employees = $entityManager
    ->getRepository('Acme\Employee')
    ->findBy(array('company' => $companies));

$employeesByCompany = [];
foreach ($employees as $employee) {
    $companyId = $employee->getCompany()->getId();
    $employeesByCompany[$companyId][] = $employee;
}

Some convenience of the ORM is lost here, but you get a more performant and scalable solution. The exact gains depend on every individual situation, it is easy to compare the difference looking at a timeline.

For the Company example it looks like a small decrease in total SQL and hydration can be achieved when fetching the data in code:

Doctrine2 DQL with Fetch Join PerformanceDoctrine2 DQL with Fetch Join Performance

Doctrine2 DQL with Eager Loading PerformanceDoctrine2 DQL with Eager Loading Performance

3. Inheritance and Associations

When using an entity with inheritance hierarchy in an associations, lazy loading proxies will not work. This can be very inconvenient when it triggers additional queries and entity creation that is not needed in a request.

This mistake slips in easily when using inheritance, because you don't see it in the mappings, for example in this association where you need to know Animal is an entity using single table inheritance:

class AnimalRegistration
{
    /**
     * @ORM\ManyToOne(targetEntity="Animal")
     */
    public $animal;
}

$registrations = $registrationRepository->findAll();

But you can see this when looking at the timeline of a request, how inside a the Doctrine findAll() operation we see one additional query for each registration entity.

Bad Performance with Inheritance and Associations in Doctrine2Bad Performance with Inheritance and Associations in Doctrine2

This is extremly inefficient and one reason why I generally avoid inheritance in Doctrine and discourage others to use it.

4. Bidirectional One-To-One Associations

Similar to the Inheritance and Associations problem, you should avoid bidirectional one-to-one associations in Doctrine. The inverse side of a one-to-one association cannot be lazy loaded.

In contrast to the case of Inheritance Doctrine can perform joins here automatically to fetch the entity. You should not rely on this behavior, because it is dependent on how you query the entity. In larger applications with many developers this performance trap is often a problem, because it is so hard to spot in code.

I avoid bi-directional one-to-one altogether to avoid this performance trap.

5. Lazy-Loading and N+1 Queries

Doctrine's lazy loading feature is an amazing time-saver for prototyping, but when growing an application it can be a huge performance killer.

The most common problem is passing entities to the view/template and then using lazy loading to access related information.

The following twig template causes the problem. It loops over a list of entities (employees) and prints the field of the association (company):

{% for employee in employees %}
<tr>
    <td>{{ employee.name }}</td>
    <td>{{ employee.company.name }}</td>
</tr>
{% endfor %}

In a Timeline profile this can be easily spotted, the purple/orange spans for SQL/Doctrine are located inside the green Twig span:

Doctrine Lazy Loading Performance BottleneckDoctrine Lazy Loading Performance Bottleneck

There are several ways to fix this:

  1. Use a DQL query to fetch both employees and their company. But be aware of trap #2
  2. Trigger eager loading of the proxies explicitly in the controller:

    $companies = array_map(function($employee) {
        return $employee->getCompany();
    }, $employees);
    
    $repository = $entityManager->getRepository('Acme\Company');
    $repository->findBy(['id' => $companies]);
    
  3. Set the assocations fetch-mode to "EAGER": @ORM\ManyToOne(fetch="EAGER"). This will achieve the same as bullet 2, but will always use eager loading. Use this strategy when you almost always need both entities in combination.

  4. Use a view model object and fetch only the fields that you really need in your view:

    SELECT new EmployeeListView(e.name, c.name)
    FROM Acme\Employee e JOIN e.company c
    

It depends on the use-case which one to pick, something you should experiment with to get a feeling for the trade-offs at play.