How to Detect n+1 Queries in PHP
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.
The post How to Detect n+1 Queries in PHP appeared first on Laravel News.
Join the Laravel Newsletter to get Laravel articles like this directly in your inbox.
