Laravel Idea for PhpStorm - Full-featured IDE for productive artisans!

How to Detect n+1 Queries in PHP

Published on by

How to Detect n+1 Queries in PHP image

What is the n+1 Query Problem?

The n+1 query problem is a common performance issue in software development. N+1 queries result in many unnecessary database calls. This can lead to your app performing at snail-like speed, especially as the data grows. So, you must be aware of and address n+1 queries to ensure your applications are efficient, responsive, and scalable.

N+1 queries occur when an application makes one database query to retrieve an object, and then for each object retrieved, it makes additional queries to fetch related objects. This results in a total of N+1 database queries being executed for N objects, which can significantly reduce the efficiency and performance of the application, especially when dealing with large datasets.

This article will explore how to quickly detect and resolve n+1 queries using Application Performance Monitoring (APM) tools.

N+1 Queries Illustrated:

Let's consider a bookstore application that needs to display a list of authors and their books. The application might first query the database to retrieve all authors (1 query). Then, for each author retrieved, it makes another query to fetch their respective books. If there are 100 authors, this results in 1 (initial query) + 100 (one for each author) = 101 queries in total.

This is very inefficient and can severely degrade the performance of the application.

To avoid the N+1 query problem, developers often use techniques like eager loading, where related data is loaded in the initial database query itself, or batch fetching, where associated data for multiple objects is retrieved in batches.

How do you Detect n+1 Queries?

If you have a non-trivial application, you likely have n+1 queries. If your application is built with a web framework like Laravel or Symfony and uses an ORM, you will surely have many n+1 queries. This is because the ORM layer of many modern web frameworks lazy-loads records by default.

N+1 query problems will likely go unnoticed in your development and testing environments. Still, they may suddenly ruin the application’s performance when deployed to production, where the number of rows in the database is much higher.

The giveaway sign that your application has n+1 queries is an unusually high number of database queries being performed.

The queries will usually be sequential and non-overlapping.

This is all well and good, but you might be wondering, “Okay, but how do I know how many queries are being performed and if they are ‘sequential and non-overlapping?’” Great question! That’s where Application Performance Monitoring (APM) tools come in.

Using an APM Tool to find n+1 Queries:

Application Performance Monitoring tools, as the name suggests, are applications you can use to monitor and diagnose issues with your app. Such tools can help you monitor all sorts of performance metrics, including database queries.

There are many different APM tools available. For this example, I’ll be using Scout APM.

Scout APM makes finding n+1 queries very straightforward because it has a dedicated n+1 insights tab. The n+1 insights tab displays a list of all the endpoints in your application (highlighted red), and for each endpoint, you can see how many queries were run and how long they took. Clicking on an individual endpoint will reveal more in-depth information.

On the endpoint details page, you get a very helpful timeline view where you can see when an n+1 showed up, for example, after deploying an update. The screenshot above depicts the condensed view that shows streamlined insights into what's going on with the n+1 queries. Click the SQL button (highlighted blue) here, and Scout will show you the blameworthy SQL query.

Backtrace

While on the endpoint details page, you can click the Backtrace button to find the exact line of code responsible for the n+1 query.

You can see in the image above that Scout backtraced the problematic code down to the exact linecausing the issue. The code snippet is displayed because I use Scout’s GitHub integration. But even If you don’t have the GitHub integration, Scout will still report the culpable code file and line number.

Now that we know how to hunt down n+1 queries using an APM let's move on to resolving them.

Resolving n+1 Queries

