Don't underestimate the N + 1 problem!

Introduction

I knew the "N + 1 problem" until now, but I hadn't done such a large-scale development, so I spent my time without being conscious of it. .. However, when I actually worked in the field, I realized that in large-scale development, there are tens of thousands of data in Zara, so I have to be aware of it from now on (crying). I think that everyone needs knowledge that is almost always necessary when they actually enter the site, so please hold it down here! !!

First, what is the N + 1 problem?

Simply put, it is a problem that SQL is issued each time in a loop process, and a large amount (more than necessary) of SQL is issued, resulting in poor performance. Let me give you an analogy. When you want to get 10 products of User named A and display the product list page of Mr. A,

Once to get Mr. A's User data 10 times to get 10 Products data You will issue a total of 11 queries and get the data you want to display.

It's not a big deal with about 11 cases, but it's hard if this is 10,000 or 20,000. Assuming that one query takes 0.001 seconds, 10000 or 20000 queries will take 10 or 20 seconds to respond. It's a hassle to wait so far, right? This problem can be solved depending on how you write the code, If you write it properly, even if you have 10,000 Products, you can get it with two queries (laugh)

Methods to solve problems

Now let's see how to solve this problem. Basically, you can use the four methods introduced below. The usage is slightly different, so let's make it possible to use it properly.

Method cache Query Use Data reference
joins do not do INNER JOIN Narrow down it can
eager_load To do LEFT JOIN Cache and refine it can
preload To do SELECT each without JOIN cache Can not
includes To do Depending on the case Cache, narrow down if necessary it can

joins Perform INNER JOIN by default. Use left_joins when you want to perform LEFT OUTER JOIN. This method does not cache, so memory can be kept to a minimum. Also, you can use it when you need only the refined result without referring to the data of the JOIN destination.


User.joins(:products).where(products: { id: 1 })
# SELECT `users`.* FROM `users` INNER JOIN `products` ON `products`.`user_id` = `users`.`id` WHERE `products`.`id` = 1

eager_load The specified association is pulled by LEFT OUTER JOIN and cached. High-speed processing is possible because only one query is required. You can use it when you want to JOIN a one-to-one or N-to-1 association (belongs_to, has_one), or when you want to refer to the information of the table to which you joined (such as narrowing down by Where).


User.eager_load(:products)
# SELECT `users`.`id`, `users`.`name`, `users`.`created_at`, `users`.`updated_at`, `products`.`id`, `products`.`user_id`, `products`.`created_at`, `products`.`updated_at` FROM `users` LEFT OUTER JOIN `products` ON `products`.`user_id` = `users`.`id`

preload The specified association is divided into multiple queries and cached. However, it is not possible to refer to the data of the association destination (such as narrowing down by Where). Use it for many-to-many associations. As a caveat, if the amount of data is large, the IN clause tends to be large, which may put pressure on the memory.


User.preload(:products)
# SELECT `users`.* FROM `users`
# SELECT `products`.* FROM `products` WHERE `products`.`user_id` IN (1, 2, 3, ...)

includes Simply put, it uses eager_load and preload properly. However, it seems better not to use includes. Because includes sorts preload and eager_load nicely. If you understand the characteristics of preload and eager_load, you will rarely see includes. It may not be a problem if you include it while the data is small, but as the data increases, wrinkles and problems become apparent, so be sure to know the behavior of includes correctly.


User.includes(:products)
# SELECT `users`.* FROM `users`
# SELECT `products`.* FROM `products` WHERE `products`.`user_id` IN (1, 2, 3, ...)

Master the Bullet gem and detect N + 1 problems early

No matter how careful you are, human beings will come out. Bullet is a gem that covers that.

How to use Bullet

  1. Add the following to the Gemfile.

group :optimization do
  gem 'bullet', '~> 6.1.0'
end
  1. With the execution environment as optimization, copy development.rb and describe the following.

config/enviroments/optomization.rb




config.after_initialize do
  #Allows optimization.
  Bullet.enable = true

  #It is allowed to display the problem with JS Alert.
  Bullet.alert = true

  #Allows logging to a file.
  Bullet.bullet_logger = true

  #N on the browser console+1 Allows you to view the problem.
  Bullet.console = true

  #Allows Bullet to log in Rails.
  Bullet.rails_logger = true

  #Allows the problem to be displayed in the footer.
  Bullet.add_footer = true
end
  1. Add the following to webpacker.yml.

webpacker.yml



optimization:
  <<: *development
  1. Run and check for N + 1 problems.

$ bundle exec rails server -e optimization

How to set Bullet in a test environment

  1. Add the following to test.rb.

config/enviroments/test.rb



config.after_initialize do
  Bullet.enable = true
  Bullet.bullet_logger = true
  Bullet.raise = false
end
  1. Add the following to spec_helper.rb.

spec/spec_helper.rb



if Bullet.enable?
  config.before(:each) do
    Bullet.start_request
  end

  config.after(:each) do
    Bullet.perform_out_of_channel_notifications if Bullet.notification?
    Bullet.end_request
  end
end

At the end

So far we have seen how to deal with the N + 1 problem. I think that the response will be much faster just by changing a little consciousness! Now, let's consciously develop from this time! !! !!

reference

[Discover N + 1 query problems hidden in Ruby on Rails code with Bullet gem and optimize Rails site response](https://www.123ish.com/jp/entries/2236-ruby-on- rails% E3% 81% AE% E3% 82% B3% E3% 83% BC% E3% 83% 89% E3% 81% AB% E6% BD% 9C% E3% 82% 80n-1% E3% 82% AF% E3% 82% A8% E3% 83% AA% E5% 95% 8F% E9% A1% 8C% E3% 82% 92bullet-gem-% E3% 81% A7% E7% 99% BA% E8% A6 % 8B% E3% 81% 97% E3% 81% A6% E3% 80% 81rail% E3% 82% B5% E3% 82% A4% E3% 83% 88% E3% 81% AE% E3% 83% AC % E3% 82% B9% E3% 83% 9D% E3% 83% B3% E3% 82% B9% E3% 82% 92% E6% 9C% 80% E9% 81% A9% E5% 8C% 96)

Run Bullet in test environment

Reason for using preload and eager_load properly without using ActiveRecord includes

Personal use of ActiveRecord includes, preload, eager_load

Differences between ActiveRecord joins, preload, includes and eager_load

Recommended Posts

Don't underestimate the N + 1 problem!
[N + 1 problem]
[Beginner] Discover the N + 1 problem! How to use Bullet
Solve the N + 1 problem with Ruby on Rails: acts-as-taggable-on
I put in a gem bullet (for those who don't notice the N + 1 problem themselves)
Explanation of the FizzBuzz problem
Let's solve the FizzBuzz problem!
I tried the FizzBuzz problem
A memorandum of the FizzBuzz problem
Expression used in the fizz_buzz problem
Strict_loading function to suppress the occurrence of N + 1 problem added from rails 6.1