[RUBY] [Rails] Suppress unnecessary SQL by utilizing the cache control of the association

Active Record has a powerful feature called associations. In this article, I have summarized how to use the cache control of associations to suppress unnecessary SQL.

What is association cache control?

It is described as follows in the Rails guide. [Active Record Association-3.1 Cache Control](https://railsguides.jp/association_basics.html#%E3%82%AD%E3%83%A3%E3%83%83%E3%82%B7%E3% 83% A5% E5% 88% B6% E5% BE% A1)

The result of the last executed query is cached and can be used in subsequent operations.

Actually move it and check if it is cached. Use the model below.

class User < ApplicationRecord
  has_many :reviews
end

class Review < ApplicationRecord
  belongs_to :user
end

When ʻuser.reviews` is executed as shown below, SQL is not issued in the second execution. This is because ActiveRecord caches the result of the first execution and returns it.

irb(main):004:0> user = User.first
  User Load (5.8ms)  SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
=> #<User:0x000056307ae3aca0

irb(main):005:0> user.reviews
  Review Load (2.7ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`user_id` = 1
=> [#<Review:0x000056307b1a77d0

irb(main):007:0> user.reviews
=> [#<Review:0x000056307b1a77d0

In this example, you can see that it takes 2.7ms to execute the SQL to get the reviews. Since the cache is used the second time, it means that the same process saves 2.7ms.

There is not much benefit if only one SQL issue is shortened like this time, If this is accumulated 100 times and 1,000 times, it will be a difference of a few seconds, and you will be able to feel the difference.

At the time of acquisition

Use the same model as in the previous example. If you have already got the user object, how do you get the reviews that the user has?

#User object acquired
user

#Get the reviews that user has
# 1
reviews = Review.where(user_id: user.id)

# 2
reviews = Review.where(user: user)

# 3
reviews = user.reviews

The SQL issued when retrieving reviews is the same for 1-3, but there is one difference. The difference is whether the review related data ʻuser is cached. Get reviews by the above methods 1 to 3 with irb and check if the related data ʻuser of reviews is cached.

#1

irb(main):009:0> reviews = Review.where(user_id: user.id)
  Review Load (1.2ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`user_id` = 1
=> [#<Review:0x000055fc149bca10

irb(main):010:0> reviews.first.user
  User Load (0.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
=> #<User:0x000055fc14b75d20

#2

irb(main):011:0> reviews = Review.where(user: user)
  Review Load (0.8ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`user_id` = 1
=> [#<Review:0x000055fc14b83ab0

irb(main):012:0> reviews.first.user
  User Load (0.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
=> #<User:0x000055fc14fca880

#3

irb(main):015:0> reviews = user.reviews
  Review Load (0.6ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`user_id` = 1
=> [#<Review:0x000055fc1504c0b0

irb(main):016:0> reviews.first.user
=> #<User:0x000055fc14873f78

Only in case of 3, the SQL to get user is not issued when executing reviews.first.user. It seems that the original related data is cached when it is acquired by the association.

Before actually checking the operation, I expected that the related data would be cached because the method of passing the object to where is also passing the object like 2, but it is implemented like that. It doesn't seem to be.

When acquiring related data, it is better to acquire it using the association because the cache can be utilized, so let's actively use it.

At the time of creation

Use the same model as in the previous example. If you have already retrieved the user object and want to add a review to that user, how do you create it?

#User object acquired
user

#Create a new review for user
# 1
review = Review.create!(content: 'hogehoge', user_id: user.id)

# 2
review = Review.create!(content: 'hogehoge', user: user)

# 3
review = user.reviews.create!(content: 'hogehoge')

The SQL of Insert is the same for 1-3, but there is a difference between the SQL before creation and the cache state after creation. Let's check with irb.

#1

#Number of reviews before creation
irb(main):051:0> user.reviews.size
=> 19

#User has been selected before creation!
irb(main):054:0> review = Review.create!(content: 'hogehoge', user_id: user.id)
   (0.4ms)  BEGIN
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Review Create (0.7ms)  INSERT INTO `reviews` (`content`, `user_id`, `created_at`, `updated_at`) VALUES ('hogehoge', 1, '2020-06-15 14:46:13.461715', '2020-06-15 14:46:13.461715')
   (2.0ms)  COMMIT
=> #<Review:0x000055fc16072188
 id: 20,

#The user is cached in the returned review
#→ Is the user acquired at the time of creation to do this cache?
irb(main):055:0> review.user
=> #<User:0x000055fc16076c60

#Original user.The number of reviews has not increased because it is the cache before creation
irb(main):056:0> user.reviews.size
=> 19
#Reload required to update
irb(main):057:0> user.reviews.reload.size
  Review Load (0.9ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`user_id` = 1
=> 20

#2

#Number of reviews before creation
irb(main):058:0> user.reviews.size
=> 20

#No select before creation
irb(main):059:0> review = Review.create!(content: 'hogehoge', user: user)
   (0.4ms)  BEGIN
  Review Create (0.6ms)  INSERT INTO `reviews` (`content`, `user_id`, `created_at`, `updated_at`) VALUES ('hogehoge', 1, '2020-06-15 14:53:06.510290', '2020-06-15 14:53:06.510290')
   (3.4ms)  COMMIT
=> #<Review:0x000055fc16fa6690
 id: 21,

#The user is cached in the returned review
#→ It seems that the user object passed to create is cached
irb(main):060:0> review.user
=> #<User:0x000055fc16b28b40
 id: 1,

#Original user.The number of reviews has not increased because it is the cache before creation
irb(main):061:0> user.reviews.size
=> 20
#Reload required to update
irb(main):062:0> user.reviews.reload.size
  Review Load (0.8ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`user_id` = 1
=> 21

#3

#Number of reviews before creation
irb(main):063:0> user.reviews.size
=> 21

#No select before creation
irb(main):064:0> review = user.reviews.create!(content: 'hogehoge')
   (0.6ms)  BEGIN
  Review Create (0.6ms)  INSERT INTO `reviews` (`content`, `user_id`, `created_at`, `updated_at`) VALUES ('hogehoge', 1, '2020-06-15 14:55:45.393655', '2020-06-15 14:55:45.393655')
   (1.8ms)  COMMIT
=> #<Review:0x000055fc15fd6120
 id: 22,

#The user is cached in the returned review
irb(main):065:0> review.user
=> #<User:0x000055fc16b28b40
 id: 1,

# user.Also added to reviews
irb(main):066:0> user.reviews.size
=> 22

In all patterns, the review object returned by create cached the user object. However, in the case of 1, the select statement has been issued before creation. If you have an association object, it seems more efficient to pass the object. Also, only in case of 3, the created review is added to ʻuser.reviews`.

When updating related data, it is better to use the association because it is added to the original data and can be handled more efficiently. Even if you do not use the association, it seems to be more efficient to pass the association as an object as in 2, because the select statement is not issued uselessly.

Finally

I thought I knew about the cache to some extent, but I hadn't noticed the behavior that select is issued before the 1st and 2nd create written in [At the time of creation] ... It seems that there are many things that you haven't noticed yet unless you consciously try it.