Understanding N+1 Database Queries
Prince Sinha
on
November 23, 2022
N+1 queries are the most common problems among developers. N+1 database query problems occur when you have to call the database for N items, and those N items have again N additional data fields which are not in the same table, and those extra N data fields are required for the use case. Generally, this issue is handled at the time of database designing, but every problem cannot be solved efficiently by one solution, some need to be solved by brute force. The N+1 database queries require brute force techniques in some use cases. In general, these N+1 queries use nested loops in which one loop calls the first N object and the second loop calls N queries for each N object.
How to Avoid N+1 Queries in Rails
N+1 Rails queries
Ruby on Rails often faces scalability problems due to these N+1 queries. This N+1 query does not create much problem for small applications, but when your application gets larger, it becomes a scalability issue. Let us suppose we want to fetch the details of books using a get API. But books also contain other information for the author, which is stored in some other table that could not be accessed by the normal join method. In this, the query will be like the below code:
class PostsController < ApplicationController
def index
posts = Post.all
render json: posts
end
end
class Post
belongs_to :author, class_name: 'User'
end
class PostSerializer < ActiveModel::Serializer
attributes :id, :title, :details
belongs_to :author
end
In this case, we have n queries for fetching the book's details, and for each book, we have to fetch the user object of the authors. This can literally timeout the application if the number of objects becomes large. In the next part of this blog, we will see the various ways to deal with this N+1 query problem.
How to Avoid N+1 Queries in Rails
If we do N extra SQL queries increase the application's response time. Hence there should be some checks to avoid N+1 queries. One such solution is eager loading. Eager loading helps you load all of the N queries data in just 1 query. In Ruby on Rails, implementing eager loading is very simple. You just have to add .includes(:branches) in the query.
builds = Build.order(:finished_at).includes(:branches).limit(10)
builds.each do |build|
puts "#{build.branch.name} build number #{build.number}"
end
Eager loading internally uses the JOIN method for fetching all the associates. In this case, we only need to do two queries - one for fetching the build and another for fetching the associates. This is much better than having N+1 queries for the same transaction.
Another solution for resolving N+1 queries in Rails code is failing the pipeline for deployment when any such query is identified. The only possible way to avoid future cases of N+1 queries is to make your developers aware of the problems and the solutions. You can enable CI/CD pipelines to prevent N+1 queries. One such method is Bullet. It helps in optimizing and identifying N+1 queries. To enable bullet, you have to add bullet in your gem. After that, all your N+1 queries will be logged in log/bullet.log file.
For raising the CI error during pipeline for N+1 queries, you have to add the following line in the configuration file i.e, config/environments/test.rb file.
Bullet.raise = true # raise an error if an n+1 query occurs
This line will throw an error whenever it identifies the N+1 queries, hence failing the pipeline. In this way, it makes sure there will be no N+1 queries in the future in the production code.
But if you have many N+1 queries in your production code that are making your application slow, then enforcing this bullet method will result in so many errors and failing the pipeline. The best way to do this is to make a list of all N+1 queries and bypass them through some code; in the same way, we avoid some lines for linting errors. Then after that, enable bullet raises the condition. It will prevent adding more N+1 queries in production from then. The next step is slowly removing those bypassed slow queries with the correct code.
Performance and N+1 Queries
Now we have seen the methods that can be used to avoid N+1 queries. But while solving the N+1 queries we also need to monitor the application's performance. Whenever you solve N+1 queries, you should focus on the most important ones. For example, let's say there are 10 places in your code where the N+1 query exist. You must figure out of those 10 places which N+1 query is used most, and you should solve that first. This way, you can make a big impact on the speed of your application in less time.
But how will you find the N+1 queries in your application? ScoutAPM helps you pinpoint all those places where the N+1 query is being used. It highlights them in the dashboard so that you can easily see the frequency of each of them and which is impacting the application’s speed more. You will see special labels on those points where the N+1 query is used. In addition, you will the SQL generated for the query, how many times it was executed, and exactly that line of code where this N+1 query is being used using our GitHub extension.
Scout APM also lists all your N+1 queries in a separate tab where you can see each individually. You can solve each of them easily from this tab because it is much easier to see their details. As you solve N+1 queries one by one, you will see many improvements in the speed of your application.
N+1 Queries and Scaling
N+1 queries decrease the speed of the application a lot, especially when the queries are often used. Whenever you try to scale your application and don’t know about the N+1 queries, you will most likely face many problems. Slow application decreases user retention by many folds, and user retention is most important for Google rankings. Scout APM helps remove the N+1 queries by pinpointing the issues where N+1 queries are used.
Scaling the application without knowing the cause and solution of N+1 queries can never be possible because as you scale, you will face a decrease in the application speed, and these customers don’t like slow applications.
Conclusion
So far, we have discussed that N+1 queries can be troublesome for your application. It decreases the speed at many places where SQL queries are being used. There are many easy methods for solving the N+1 queries, and also, to stop the further uses of N+1 queries, we can put some CI CD validations like a bullet. But the main problem with the N+1 query is the detection of N+1 queries. ScoutAPM makes detecting the N+1 queries very easy by pinpointing the issues. The dashboard of ScoutAPM shows the timeline in which those requests occur with some special label. You can see the generated SQL query, the time it took, and the exact piece of code that caused the issue. This way, you can easily look into the code and contact the developer for the resolution.
If you are facing the N+1 query problem or don’t know what is slowing your application, you should try ScoutAPM. You may have used some other APMs also, but the insights given by ScoutAPM will make your monitoring life very easy. You just need to sign up on their website for the trial of Scout APM. Scout offers a 7-day free trial without even showing your credit card. Just check out this link and get started for free. After that, you decide which APM gives you more insight. Happy monitoring!
