Increasing Project Productivity In Symfony2 With Doctrine2 ORM

I have been trying to write this Doctrine 2 ORM tutorial for a long time but just couldn’t get down to it. Finally, I pulled myself together and did it. So here I’m sharing some techniques for working with Doctrine2 ORM which will help to improve the site performance on Symfony2(precisely any site that uses Doctrine2 ORM). I have created a project and put it on a GitHub as a visual guide so anyone can test my words in action now.

1. Downloading All Necessary Connections

I think this method is the most efficient. That’s why it gets the first place.

Let’s say we have 5 authors and their 100 posts and we need to display all the posts with the names of the authors on this page.

Template:

    {% for post in posts %}
    <article><h2>{{ post.title }}</h2><p>Author: {{ post.author.firstName }} {{ post.author.lastName }} created at {{ post.createdAt | date('d-m-Y H:i') }}</p><p>{{ post.text }}</p></article>
    {% endfor %}

What could be simpler you say and do something like this:

$posts=$this->getDoctrine()->getRepository('AcmeDemoBundle:Post')->findAll();

But if you look at the profiler (Symfony Profiler Toolbar), you will see this upsetting information:

Where did so many queries to the database come from if we made only one request?

The thing is that Doctrine doesn’t load default entity links and when we turn to them, a new request for this data is issued. That’s why we have 5 more requests for the authors of the posts.

In order to optimize the Doctrine 2 ORM query we need to use JOIN and add the required entities to the sample. Here is the optimized repository method code for obtaining a list of posts.

/**
* Find all posts with authors
*
* @return array | Post[]
*/publicfunction findAllPostsAndAuthors(){$qb=$this->createQueryBuilder('p');$qb->addSelect('a')->innerJoin('p.author','a');
 
    return$qb->getQuery()->getResult();}

By using this method to get a list of posts, we’ll get the following picture in the profiler:

Now we are left with only one query. The execution time of it is decreased comparing to the previous version.

2. Updating Multiple Entities By Request

Suppose we now need to update the creation date of all our posts. Often it is done by getting all the posts from the database (due to Doctrine 2 documentation) and then updating each Doctrine 2 entity separately in a cycle:

$newCreatedAt=new \DateTime();$posts=$this->getDoctrine()->getRepository('AcmeDemoBundle:Post')->findAll();
 
/** @var Post $post */foreach($postsas$post){$post->setCreatedAt($newCreatedAt);}$this->getDoctrine()->getManager()->flush();

As a result, we have the following values in the profiler after updating 100 posts:

As we can see, as many as 103 queries were directed to the database. Even the profiler highlighted them in yellow. This should already alert you and make you think about how to increase Symfony2 website productivity.

Why so many requests? Since we were updating entities in the cycle, we received a large number of UPDATE queries to the database.

For optimization we need to write only one request to update all entries in the database.

/**
* Update created date for all posts
*
* @param \DateTime $newCreatedAt
*
* @return int
*/publicfunction updateCreatedAtForAllPosts(\DateTime $newCreatedAt){$qb=$this->createQueryBuilder('p');$qb->update()->set('p.createdAt',':newCreatedAt')->setParameter('newCreatedAt',$newCreatedAt);
 
    return$qb->getQuery()->execute();}

After that we will get only one request to the database and also will reduce execution time.

3. Doctrine 2 Hydration Waiver

What is hydration? Hydration is a transformation of an array to an object and back.

Hydration is the most costly in terms of time and ORM memory.

That’s why when receiving large amount of data from the database only to display it, for example when retrieving a list, hydrating everything into entities objects will be very costly. It is better to get data as an associative array and save resources.

$posts=$this->createQueryBuilder('p')->getQuery()->getArrayResult();

4. Using Reference Proxies

Suppose we need to add a connection with an author to the post, and we have author’s ID. In most cases, it is done by retrieving the entity of the author from the database using his/her ID and then simply establishing a connection with the author’s post:

$author=$this->getDoctrine()->getRepository('AcmeDemoBundle:Author')->find($autorId);
 
$post=new Post();$post->setAuthor($author);

As it turned out, getting the author from the database is an unnecessary query and it is very easy to discard it. There are Doctrine2 Reference Proxies for this. Here’s how it works:

$em=$this->getDoctrine()->getManager();
 
$post=new Post();$post->setAuthor($em->getReference('Acme\DemoBundle\Entity\Author',$authorId));

This way we got rid of unnecessary query and made a connection with author’s ID only.

5. The Use Of Symfony Profiler Toolbar

Despite the fact that this is the last clause, this tip isn’t the least important. Constant control of what is happening in the profiler will definitely help you to develop effective projects on Symfony2. Thanks to the profiler you will always keep track of your database queries and will be able to optimize them in time to increase website productivity in Symfony 2.

You may learn more about Doctrine 2 architecture here.

Stfalcon.com invites you to collaboration!