N+1 problemEdit on GitHub
Some actions, parts of the website, or the entire website are slow.
It can be any type of action that is performed for each entity. For example, suppose a customer has a cart with 20 items. In this case, we do the following on each item:
- We make a request to a DB per item.
- We make an external call per item.
Cases may vary, but the main idea is that we perform some action per item.
Repeated action per entity.
Change action from per entity to batch of entities.
N+1 problem—external calls in New Relic
For example, suppose there is an order with 42 items. In the New Relic tool, do the following:
Select the desired profiling:
Check the details and analyze:
As you can see in the profiling, we make 42 external calls, which is equal to the number of items in the order.
CurlResponse::select and the request itself we spend 45.70% and 45.49%, so 91.19% of the total time spent on this request.Therefore, optimizing the number of requests should be the priority.
- Define a strategy to fix the problem. Specifically, you can:
- Make one bulk request.
- Move request after the
place orderaction. For example, use one of the OMS transitions (but after taking the customer to a success page).
However, these approaches may not always be applicable. For example, in the cases when:
- A 3rd party system does not support bulk operations.
- You have to make a call right during the order placement.
In the cases like these, you need to develop your solution. Some of the most common recommendations include:
- Contacting the third-party system to provide you with an integration point for bulk operations.
- Talking to the business stakeholders to define where else this action can be performed.
- Updating the loader on the page with some interactive games, so customers are not bored if nothing else can be optimized.
N+1 problem—DB queries in Blackfire
For example, suppose you add products to cart in a B2B store. In the Blackfire tool, do the following:
- Go to SQL.
- Order by calls or by time.
- Optimize the calls and, ideally, reduce the number of calls to one.
For submitting the form