What is an N+1 query?
45sClear, visual explanation of a common performance pitfall that resonates with developers.
▶ Play ClipAI Summary
This video by Benjamin, a PHP performance expert, addresses the N+1 query problem, specifically when it's hidden behind third-party APIs. Using a real-world example from the Frosh platform's share basket module, he demonstrates how a method name like 'add' can mislead developers into inefficiently calling an API in a loop, causing repeated database queries. He shows how profiling with Tideways reveals the issue and how a simple refactor—calling the API once with all items—dramatically improves performance.
Chapters
[00:01]
Introduction to N+1 Problem
Benjamin introduces the topic of performance bottlenecks in PHP, focusing on N+1 queries hidden behind third-party APIs and how method naming can lead to wrong performance assumptions.
[00:29]
Classic N+1 Example
Shows a simple N+1: a query for recent articles, then a loop executing a query per article to fetch the author. Uses SQLite user-defined function to slow down queries for profiling.
[01:28]
Hiding N+1 Behind Layers
Explains that in real code, layers between raw queries can hide N+1 problems. Recommends using a PHP profiler like Tideways to detect them.
[01:42]
Profiling with Tideways
Benjamin uses Tideways, a profiler his team wrote, because it has SQL query profiling and N+1 detection built in.
[01:57]
Code Walkthrough: Frosh Bundle
Steps through the controller: loadCart calls addLineItems, which calls addProduct, which calls CartService->add(). This switches from the Frosh bundle to Shopware core API.
[02:41]
CartService->add() Does More Than Expected
The add() method calls addItemRoot(), calculates, and saves the cart to the database. The API can handle multiple items, but the Frosh bundle calls it in a loop, causing inefficiency.
[03:42]
Real-World Case: Share Basket
In a customer consulting, they found the Frosh share basket module used for large orders with hundreds of line items. Reproducing with a demo store of six items, profiling showed repeated SQL queries (insert cart, select product) – an N+1 problem.
[05:27]
Refactor: Call API Once
Benjamin refactored the code to call CartService->add() only once for all line items, saving the cart and calculating only once. This reduced database operations and improved performance by 3.5 seconds for the customer.
[06:10]
Performance Improvement Demonstrated
After refactoring, the demo store request was 400ms faster, with cart service add and calculation called only once, and queries no longer in N+1 fashion.
Third-party APIs can hide N+1 query problems behind their method names, leading to inefficient code. Profiling with tools like Tideways reveals these issues, and refactoring to call APIs once with all data can yield significant performance gains.
Mentioned in this Video
Study Flashcards (5)
What is an N+1 query problem?
easy
Click to reveal answer
What is an N+1 query problem?
easy
Click to reveal answer
A performance bottleneck where a loop executes a database query for each item, resulting in N+1 queries instead of one.
00:29
What tool does Benjamin use for profiling?
easy
Click to reveal answer
What tool does Benjamin use for profiling?
easy
Click to reveal answer
Tideways, a PHP profiler with SQL query profiling and N+1 detection built in.
01:42
Why can method names be misleading in third-party APIs?
medium
Click to reveal answer
Why can method names be misleading in third-party APIs?
medium
Click to reveal answer
A method like 'add' might also save the cart to the database, leading to repeated saves when called in a loop.
02:41
What was the performance improvement after refactoring the share basket module?
medium
Click to reveal answer
What was the performance improvement after refactoring the share basket module?
medium
Click to reveal answer
3.5 seconds faster for the customer, and 400ms faster in the demo store.
06:10
What is the key refactoring step to avoid N+1 with third-party APIs?
medium
Click to reveal answer
What is the key refactoring step to avoid N+1 with third-party APIs?
medium
Click to reveal answer
Call the API once with all items instead of calling it in a loop.
05:27
💡 Key Takeaways
🔧
Classic N+1 Example
Provides a clear, simple illustration of the N+1 problem that most developers will recognize.
00:29
💡
Method Name Misleading
Highlights how a method named 'add' can hide a database save, leading to inefficient loops.
02:41
🔧
Refactor to Single API Call
Demonstrates a simple yet effective fix that eliminates N+1 queries and improves performance significantly.
05:27
📊
Measurable Performance Gain
Provides concrete numbers (3.5s, 400ms) showing the impact of the refactor.
06:10Full Transcript
[00:01] performance bottlenecks in PHP applications. And in this video, I want to cover one special case where a third-party API hides the N plus1 query and when naming of methods could lead to wrong assumptions with respect to
[00:14] performance. Mo, I am Benjamin and I'm working on PHP performance topics for the last 10 years, helping thousands of developers along the way. But let's back up a little bit. What is an N plus1 performance bottleneck? The simplest
[00:29] case in PHP code is something you have probably all seen. A single database query directly in the code first then a loop around the results and each loop the iteration runs another database query just changing the parameters. So
[00:45] looking at this case here we see select over articles the the recent 10 articles and then we are executing a query here against the users table with the author
[00:58] ID from this articles table. Uh one thing you will notice here um I added SQLite userdefined function to slow down this query we um so that we can see this um in a profiler easier. This code is purposely simplified to show the point
[01:14] about N plus1 performance bottlenecks. And in your code, you probably have various degrees of layers between this raw database queries. But this video is raw database queries. But this video is especially about uh hiding N plus1
[01:28] queries behind these layers. So we will see later what the problem is. You can use a PHP profiler to see the effects of N plus1 queries on performance. And you profilers in the description of this video. In this video, I will use
[01:42] tideways as that is the profiler my team and I wrote and because it has SQL query profiling and one N plus1 detection built in. Let's look at the code what's happening here. So I opened the controller um the load card method
[01:57] controller um the load card method and going to step through that. So load card calls add line items and then what at line item is doing it's calling add product
[02:12] and what add product doing is it's calling card service add and this switches from the the fro bundle to a service card
[02:25] services on shopware itself. So for the authors of the Froch bundle, it's now sort of a switch from their API to a third party API, the Shopware core. And what is the card service at method doing? And that it's the one that's um
[02:41] can sort of like lead to conf uh confusion. It calls at item root at and then let's go to the concrete implementation here.
[02:53] the cart. And what it's doing then it's also calculating something again and then saving the card. So the at method also saves the card into the database. And one thing that we can see is if we
[03:10] go back to the cart service here then we can see the API is built in such a way that it can work with just a single item but it's also perfectly fine to work with multiple items. So the crossover between the third party API to the uh
[03:26] the own API to the third party API um sort of made a small mistake where they sort of used the inefficient way of doing it uh inside a loop calling this over and over again instead of using the more efficient approach. So you're
[03:42] probably asking yourself how does this relate to third party APIs and N plus1 queries in them. In a recent customer consulting, we looked at this Fro platform share basket uh module and it was used um in in the customer store. It
[03:57] allows uh users of the store to share baskets with each other and they used it baskets with each other and they used it to share like really big orders of hundreds of line items and very uh complex calculations.
[04:11] And if you um sort of reproduce this uh this is a demo store that I set up this is a demo store that I set up myself with six items and um I run the profiler to find out what's happening here. So what I'm doing in tideways I
[04:25] click on trigger trace. I generate sort of a um token here for me to append to the URL. Um so this is a shortlived token to
[04:38] generate profiling information with sort of a secret key and then going back uh on the my core graphs list um it generates a call graph for me I can see this call graph it's much uh more complex than the one we saw before and
[04:54] specifically what we can see here is this section of the code calling the share basket controller this is from the fro um module that I showed you um performing operations over and over again. So cart service add card
[05:12] calculate and then we can see in pink the SQL queries being repeated here insert card multiple times selecting the product multiple times. So this is a sort of n plus1 uh problem that is happening here. So I refactored this and
[05:27] uh opened a pull request to only call the card service at method once for all the shared line items. And uh let's look at the code a little bit. So if you at the code a little bit. So if you remember uh the at line item method um
[05:41] called add product and this one added it to the cart already. What the change code now does is it keeps the product here, attaches this to the line item and then at the end of this method at line items, it adds all the line items at
[05:57] items, it adds all the line items at once. And um this leads to the cart only be saved once. Also, the card calculation only being done once. And this reduces the amount of um sort of like database operations quite a lot.
[06:10] Makes the performance much faster. And uh in this pull request I also showed um this um sort of comparison between the old and the new way for this customer where this change alone improved the performance by 3.5 seconds in their
[06:24] case. So let's go back to our simple demo store and uh generate the profiling demo store and uh generate the profiling data again. So another core graph token for me here. I adjust the URL. I go back to this screen
[06:40] waiting for the results. So we have it here. You already see it's much faster than the uh request before. It's 400 milliseconds less. And we can see card service at only called once. Calculator is only called once. And also the
[06:54] queries here are not executed really um in sort of like n plus1 fashion anymore. Instead um they are only done once. Um and it's like uh way more efficient this way. So this was a quick demonstration
[07:10] way. So this was a quick demonstration of how u a third party API sort of can hide it its internals in a way that it makes N plus1's queries quite simple for makes N plus1's queries quite simple for users to make and uh where sort of like
[07:22] how big an improvement can be to refactor this away um and make sure that the queries are only executed a single time. If you like this type of PHP performance content, I would be really happy if you subscribe to this channel
[07:36] or click the link in the description to our newsletter. And I hope to see you our newsletter. And I hope to see you soon. Bye.
