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! !!
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)
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, ...)
No matter how careful you are, human beings will come out. Bullet is a gem that covers that.
group :optimization do
gem 'bullet', '~> 6.1.0'
end
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
webpacker.yml
optimization:
<<: *development
$ bundle exec rails server -e optimization
config/enviroments/test.rb
config.after_initialize do
Bullet.enable = true
Bullet.bullet_logger = true
Bullet.raise = false
end
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
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! !! !!
[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