Hi folks,

Happy New Year !!!

Sorry I have been really busy past few months. I always wanted to continue my blog but the amount of workload I had to go through was immense. Fortunately, I found sometime to come up with a new post.

Well, this brief post is about the impact on fetching data mainly using getRepository and DQL (Doctrine Query Language), which you might have already known before. But I took some time out to highlight this fact, mainly for developers who are new to Symfony 2.x & Doctrine 2.x., which can heavily impact on performance on your Symfony application in long run.

Imagine you have 3 tables which are ‘users’, ‘user_roles’ and ‘user_categories’. There are unidirectional many-to-one relationships between ‘users’ to ‘user_roles’ and ‘user_roles’ to ‘user_categories’. Refer to Users.orm, UserRoles.orm and UserCategories.orm files below.

 

Now, assume that you need to bring category_name (from ‘user_categories’ table) for a specific user. There are number of ways to achieve this but most of the time (due to the easiness) we use Doctrine EntityManager’s getRepository() function. See below (assume we are in a Controller),

<blockquote>public function getAllUsers($id = 1)
{

$em = $this-&gt;getDoctrine()-&gt;getManager();
$category_name = $em-&gt;getRepository('TESTFirstBundle:Users')-&gt;find(array('id' =&gt; $id))-&gt;getUserRole()-&gt;getUserCategory()-&gt;getCategoryName();

return $category_name;
}</blockquote>

If you look in to the app/logs/dev file (assuming you are running above test from the dev environment) , you can spot that above will hit the database 3 times. See the log output below,

<blockquote>[2015-01-19 16:42:17] doctrine.DEBUG: SELECT t0.id AS id1, t0.username AS username2, t0.user_role_id AS user_role_id3 FROM users t0 WHERE t0.id = ? [1] []
[2015-01-19 16:42:17] doctrine.DEBUG: SELECT t0.id AS id1, t0.role AS role2, t0.user_category_id AS user_category_id3 FROM user_roles t0 WHERE t0.id = ? [1] []
[2015-01-19 16:42:17] doctrine.DEBUG: SELECT t0.id AS id1, t0.category_name AS category_name2 FROM user_categories t0 WHERE t0.id = ? [1] []</blockquote>

As you can see Doctrine is hitting the database 3 times just to get the category_name. If you look at the log output carefully you will see it fetches records one at a time executing one query each time. So 3 queries means 3 hits to the database which is very costly. We can easily get to the ‘category_name’ column which is in the ‘user_categories’ table  through relationships with just one sql, hit. This is where DQL comes to the party. Look at the DQL below which does the exact same job.

<blockquote>public function getAllUsers()
{

$em = $this-&gt;getDoctrine()-&gt;getManager();

$category_name = $em-&gt;createQueryBuilder()
-&gt;select('uc.categoryName')
-&gt;from('TESTFirstBundle:Users', 'u')
-&gt;leftJoin('u.userRole', 'ur')
-&gt;leftJoin('ur.userCategory', 'uc')
-&gt;where('u.id = :uid ')
-&gt;setParameter('uid', 1)
-&gt;getQuery()
-&gt;getResult(Query::HYDRATE_ARRAY);

return $category_name;
}</blockquote>

If you look at the log file (dev log), you can see just a one query is being executed. See below,

<blockquote>[2015-01-19 16:52:43] doctrine.DEBUG: SELECT u0_.category_name AS category_name0 FROM users u1_ LEFT JOIN user_roles u2_ ON u1_.user_role_id = u2_.id LEFT JOIN user_categories u0_ ON u2_.user_category_id = u0_.id WHERE u1_.id = ? [1] []</blockquote>

The reason for a single hit to the database is due to the perfectly set up relationships at first place. Since we have relationships set up, we can easily locate the destination table through joins. This way is much more efficient and have more control over previous example.

If you would like to see some SQL to DQL examples, please refer to one of my previous post from here.

I hope you enjoyed my post after months of silence. I welcome all your suggestions and feedback. Please place your thoughts on the comments section below.

Thank you. See you again very soon. Cheers!!!