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.
Twig template
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.
Runnable SQL generated from Doctrine.
As simple as this looks it remove the need to execute 10 DB queries when executing the SQL statement.
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.
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.
Runnable SQL generated from Doctrine
Executing that will return an array of the matching relation between Tags & Articles.
Next use foreach to iterate the result and create a new array and concatenate all of the matching Tags by Article id.
And finally the two arrays $articles & $tags are passed from the controller to the Twig file and use the for loop to display the results
Optimized behaviour: 2 DB Queries execution time 6.23 ms.