Last Friday of August I presented a session on the monthly meetup of my local user group Mexicali Open Source
The session name was "ORM won't kill any kittens". It was a few recomentations about how to use an ORM Doctrine in this particular case, since the demo was coded on Symfony2.
This was the topic list:
The Symfony WebProfiler
Write the DQL statements
Lazy Associations
Avoid Object Hydration and use Array Hydration instead
Avoid fetching all of the properties
Avoid loading entities use a reference instead
Batch Processing
Write DQL for Updates
The N+1 Problem
The final point "The N+1 Problem" was the main reason for this session, since a few sessions back another member of the comunity was presenting a demo about a web application and looking at the quantity of executed queries, I mentioned this seems to be the N+1 Problem but what is this.
This issue appear when you need to load the children of an entity-relation. The problem is one query is executed for the parent record and one query of each child record.
As you may expect instead of one single query a lot of queries are executed and that is something you should try to avoid.
Along with the presentation I did a demo of how to solve the "N+1 Problem" writing DQL statements and adding some code on the Controller.
This is the number of the pre-loaded records and the relation between the entities on the demo:
250 Articles
10 Categories (Many to One)
100 Tags (Many to Many)
Without writing any DQL/Query and using default methods provided by Doctrine to load the list of Article entities from the DB.
{% for article in articles %}
{{ article.title }}
{{ article.category.name }}
{% for tag in article.tags %}
{{ tag.name }} {%- if loop.last == false -%}, {%- endif -%}
{% endfor %}
{% endfor %}
Default behaviour: 511 DB Queries execution time 165.19 ms, 261 removing fetch="EXTRA_LAZY" and setting "EAGER" on Article association mapping Tag.
I will show the steps I used to approach this problem and reduce the number of executed queries to only 2.
Categories (Many to One)
The first improvement is create a DQL/Query and use LEFT JOIN on the relation between Article and Category.
public function findByArticleCategory(){
$queryBuilder = $this->getQueryBuilder();
$query = $queryBuilder->select('a.id, a.title, a.body, c.name as category_name')
->from('ORMDemoBundle:Article','a')
->leftJoin('a.category', 'c')
->getQuery();
return $query->getArrayResult();
}
Runnable SQL generated from Doctrine.
SELECT a0_.id AS id0, a0_.title AS title1,
a0_.body AS body2, c1_.name AS name3
FROM article a0_ LEFT JOIN category c1_ ON a0_.category_id = c1_.id;
As simple as this looks it remove the need to execute 10 DB queries when executing the SQL statement.
//Obtain the list of Articles
$articles = $entityManager->getRepository('ORMDemoBundle:Article')
->findByArticleCategory();
Tags (Many to Many)
As you can imagine the following improvement is also related to creating a DQQuery, but first extract all of the Article's ids obtained on the previous query result, the array_column function was used for that purpose.
// Extract the values from the id column on the articles array
$articleKeys = array_column($articles, 'id');
This array of Article ids it's be pass as an argument to the next DQL/Query in this case using the relation between Tags & Articles to implement a LEFT JOIN on Articles and then filter the id column with the passed id values.
public function findTagsByArticle($articleKeys){
$queryBuilder = $this->getQueryBuilder();
$query = $queryBuilder->select('article.id as article_id,
tag.id as tag_id, tag.name as tag_name')
->from('ORMDemoBundle:Tag','tag')
->leftJoin('tag.articles', 'article', 'ON')
->where('article.id IN (:articleKeys)')
->setParameter('articleIds', $articleKeys)
->getQuery();
return $query->getArrayResult();
}
