If you’re a Rails developer, you’ve come across the term N + 1 query. And this is something you need to avoid.
The N + 1 query occurs when loading from the database a group of records in an inefficient way, along with the records associated with them. The examples below dive into how we can solve this problem with: includes and will help shed some light on how this method works. Note that the code is using Ruby 2.3.4 with Rails 4.2.11.
Give out the problem
We have a model: Employee has many Forms. 
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | # == Schema Information # Table name: employees # id # name class Employee < ApplicationRecord has_many: :forms end # == Schema Information # Table name: forms # id # employee_id # kind class Form < ApplicationRecord belongs_to: :employee end |
We have a total of 5 employee records and forms for each employee
Employee.all.map { |employee| employee.forms }.flatten
The SQL query is as follows:
1 2 3 4 5 6 7 | > SELECT `employees`.* FROM `employees` ORDER BY `employees`.`id` > SELECT `forms`.* FROM `forms` WHERE `forms`.`employee_id` = 1 > SELECT `forms`.* FROM `forms` WHERE `forms`.`employee_id` = 2 > SELECT `forms`.* FROM `forms` WHERE `forms`.`employee_id` = 3 > SELECT `forms`.* FROM `forms` WHERE `forms`.`employee_id` = 4 > SELECT `forms`.* FROM `forms` WHERE `forms`.`employee_id` = 5 |
There were 6 visits to the database made because we loaded the employees in the first query and then made 5 more queries to get the forms of each employee. In other words, N + 1 SQL selects occurs where N = 5.
Execute query with: includes
Rails provides an ActiveRecord method called: includes loading related records first and limiting the number of SQL queries made to the database. This technique is called “eager loading” and in many cases will improve performance significantly.
Depending on what your query is: includes will use the ActiveRecord: preload or: eager_load methods.
When: includes use: preload?
In most cases: includes will use the default method: preload will fire 2 queries:
- Download all the records associated with the leading model
- Load the records associated with the leading model based on the foreign key on the model associated with the leading model So if we use: preload our query, we will only generate 2 SQL selects for forms. , based on foreign key Form # employee_id.
1 2 3 4 5 6 | Employee.preload(:forms).map { |employee| employee.forms }.flatten > SELECT `employees`.* FROM `employees` > SELECT `forms`.* FROM `forms` WHERE `forms`.`employee_id` IN (1, 2, 3, 4, 5) |
The SQL query for this example will be similar to how we replace: preload with: includes.
1 2 3 4 5 | Employee.includes(:forms).map { |employee| employee.forms }.flatten > SELECT `employees`.* FROM `employees` > SELECT `forms`.* FROM `forms` WHERE `forms`.`employee_id` IN (1, 2, 3, 4, 5) |
When: includes use: eager_load?
: includes will default to using: preload unless you reference the link being loaded in the next clause, such as: where or: order. When building a query this way, you also need to explicitly reference the eager loaded model.
1 2 3 | Employee.includes(:forms).where('forms.kind = "health"').references(:forms) |
In this case,: includes will use the: eager_load method to create a query that uses the left outer join to build the intermediate table which is then used to build the output.
1 2 | > SELECT `employees`.`id` AS t0_r0, `employees`.`name` AS t0_r1, `forms`.`id` AS t1_r0, `forms`.`employee_id` AS t1_r1, `forms`.`kind` AS t1_r2 LEFT OUTER JOIN `forms` ON `forms`.`employee_id` = `employees`.`id` WHERE (forms.kind = "health") |
The SQL query for this example is similar to how we replace: eager_load with: includes. We can remove: references in this case.
1 2 3 4 | Employee.eager_load(:forms).where('forms.kind = "health"') > SELECT `employees`.`id` AS t0_r0, `employees`.`name` AS t0_r1, `forms`.`id` AS t1_r0, `forms`.`employee_id` AS t1_r1, `forms`.`kind` AS t1_r2 LEFT OUTER JOIN `forms` ON `forms`.`employee_id` = `employees`.`id` WHERE (forms.kind = "health") |
However, if you replace: preload with: includes, the query will not execute.
Can use: includes with model forms instead of leading model?
If the query is reversed, where forms are loaded first and we want to effectively load both tables linked to the employee table, we can still use: includes. The query will load the employee based on the Employee # ids collection, referenced from Form # employee_id.
1 2 3 4 5 | Form.includes(:employee) > SELECT `forms`.* FROM `forms` > SELECT `employees`.* FROM `employees` WHERE `employees`.`id` IN (1, 2, 3, 4, 5) |
Is it possible to eager load nested associations with: includes?
Here are some examples of different situations that you can use if the Model model has been modified to have additional associations such as has_one:: signer and has_one: issuer, including has_one:: address.
1 2 3 4 5 6 7 | Employee.includes(forms: :issuer) Employee.includes(forms: { issuer: :address }) Employee.includes(forms: [{ issuer: :address }, :signer]) |
Use: includes always faster
How to increase performance by using: includes in your code? And when: includes calls: preload or: eager_load, which method ends faster?
I checked the performance of the same queries listed in the examples above in a local version of the database to see the difference. Each query is run 3 times with caching disabled and then averaged in the table below.
1 2 3 4 5 6 | Employee.first(n).map { |employee| employee.forms }.flatten Employee.preload(:forms).first(n).map { |employee| employee.forms }.flatten Employee.eager_load(:forms).first(n).map { |employee| employee.forms }.flatten |
Data shows that using: includes will improve performance significantly when: preload is called, but has the opposite effect: eager_load in most cases. : eager_load builds a complex query to gather information about the links being loaded, so that means this option will be slower (although I was a bit shocked when it dropped sharply).
Measuring query performance in your own application is the best way to ensure you are using the most effective methods available for your use cases. However, as a general rule, getting into the habit of using: includes will lead to performance code that creates the best experience for other developers in your code – and of course, customers who interact with your application.
Refer:
https://engineering.gusto.com/a-visual-guide-to-using-includes-in-rails/