One of the Scout’s key features is its ability to quickly highlight N+1 queries in your application that you might not have been aware of, and then show you the exact line of code that you need to look at in order to fix it. In this video, we will use a Ruby on Rails application as an example, but the same concepts apply to other popular web frameworks.
What is the N+1 Problem?
When we talk about the N+1 problem, what we are describing is the inefficient way of querying data from the database, resulting in an unnecessary amount of individual SQL queries being executed. A small inefficiency like this will often go unnoticed in development, but then suddenly cripple the application’s performance when deployed to production.
N+1s happen because the ORM layer of many modern web frameworks employ lazy-loading of records by default which can cause an issue when you try to load the child data for each row of the parent record. With lazy-loading, the number of SQL queries that will execute will be one for each of the child rows, plus one for the parent row, hence the name N+1. When what we only really needed was two SQL queries.
If you don’t know what you are looking for, then the N+1 issue can go unnoticed in your development and testing environments, and then rear its ugly head in production when the number of rows in the database is much higher.
Fortunately, if you are able to identify your N+1 queries, they are easy to fix, often making a huge, instant performance improvement to your application.
How to Find N+1s in your Application Using Scout
Let’s imagine a scenario where you are investigating a slow endpoint in your APM and you are trying to understand why this request is running so slow. You might be looking at what happened at each moment in the request. In Scout, if we detect an N+1 query, then you will be able to see this special label next to the layer of the request where it occurred.
Now we can also see three really useful pieces of information here. We can see the SQL that the ORM generated, we can see how many times that SQL was called, and finally, we can see why it was called this many times using our GitHub integration. This allows us to look at the precise line of code in our application that caused this N+1 query.
Another example of how you can find N+1 queries in Scout is using our Insights tab on the main page. Here we present you with a list of all the N+1 queries that we have found in your application in one handy list.
If you are using Scout for the first time, then this is one of the best places to start optimizing your application. It is the low-hanging fruit of performance issues in your application, as each one of these N+1s that you fix will give your application a performance boost.
How to Fix N+1 Queries Once They’re Identified
I mentioned earlier that the cause of N+1s is the lazy-loading of records from the database by the ORM. So in order to fix an N+1, we just need to request that the ORM uses eager-loading instead.
If we take a look at this code that Scout identified, you can see that on the first line we load some rows from the article table, and then we go on to loop through these results and fetch data from the associated user table.
If, on the first line, we add a call to the Rails includes(:user) method here. This will cause eager-loading to occur, which will result in just two SQL queries being generated, instead of the previous large amount.
Conclusion
The important thing to know about N+1s is that fixing them is quite trivial. The difficulty is actually finding them in the first place, and as you can see, Scout has got you covered there.
Finding N+1’s in Scout is fast, easy and painless.
If you are not already using Scout, then why not find out how easy it is yourself and start your free 14-day trial, and find and fix your first N+1 today!
matthew@scoutapp.com