5 Simple tips for boost the database handling with Symfony2 and Doctrine

Nowadays the Doctrine Object Relational Mapping (ORM) is most widely used among the Symfony developers because makes the things between the developer and the database really easy although exist people that doesn't agree with this point.

You should already know that Doctrine 2 can't be compared with its old version Doctrine 1 in relation of performance , for example :

Doctrine1 implements ActiveRecord design, while D2 implements DataMapper design - that's the most important difference.

D2 requires PHP 5.3 or newer and uses its benefits like namespaces.

D2 is divided into set of smaller subprojects: Doctrine Commons, Doctrine DBAL, Doctrine ORM (for RDBMS) and Doctrine ODM (for MongoDB).

D2 is much faster.

D2 supports annotations.

Doctrine will definitely make life easier for the php programmer.

However you as a developer are encouraged to offer good quality results, the performance of an application gives a lot to talk about it and the developer, learn some tips to optimize your performance with doctrine in your symfony2 project.

1. Avoid Object Hydration when handling many entities

When you’re retrieving many registers from the database only to show them in the view (for example the default CRUD generated files) , you should think about don't use the default object hydration of Doctrine (returns a Doctrine_collection object what is translated in more memory and time consuming ). Increase the performance hydrating with the following methods.

Instead of retrieve Doctrine_collection objects like :

// In the controller
$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('ourcodeworldBundle:Users');
$query = $repo >createQueryBuilder('a')
                ->where('a.role LIKE :role')
                ->setParameter('role', '%ADMIN%');
                
$results = $query->getQuery()->getResult();// Default hydration

Apply instead :

// In the controller
$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('ourcodeworldBundle:Users');
$query = $repo >createQueryBuilder('a')
                ->where('a.role LIKE :role')
                ->setParameter('role', '%ADMIN%');

$results = $query->getQuery()->getArrayResult();// Array hydration

$results = $query->getQuery()->getScalarResult();// Scalar hydration

2. Don’t load the entire entity if you can reference it on persist

Lets imagine an entity which have a field that is referenced from another database table (an User object have a field Role that have a Foreign key from Role table) and to persist the user object you need that setRole($roleObject) be an Role Entity.

In that case making an extra find($id which comes for a request parameter for example) operation forces to an unnecessary additional database statement (which it is what we all do to solve this problem) like this :

Using getReference must be done only if you are sure of the origin of the data (when your environment cache is not cleared often).

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

// In the controller

$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('ourcodeworldBundle:Users');
$roleId = 2;
$role = $repo->find($roleId);

$user = new User();
$user->setName('Invisible man');
$user->setAge(27);
$user->setRole($role);
$em->persist($user);
$em->flush();

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 like this :

// In the controller

$em = $this->getDoctrine()->getManager(); 
$roleId = 2; 

$user = new User(); 
$user->setName('Invisible man');
$user->setAge(27); 
$user->setRole($em->getReference('ourcodeworldBundle:Users', $roleId)); 
$em->persist($user); 
$em->flush();

3. Do not get referenced values from entities in a loop

We go back to the View that shows us the users, then we will going to render its name, name of the role and age with a twig loop like this :

{%for user in collectionUsers %}
<tr>
    <td>{{user.name}}</td>
    <td>{{user.role.name}}</td>{# Keep in mind that for every user , A QUERY will be executed asking for the name of its role !#}
    <td>{{user.age}}</td>
</tr>
{%endfor%}

So when you render 100 users, you way want to send collectionUsers as the result of a Join Query and in Array Result, that will increase considerably the performance in comparation to the previous test due to only 1 query will be executed finally.

$qb = $this->createQueryBuilder('p');
      $qb->addSelect('a')
        ->innerJoin('p.role', 'a');

return $qb->getQuery()->getArrayResult();

4. Multiple rows update using 'update' statement instead object persisting

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

$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('ourcodeworldBundle:Posts');

$newCreatedAt = new \DateTime();
$posts = $repo->findAll();
 
foreach ($posts as $post) {
    $post->setCreatedAt($newCreatedAt);
    $em->persist($post);
}

$em->flush();

Is recommended to apply instead :

$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('ourcodeworldBundle:Posts');
$newCreatedAt = new \DateTime();

$qb = $repo->createQueryBuilder('p');
    $qb->update()
        ->set('p.createdAt', ':newCreatedAt')
        ->setParameter('newCreatedAt', $newCreatedAt); 
$qb->getQuery()->execute();

5. If not necessary do not load the whole object (with relations) just to get a simple value

Unless you can deal with scalar and arrays values, this is a good tip to prevent the load of unnecessary information that only occupy space in memory.

$em = $this->getDoctrine()->getManager();
$repo = $em->getRepository('ourcodeworldBundle:Users');
$age = $repo->createQuery(
    'SELECT z.age'.
    'FROM ourcodeworldBundle:Users z'.
    'WHERE z.id = :id'
)
->setParameter('id',2)
->getSingleScalarResult();

return $age;//Just the number we need !

Some important recomendations :

Make sure you properly create your queries.
Load only what you really need from database and return you are in need of hydration or objects.

In the doctrine website , allude a important point :

It is highly recommended to make use of a bytecode cache like APC. A bytecode cache removes the need for parsing PHP code on every request and can greatly improve performance.

“If you care about performance and don’t use a bytecode cache then you don’t really care about performance. Please get one and start using it.”

Stas Malyshev, Core Contributor to PHP and Zend Employee

Finally, be carefully applyng this article to your projects.Is not necessary to attend all these recommendations if your project doesn't handle very high traffic, heavy querys or have low specs.