To illustrate how to solve an N+1 query problem in a PHP application using an ORM (like Laravel's Eloquent or Doctrine), we’ll revisit the example based on the bookstore application scenario mentioned earlier.

Scenario:

You have a database with two tables: authorsand books. Each author can have multiple books. Let’s first see the code that causes the N+1 query problem, and then I'll show how to solve it.

Code with N+1 Query Problem:

// Retrieve all authors
$authors = Author::all();
 
foreach($authors as $author) {
   // For each author, retrieve their books$books = $author->books()->get(); // This causes an additional query for each author
   // Process the books...
}

In this code, the first query retrieves all authors, and then, for each author, a new query is executed to fetch their books, leading to n+1 queries.

N+1 Query Solution 1: Eager Loading

One approach to solving the n+1 query problem is using a strategy called eager loading. With eager loading, you load the related models (in this case, books) in your initial query.

// Eager load books with authors in one query
$authors = Author::with('books')->get();
 
foreach($authors as $author) {
   // Now, no additional query is made here
   $books = $author->books;
   // Process the books...
}

N+1 Query Solution 2: Join Query

Sometimes, you might want to use a JOIN statementto fetch everything in a single query. You can do this using raw queries or a query builder supplied by your framework.

Raw Query Example (using PDO):

$sql = "SELECT * FROM authors JOIN books ON authors.id = books.author_id";
$stmt = $pdo->query($sql);
$authorsAndBooks = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Process the books accordingly

Query Builder Example (in Laravel):

$authorsAndBooks = DB::table('authors')
   ->join('books', 'authors.id', '=', 'books.author_id')
   ->get();
 
// Process the results accordingly

Using a raw query or a query builder allows you to write a more optimized SQL query to fetch the data you need in a single request to the database.

N+1 Query Solution 3: Caching

Yet another approach to resolving n+1 query problems is to use caching. Caching offers a strategic solution to the n+1 query problem, particularly when data doesn't change frequently. By storing the results of database queries in a cache, subsequent requests can retrieve data from this cache instead of hitting the database again. This significantly reduces the number of queries made to the database, especially for repeated requests.

You should note that you can use caching together with either one of the previous solutions.

N+1 Queries in PHP: Conclusion

Understanding and addressing n+1 queries is crucial for optimizing PHP applications. We've explored what n+1 queries are, how they can silently degrade app performance and the strategies to detect n+1 problems with tools like Scout APM.

Resolving n+1 queries isn't just about improving speed; it's about writing smarter, more efficient code. By applying these insights, you can ensure a smoother and faster experience for your users.

Sarah Morgan photo

Sarah has over 18 years of experience in the software industry. In addition to her current role as a Sr. Product Manager at Scout, she is a featured speaker at Product School and a product strategy consultant for Tapple.io.

Filed in:
Cube

Laravel Newsletter

Join 40k+ other developers and never miss out on new tips, tutorials, and more.

Laravel Forge logo

Laravel Forge

Easily create and manage your servers and deploy your Laravel applications in seconds.

Laravel Forge
Tinkerwell logo

Tinkerwell

The must-have code runner for Laravel developers. Tinker with AI, autocompletion and instant feedback on local and production environments.

Tinkerwell
No Compromises logo

No Compromises

Joel and Aaron, the two seasoned devs from the No Compromises podcast, are now available to hire for your Laravel project. ⬧ Flat rate of $7500/mo. ⬧ No lengthy sales process. ⬧ No contracts. ⬧ 100% money back guarantee.

No Compromises
Kirschbaum logo

Kirschbaum

Providing innovation and stability to ensure your web application succeeds.

Kirschbaum
Shift logo

Shift

Running an old Laravel version? Instant, automated Laravel upgrades and code modernization to keep your applications fresh.

Shift
Bacancy logo

Bacancy

Supercharge your project with a seasoned Laravel developer with 4-6 years of experience for just $2500/month. Get 160 hours of dedicated expertise & a risk-free 15-day trial. Schedule a call now!

Bacancy
Lucky Media logo

Lucky Media

Get Lucky Now - the ideal choice for Laravel Development, with over a decade of experience!

Lucky Media
Lunar: Laravel E-Commerce logo

Lunar: Laravel E-Commerce

E-Commerce for Laravel. An open-source package that brings the power of modern headless e-commerce functionality to Laravel.

Lunar: Laravel E-Commerce
LaraJobs logo

LaraJobs

The official Laravel job board

LaraJobs
SaaSykit: Laravel SaaS Starter Kit logo

SaaSykit: Laravel SaaS Starter Kit

SaaSykit is a Multi-tenant Laravel SaaS Starter Kit that comes with all features required to run a modern SaaS. Payments, Beautiful Checkout, Admin Panel, User dashboard, Auth, Ready Components, Stats, Blog, Docs and more.

SaaSykit: Laravel SaaS Starter Kit
Supercharge Your SaaS Development with FilamentFlow: The Ultimate Laravel Filament Boilerplate logo

Supercharge Your SaaS Development with FilamentFlow: The Ultimate Laravel Filament Boilerplate

Build your SaaS application in hours. Out-of-the-box multi-tenancy and seamless Stripe integration. Supports subscriptions and one-time purchases, allowing you to focus on building and creating without repetitive setup tasks.

Supercharge Your SaaS Development with FilamentFlow: The Ultimate Laravel Filament Boilerplate
Rector logo

Rector

Your partner for seamless Laravel upgrades, cutting costs, and accelerating innovation for successful companies

Rector
MongoDB logo

MongoDB

Enhance your PHP applications with the powerful integration of MongoDB and Laravel, empowering developers to build applications with ease and efficiency. Support transactional, search, analytics and mobile use cases while using the familiar Eloquent APIs. Discover how MongoDB's flexible, modern database can transform your Laravel applications.

MongoDB

The latest

View all →
PHPStan 2.0 is Here image

PHPStan 2.0 is Here

Read article
Run multiple CLI commands locally at once with Solo for Laravel image

Run multiple CLI commands locally at once with Solo for Laravel

Read article
Securing Laravel Applications with Stephen Rees-Carter image

Securing Laravel Applications with Stephen Rees-Carter

Read article
Laravel on any Developer Machine with Gitpod image

Laravel on any Developer Machine with Gitpod

Read article
Build your slide deck in Laravel with Simple Slides image

Build your slide deck in Laravel with Simple Slides

Read article
Lazy JSON Pages is a JSON API Scraper for PHP image

Lazy JSON Pages is a JSON API Scraper for PHP

Read article