Improving Page Load Time With Doctrine Join

When first starting out with Symfony, there's so much going on during a given page render that you can be forgiven for not paying attention to the finer details.

However, as your knowledge of the Framework improves and your comfort level grows, you may / will hopefully start wondering about that funny number in the bottom right corner:

And more importantly, you may wonder why it seems to keep growing, and why suddenly it has turned a mustardy yellow colour. That can't be good, right?

Doctrine makes it easy for us to interact with our database. That's great. But behind the convenience methods (find, findAll, etc) are queries that may not be as efficient as could be for our situation.

And when we combine this with looping through these queries inside our Twig templates, suddenly we have this mass of query overhead that can - with a quick change - be very simply removed from our code.

Example

This video follows on / uses the Will Durand Faker Bundle to create our dummy data. If you haven't already, consider watching that video before this one.

In this video, we will take the concept of a Forum. Our Forum will have Topics which contain a random number of Replies.

To begin with, all is rosy. We have our ten topics, and one simple findAll query is returning all our topics in one query.

However, our Forum suddenly gets a changing business requirement.

We now need to display the number of Replies that a Topic has received.

To make things a little more interesting, we will simulate some healthy growth of our Forum, taking our Topics count from 10 to 40, and our Replies count up to 3000.

Now, this is a problem I have seen a number of times as projects move from the team that develops the initial build on to the guys and girls who have to manage the system in Production.

Usually the initial build team have been working with a tiny data set. As such, they don't experience the horrific slowdown that occurs when the database size grows when real people start putting real data in to it.

There are other issues that usually crop up here - lack of indexes being the most common - but query optimisation is pretty straightforward. So let's look at how we can resolve this problem.

Rather than use the findAll() method, we can replace that with a Doctrine Query Builder equivalent, and become much more specific about what we want Doctrine to do for us.

As such:

$topics = $em->getRepository('AppBundle:Topic)->findAll();

becomes:

$qb = $em->createQueryBuilder('qb');

$topics = $qb->select('t', 'r')
    ->from('AppBundles:Topic', 't')
    ->join('t.replies', 'r')
    ->getQuery()
    ->getResult()

Now, that looks a little scary / confusing. I remember when I first saw this, I didn't like it.

But stick with me, it's not that complicated.

$qb = $em->createQueryBuilder('qb'); - we need a way to tell Doctrine how to create our queries. We ask the Entity Manager ($em) to give us a QueryBuilder object, which is a builder provided by Doctrine to help in the creation of Doctrine DQL queries. Official Manual Page of the Doctrine Query Builder

$qb->select('t', 'r') - that's very similar to when we give our tables shorter names in SQL. We could just as easily say: $qb->select('topics', 'replies') but that's more to keep typing out.

Next up, we let Doctrine know where we expect it to find the contents of t and r.

    ->from('AppBundles:Topic', 't')
    ->join('t.replies', 'r')

The from line is pretty straightforward. Think of it like select * from Topics in good old SQL.

Then the join line is a bit confusing. We join on the relationship with the Entity we already know about.

So, we already know t is Topic, so we don't need to tell Doctrine about the Replies Entity as it can deduce this itself from the relationships it already knows about. Instead, we just say you can find what we want to be put in r on the replies relationship defined in our Topic entity.

Then lastly we call:

    ->getQuery()
    ->getResult()

And that does as you would expect - get's the query, and then get's us the result of running that query.

Apologies if you knew all that.

The Outcome

But the upshot of all this is that our query overhead is now dramatically reduced.

By telling Doctrine so specifically about what result we want, it now collects all the required data in one query. Joining two tables in one go, rather than getting the list of Topics and then when looping / iterating through them, saying "oh, ok, I now need to find all the replies for id: 1", "ah, darn, I now need to find all the replies for id: #2" etc etc multiplied by however many Topics you are displaying per page.

Additional Reading

If any of this is a little fuzzy for you, I suggest checking out the Symfony documentation on Querying For Objects as there is always plenty more to be learned :)