N+1 query problem and how to prevent it using include() ActiveRecord method
One trick to optimise your database queries
I’m trying to write a controller action for the Programs model that shows a list of all users who have signed up to that particular program.
For context, here’s a snapshot of what my tables look like in my database schema (I have more tables in the application, but for simplicity, I’m only showing the ones relevant to the discussion below):
ChatGPT suggested that I use the following code for this action:
def program_users
@program = Program.find(params[:program_id])
@signups = @program.signups.includes(:user)
end
This would allow me to @signups
variable in my view like this:
<% @signups.each do |signup| %>
<tr>
<td><%= signup.user.email %></td>
<% # rest of the code... %>
But this was the first time I had seen this format using includes
keyword. So, I asked:
Me: What does this line do?
@signups = @program.signups.includes(:user)
ChatGPt: This line retrieves all the signups for the specified program and includes the associated user records for each signup. Let's break it down:
@program.signups: This part fetches all the signups associated with the @program object. It's a collection of signup records related to the specific program.
.includes(:user): This part is an ActiveRecord method called includes. It is used for eager loading the associated user records for each signup in the @signups collection. Eager loading helps to reduce the number of database queries and avoid the "N+1 query problem."
By using includes(:user), you're telling Rails to fetch the associated user records for all signups in a single query, rather than executing an individual query for each signup record. This optimization can significantly improve the performance of your application, especially when dealing with large numbers of records.
What is the importance of the .includes method?
Me: I still don't understand the importance of .includes(:user) part. Be my coding mentor and explain it to me so that I can be successful in the future.
ChatGPT: I'd be happy to explain the importance of the .includes(:user) part in more detail.
When you're fetching associated data from a database, the naive approach would be to fetch each associated record individually. This can lead to a large number of queries, which can degrade the performance of your application. This issue is known as the "N+1 query problem."
The "N+1 query problem" occurs when you fetch a list of records (N records) and then fetch their associated records one-by-one, resulting in N additional queries, plus the initial query to fetch the list of records (hence the name N+1).
The .includes(:user) part in the code is an ActiveRecord method used to address the N+1 query problem by eager loading the associated records. Eager loading means fetching the associated records in a single query, rather than executing an individual query for each associated record.
In our case, without using .includes(:user), if you have 10 signups in @program.signups and you try to access the user associated with each signup, Rails would execute 1 query to fetch the signups and then 10 additional queries to fetch the associated user for each signup, resulting in a total of 11 queries.
By using .includes(:user), you're telling Rails to fetch the associated user records for all signups in a single query, which significantly reduces the number of database queries, thus improving the performance of your application.
As a coding mentor, I'd advise you to be mindful of the N+1 query problem when dealing with associated data in your applications. By using eager loading techniques like .includes, you can optimize your application's database queries and ensure better performance. Understanding and addressing performance bottlenecks like the N+1 query problem is an essential skill for a successful developer.