Rails + MySQL - loading data from 3 tables with a specific format (very slow "has_many" relation)

Question

I have this structure of tables:

users

id | name
1  | John
2  | Peter
3  | Claire

services

id | name
1  | home
2  | garden
3  | music

user_services

id | user_id | services_id
1  | 1       | 3
2  | 2       | 3
3  | 1       | 2

Models: user.rb

class User < ActiveRecord::Base
  has_many :user_services, dependent: :destroy
  has_many :services, through: :user_services
end

user_service.rb

class UserService < ActiveRecord::Base
  belongs_to :user
  belongs_to :service
  validates_uniqueness_of :service_id, scope: :user_id
end

service.rb

class Service < ActiveRecord::Base
  has_many :user_services
  has_many :users, through: :user_services
end

And here's what I do (controller):

def do_search
  @results = search_users
  @users_a = []
  @users_b = []

  @results[:users].each do |result|
    is_user_b = 0
    result.user_services.each do |service|
      if service.service_id == 28
        @users_a << "a string..."
        is_user_b = 1
        break
      end
    end
    if is_user_b == 0
      @users_b << "string B"
    end
  end
  render "results_users"
end

def search_users
  @users = User.within(distance, origin: city).where('gender = ?', 0).order("distance ASC")
end

and in the view:

<% @results[:users].each do |result| %>
  <li data-services="<% result.services.each do |service| %><%= service.name %>,<% end %>">
    <div><%= result.facility_name %></div>
    <ul class='service-icons cf'>
      <% result.services.each do |service| %>
        <li class='service-icon'>
          <span class="streamline" aria-hidden="true" data-icon="<%= raw service.icon %>" title="<%= service.label %>"></span>
        </li>
      <% end %>
    </ul>
  </li>
<% end %>

The problem: this flow is very slow - the SQL query is quite fast (0.4s), but processing those 3 loops (1 in controller and 2 in the view) is very slow and it causes that displaying results lasts about 60 seconds.

It surprises me, because I have nearly the same model, but with cars (cars, services, car_services), almost the same amount of attributes (cars) as in users, in cars is 600k records, in users 90K and it lasts only 2 seconds to get it done. I spent the whole day trying to figure out why, but still don't understand how is it possible.

Could anyone help me, please, why looping through the has_many association takes so much time? (if I comment all the 3 loops, the page is loaded in 2 seconds)

Thank you in advance for your time.

Answer 1

Actuallly Active Record is really bad at handling large datasets.

However first thing you should do is avoiding the massive amount of n+1 queries the Code does within those loops by eagerloading the associated records, which will be accessed within the iterations

 User.includes(:user_services, :services).within(...).where(...)

It's of magnitude faster to fetch all the records at once, than issuing a separate +1 query per loop Iteration.

You should also consider introducing pagination or some other User initiated additional fetching (e.g. "Show More")for the Users and perhaps their services. Humans will struggle to process large amounts of data anyway.

If this optimazation is unsufficient and the requirement to fetch all the data at once is unnegotiable, then go with raw SQL. The impact of instantiating an ActiveRecord Objectgraph for every row is just huge. We've been able to optimize our billing reports from minutes down to seconds by this.

I also recommend the 'rack-mini-profiler' gem for analyzing performance issues.

Answer 2

It is slow because you have what is known as the N+1 problem. The solution is to change your query to something like:

@users = User.within(distance, origin: city).where('gender = ?', 0).includes(:facility,:services).order("distance ASC")

Lots more info in the Rails guide. I highly recommend integrating the bullet gem which will immediately notify if you forget an includes or include an unnecessary one.