N+1 Problem - Database Query

Oct 20, 2016

When your database slow, what will you do?

Have you heard about N+1 problems in ActiveRecord? When we make associations in ActiveRecord and call the associations like @article.comments.count, it hits the database unnecessarily more than you may wish.

#deal.rb has_many :experts #deals_controller.rb def index @deals = Deal.all end #index.html.erb <% @deals.each do |d| %> <% d.experts.each do |e| %> <%= e.first_name %> <% end %> <% end %>

This operation hit total deals number * experts number to just render each expert's name. Instead of calling database everytime when we look up the expert.first_name, we can 'preload' experts when we load deal.

Preload

@deals = Deal.preload(:experts)

This will preload all experts instance associated with the deals. I actually had to limit(3) because the data was too big so I wasn't able to trace.

Preload will actually show 'join' record. So it doesn't have to go back to database every instance. There is another way to solve this problem - eager_loading. eager_load is much popular because it allows using the 'select' statement.

Eater Load

@deals = Deal.eager_load(:experts).where(experts {name: 'Sarah'}).limit(10)

Eager loading is actually using LEFT OUTER JOIN. In terms of performance, eger_load nad preload are pretty similar. So which one to use?

Includes

Ya know that rails is pretty smart. They knew all rails developers are wondering about which method to use, they made '.includes' method.

@deals = Deal.includes(:experts).where(experts {name: 'Sarah'}).limit(10)

Includes is flexible so it can be either eager_load or preload. If you are not use '.where' at the end of the query, it will use preload otherwise it will use eager_load. This is pretty cool, huh?

Count - Customized database query

Let's say you want to show how many experts in individual expert_category.

# expert_category.rb has_many :expert expert_type = ExpertCategory.create(description: 'Developer') # expert.rb belongs_to :expert_category expert = Expert.create(first_name: 'sarah', expert_category: 1)

In order to render experts number by expert_category, we will do operation like this:

<% @expert_category = ExpertCategory.all %> <% @expert_category.each do |ec| %> <%= ec.experts.count %> <% end %>

In this case, unfortunately, includes or eager_load do not make the query faster. Becuase it is separate query. Although expert_category and experts are loaded(or preloaded), the count operation need to be done separately. In this case we can make hash of the each count by category_id by using 'group' method.

@experts_category_counts = Expert.group("experts.expert_category_id").count => { 2 => 60, 1 => 53, 7 => 67, 5 => 208 } <% @experts_category = ExpertCategory.all %> <% @experts_category.each do |ec| %> <% ec.experts.each do |ex| %> <%= ex.first_name + @experts_category_counts[ex.id] %> <% end %> <% end %>