Almost any app that we build has a lot of interactive options with a lot of data, to this we can also have conditions to which data should we show to what type of user, making us request data from different tables based on a unique identifier, if the queries are not handled in a proper manner this can slow down the loading, and that is because of the "N + 1" problem. What is the "N + 1" problem? The problem occurs when we have to make a query to get each of the associations, "1" being the first query to get the parent and "N" the number of queries required to get all the associations, let's take a look into an example. author.rb < : class Author ApplicationRecord has_many articles end article.rb < : class Article ApplicationRecord belongs_to author end articles_controller.rb = Article.where( , start_date, end_date)
  end
end < @ class SalesController ApplicationController def index articles_list "created_at >= ? AND created_at <= ?" index.html.erb <% @articles_list.each |article| %> <h1> <br> do < > h3 < %> %= article.created_at </ > h3 < %> %= article.author.name </ > h1 < %> % end If we run the example above we will notice that we are going to get more than one query in the terminal. We have on query to get all the articles in the range of date Article ( ms) .* (published_at >= published_at <= ) Load 0.4 SELECT "articles" FROM "articles" WHERE '2020-03-13' AND '2020-06-24' And we have an extra query to get the authors name of each article Author Load ( ms)  SELECT .* FROM WHERE . = ? LIMIT ?  [[ , ], [ , ]]
Author Load ( ms)  SELECT .* FROM WHERE . = ? LIMIT ?  [[ , ], [ , ]]
Author Load ( ms)  SELECT .* FROM WHERE . = ? LIMIT ?  [[ , ], [ , ]]
Author Load ( ms)  SELECT .* FROM WHERE . = ? LIMIT ?  [[ , ], [ , ]] 0.1 "authors" "authors" "authors" "id" "id" 3 "LIMIT" 1 0.1 "authors" "authors" "authors" "id" "id" 4 "LIMIT" 1 0.1 "authors" "authors" "authors" "id" "id" 1 "LIMIT" 1 0.1 "authors" "authors" "authors" "id" "id" 2 "LIMIT" 1 This is the "N + 1" problem, we have one query to get all the article but once we try to access the child (the relation to the author model) it will create a new query for each one. How to avoid this? There are Three ways to avoid this problem Preload, Includes and eager_load, we will not talk about preload since the behavior is pretty much the same as includes, but if we use Preload we can't use a "where" clause in the associated table and with Includes we can! Includes Includes helps us to fix the problem by making only two queries to get all the data, let's modify our example and take a look to the terminal. articles_controller.rb @articles_list = Article.includes(:author).where( , , ) "published_at >= ? AND published_at <= ?" '2020-03-13' '2020-06-24' We only have to add "includes(:name_of_association)" next to the name of the model and we will be using includes! Now let's refresh and take a look at our terminal Article ( ms) .* (published_at >= published_at <= )
  Author ( ms) .* . (?, ?, ?, ?)  [[ , ], [ , ], [ , ], [ , ]] Load 0.4 SELECT "articles" FROM "articles" WHERE '2020-03-13' AND '2020-06-24' Load 0.5 SELECT "authors" FROM "authors" WHERE "authors" "id" IN "id" 3 "id" 4 "id" 1 "id" 2 Now we only make two queries to get all the data of the main model and the specified associations if we want to get all the associations we have to add the name to the includes like this Model.includes(:association_one, :association_two) eager_load This will create a single query using a left outer join to get the data of the main table and the association, we can also use a where clause to give conditions to the associated table, to use it is pretty much the same as we do with includes article_controller.rb @articles_list = Article.eager_load(:author).where("published_at >= ? AND published_at <= ?", start_date, end_date) We have to add the name of the association in the parenthesis next to eager_load as we would do it with includes Terminal SQL (0.4ms) . t0_r0, . t0_r1, . t0_r2, . t0_r3, . t0_r4, . t0_r5, . t1_r0, . t1_r1, . t1_r2, . t1_r3, . t1_r4, . t1_r5 . = . (published_at >= published_at <= ) SELECT "articles" "id" AS "articles" "title" AS "articles" "published_at" AS "articles" "author_id" AS "articles" "created_at" AS "articles" "updated_at" AS "authors" "id" AS "authors" "name" AS "authors" "age" AS "authors" "city" AS "authors" "created_at" AS "authors" "updated_at" AS FROM "articles" LEFT OUTER JOIN "authors" ON "authors" "id" "articles" "author_id" WHERE '2020-03-13' AND '2020-06-24' As we can see with this we only need a single query to get all the data, the structure of the query will be created using a left outer join. Which one to use? As I metioned earlier we didn't used Preload since Includes does pretty much the same but we the difference that we can use a where clause, and Includes can also do the same as eager_load, we can force a left outer join using references @articles_list = Article.includes(:author).references(:author).where("published_at >= ? AND published_at <= ?", '2020-03-13', '2020-06-24') Not only that but when using includes even without the use of references sometimes it will automatically choose to use a left outer join query, then why don't we just stick with includes? well the reason is that although is designed to use the best possible pattern, sometimes it can go wrong and force a join where is not needed slowing down the process, you can get it right more often than rails.

Different

Follow me on Github!

Read My Stories

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

How To Avoid N + 1 problem in Rails

Too Long; Didn't Read

Company Mentioned

Josué

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

How To Avoid N + 1 problem in Rails

Too Long; Didn't Read

Company Mentioned

Josué

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES