Mastering Symfony2 Performance – Doctrine

database

Doctrine ORM is a powerful tool which can facilitate an access to the database layer providing mapping the source data to objects. Unfortunately, as every next abstraction layer, such possibilities come with a performance overhead.

Based on some common use-cases, the article will cover topics related with improving the performance when using Doctrine ORM. We’ve choosen it as it comes in a Symfony Standard distribution and is integrated out-of-box within your project — it’s the most commonly ORM used in Symfony2.

0. Always control your Symfony2 Profiler toolbar

The first, key rule of developing efficient Symfony2 project is to always control what is going on in the profiler’s toolbar.

Thanks to it, you can always spot weak points of your application – rise of the number of queries can show you that something may be inefficient, some queries may leak.

In the Symfony Profiler you can also get into details of the database queries:

  • Click on the blue (+) to get a nicely formatted query
  • Time – shows the time of the statement execution
  • Explain query – shows an EXPLAIN of the query

Btw. click on the profiler below to get the queries explained 🙂

Symfony2 Profiler

1. Avoid Object Hydration when handling many entities

The process of Hydration is one of the most time and memory consuming in the ORM.

When you’re retrieving many rows from the database only to show them in the view (e.g. in a some sort of listing/CRUD), hydrating them to the object may not make sense.

Instead you should use simpler hydration modes like arrays/scalar:

<?php

$qb = new \Doctrine\ORM\QueryBuilder;

$arrayResults  = $qb->getQuery()->getArrayResult();
$scalarResults = $qb->getQuery()->getScalarResult();

Doctrine Docs – Hydration

2. Don’t load the whole entity if you only need reference to it

Sometimes you’re in the situation when having an Entity and an ID of the other one which you will like to associate. In that case making an extra find($id) operation — SELECT — can lead to an unnecessary additional database statement.

Thanks to the Doctrine’s Reference Proxies you don’t have to retrieve the whole entity from the database only to associate it with another one.

You can use its ID instead:

<?php

$em = ...; // the \Doctrine\ORM\EntityManager instance
$friendId = ...; // ID of other user entity, obtained from anywhere - e.g. request

$user = new User;
$user->addFriend($em->getReference('Octivi\Entity\User', $friendId));

$em->persist($user);
$em->flush();

Doctrine Docs – Reference Proxies

3. Update multiple database rows using Update statement

When you have to update multiple entities, retrieving them all from the database and iterating over ORM entities is known as a bad practice.

You should never do like:

<?php

$friend = $em->getReference('Octivi\Entity\User', $friendId);
$users = $this->findAll();

foreach ($users as $user) {
    $user->setFriend($friend);
    $em->persist($user);
}

$em->flush();

Instead, you should rely on the UPDATE query:

<?php 
$qb->update('Octivi:User', 'u')
    ->set('u.friend', $friendId)
    ->getQuery()->execute();

Thanks to it, we only execute one SQL UPDATE statement instead of N-updates for each User entity.

4. Use the advantages of Lazy Collections

From the 2.1 version, Doctrine added support for lazy collections.

With extra lazy collections you can now not only add entities to large collections but also paginate them easily using a combination of count and slice.

Having ManyToMany or OneToMany mappings defined with fetch type of EXTRA_LAZY:

/**
 * @Entity
 */
class CmsGroup
{
    /**
     * @ManyToMany(targetEntity="CmsUser", mappedBy="groups", fetch="EXTRA_LAZY")
     */
    public $users;
}

makes, that calling Collection’s methods like:

  • $users->count();
  • $users->slice(…)

won’t load the whole collection from the database to the memory.

Instead, Doctrine will cleverly execute appropriate queries such as COUNT.

Doctrine – Extra Lazy Associations

5. Beware of loading entities in a loop

By default, Doctrine won’t make JOIN for associated entities. The common situation is when you have entities with N-1 association (e.g. one article can have only one author) and you want to display a list of articles with their author’s names.

Take a look at the sample code:

In some controller you are finding all/some articles:

<?php

$em = $this->getEntityManager();

$articlesRepo = $em->getRepository('OctiviTestBundle:Article');
$articles = $articlesRepo->findAll();

In the view you want to display the name of the article’s author:

{% for article in articles %}
    {{ article.author.name }}
    {{ article.content }}
{% endfor %}

Such code will lead to additional SELECT statements for every article’s author.

Instead, you should use JOIN in your DQL/Query Builder:

<?php

$qb = $em->createQueryBuilder('qb1');
$qb->add('select', 'a, au')
        ->add('from', 'OctiviTestBundle:Article a')
        ->join('a.author', 'au');

which will get articles with their authors in one SELECT query. So next calls for article.getAuthor() won’t make any extra database queries.

Photo by tec_estromberg


Are you looking for an experienced Symfony2 developers?
Check out our offer!

Looking to scale-out your
web application?

Hire Octivi!

Antoni is a Software Architect and Scrum Master at Octivi. He is responsible for software architecture of our key projects, he also holds Professional Scrum Master certificate.

  • PM

    great post

  • shadypierre

    Really interesting, thank you.

  • Sarel

    We also found that to conserve memory, we switched SQLLogging off, like so:
    $entityManager->getConnection()->getConfiguration()->setSQLLogger(null);

    • Alberto

      Doctrine logging and profiling is disabled in production environment by default, isn’t it?

  • I am trying to figure out for a long time where i do mistakes with ORM (memoy usage)…I found points 1 and 2 interesting but it’s still do not solve my mystery of too much ram consumed. If someone would be interested in helping me move to another lvl by mentoring i would be reaaallly happy 🙂 BTW: keep posting these articles. Thank you! 🙂

  • Good article, thank you 🙂

  • Michaël Perrin

    Great article! Thank you.
    I didn’t know about point 2, and was feeling frustrated to have to load an extra entity just to bind it to an other one.
    Concerning point 0, beware of your invalid entity as well! (probably due to a wrong oneToMany / manyToOne declaration)

  • Christophe Coevoet

    The advice about using getReference to get a proxy needs to be applied very carefully: as getReference() does not perform a DB query, it will allow getting a reference to a non-existent user, which will then trigger an exception when doing an action requiring to initialize the proxy (and breaking the foreign key constraints when using it in a relation). If your friendId comes from a request, you will generally want to use find() rather than getReference because you cannot trust the request to provide a valid id

    • Great comment. Indeed, you should never rely on the external data sources like Requests or sometimes, even 3rd-party webservices.

      Using getReference must be done only if you are sure of the origin of the data.

      But sometimes, getReference is handy to for ex. manage larger collections like bulk actions based on the IDs of resources. That way you won’t have to make SELECT (find()) statements for every resource.

      Even further, bulk actions can be easily done using our tip from “3. Update multiple database rows using Update statement”. Just make UPDATE statement with IN() containing IDs of the resources 🙂

    • hmmm very nice comment.

  • DEY

    nice tips, thank you.

    Well what about optimizing N:N relation queries ? I mean, sometimes it could be better to make 2 requests that return small amount of lines instead of one returning a big Cartesian product.

    My first thought is to use hashes (+ managers). We loose the OOP benefits but at this level of optimization we should not hydrate anything.

  • Leodi42

    Awesome, thank you

  • saman shafigh

    Nice Article, thanks

  • saman shafigh

    Nice article, thanks

  • Avigo Development

    Thank you very much!