Optimise queries and speed up your Laravel application

Roughly a 6 minute read by Jason

Blog Image

Sub-optimal queries can be difficult to identify whilst you are working on an application with a small data set. You might not initially notice any issues but, as time passes and with more data added over the years, the cracks will begin to show. A query that used to be quick and snappy can take exponentially longer to execute as more data is acquired, resulting in longer load times or requests timing out.

Mistakes like these can be quite easy to make (I know I’ve made them once or twice over the years); thankfully, tools and packages have been created to help us counteract this. In this post we’ll take a look at one such package and how it can help with optimising your application.

Laravel Debugbar to the rescue

The Laravel Debugbar package allows you to easily see how your application is performing during development. This powerful tool is going to make your life a lot easier and reduce technical debt that could be problematic in the future. The package has a lot of features but we’re only going to look at two key areas (views and queries tabs) to help fix some common problems.

Debugbar

Common problems & solutions

Wildcard view composers

In Laravel you can create view composer files that allow you to always pass data to views whenever they are rendered. You can attach view composers to multiple views. A view composer will also accept a wildcard which will attach data to every view. This sounds really handy, and it is, but it can have some unintended side effects if you’re not careful!

This wildcard action is easily misused, resulting in redundant queries being performed. An example of this is assuming we need navigation data in every request, and using a wildcard view composer to solve this problem.

Using a fairly common view structure we will load the pages/home view which extends the layouts/main view. This in turn includes partials/meta, partials/header & partials/footer. Finally, partials/navigation is included in partials/header.

Screenshot 2019 03 06 At 16 33 25

We will attach a view composer with the wildcard code which adds the navigation query to the request.

Screenshot 2019 03 06 At 16 51 53

When we view the home page in the browser, Laravel Debugbar generates the above output. The code successfully runs and the home page is rendered with the navigation items, but upon closer inspection with the Debugbar we can see that 6 identical queries were executed on page load in the queries tab, why is that?

Screenshot 2019 03 06 At 16 54 18

The views tab in the Debugbar allows us to dive in and examine what has happened. Because the wildcard has been used in the view composer, each individual view has executed a navigation query. In this example, only our partials/navigation view requires the nav_items data.

If we make our view composers more granular so that they follow the ‘single responsibility principle’ we can prevent unnecessary queries from being executed. This is a rudimentary example but it demonstrates how to prevent our page loads from becoming bloated.

Screenshot 2019 03 06 At 21 11 34

The n+1 problem

Relationships provided by Eloquent are extremely useful but they lead to another common problem that easily goes unnoticed without the help of Laravel Debugbar. I think every developer who’s used Laravel will have made this mistake at least once (or maybe just me 😅).

If relationships have been implemented for a model then we have a shortcut to query associated data on other models, often called ‘lazy loading’. Under the hood this fires another query, which can be problematic if we are looping through a large data set. This can become exponentially worse if we are calling relationships of relationships leading to hundreds of queries being executed.

In this next example we have a navigation model with a one to one relationship of extras. The nav items content is stored on the extras relationship which we will access as we loop through the navigation items, like so: $nav_item->extras->name.

Screenshot 2019 03 07 At 09 40 43

By using the Debugbar we can see that the number of executed queries has increased. This is because we haven’t pre-fetched the relationship with our navigation model, so Laravel is performing a new query every time it tries to access the relationships data.

Laravel details this problem in its documentation, and provides a way to optimise these relationship queries by eager loading data. The with() method will pre-fetch this data for us.

Screenshot 2019 03 07 At 09 47 40

Immediately we can see that the number of queries being executed is more maintainable. All relationships have been eager loaded and are ready to be utilised.

Do you really need all that data?

For bonus points, you can further optimise your application by selecting only the data required on the page by being explicit in our use of the select() and with() methods. Remember to fetch relationship ids in order for the queries to continue working!

Screenshot 2019 03 07 At 09 53 52

In summary

Following these simple tips can help reduce the amount of unnecessary queries being executed, improving load times and negating the risk of a request timing out (in certain circumstances).

The Laravel Debugbar helps to highlight these problems, allowing developers to more easily pinpoint issues and resolve them.

Jason focuses on delivering functionality that really hits the mark. He can usually be found puzzling over a new development challenge, or working away on some of Engage’s longer term projects.