Ruby on Rails Method ActiveRecord Query Optimizations

Question

So I have three methods and I'm not sure how to optimize them. I will be working on this and updating this post while I do but basically running these three methods is central to some parts of my application and they are causing a great deal of slowdown. I just have too many queries being ran because of them. If I could get some optimization help, I would appreciate it!

def self.appropriate_users(current_user)
    unless current_user.organization.nil?
      if current_user.has_role? :director
        @users = User.where(organization: current_user.organization)
      elsif current_user.has_role? :supervisor
        @users = current_user.subordinates
      elsif current_user.has_role? :leader
        @users = []
        groups = current_user.leading_groups.each do |g|
          g.users.each do |u|
            @users << u
          end
        end

        @users.uniq
      else
        @users = User.none
      end
    else
      @users = User.all if current_user.has_role? :admin
    end
    @users ||= []

    @users
  end

  def expired_trainings(current_user)
    retval = []
    Organization.appropriate_users(current_user).each do |user|
      Expiration.where("user_id = #{user.id} and expire_on <= '#{Date.today}'").each do |expir|
        retval << expir
      end
    end
    ttypes = []
    if current_user.max_role.downcase == 'leader'
      current_user.leading_groups.each do |g|
        g.training_types.each do |tt|
          ttypes << tt
        end
      end
      ttypes.flatten.uniq!
      retval.delete_if { |expir| !ttypes.include?(expir.training_type) }
    end
    retval.flatten
  end

  def current_trainings(current_user)
    retval = []
    Organization.appropriate_users(current_user).each do |user|
      Expiration.where("user_id = #{user.id} and expire_on >= '#{Date.today}' and expire_on <= '#{Date.today + 30.days}'").each do |expir|
        retval << expir
      end
    end
    ttypes = []
    if current_user.max_role.downcase == 'leader'
      current_user.leading_groups.each do |g|
        g.training_types.each do |tt|
          ttypes << tt
        end
      end
      ttypes.flatten.uniq!
      retval.delete_if { |expir| !ttypes.include?(expir.training_type) }
    end
    retval.flatten
  end

  def future_trainings(current_user)
    retval = []
    Organization.appropriate_users(current_user).each do |user|
      Expiration.where("user_id = #{user.id} and expire_on >= '#{Date.today + 31.days}'").each do |expir|
        retval << expir
      end
    end
    ttypes = []
    if current_user.max_role.downcase == 'leader'
      current_user.leading_groups.each do |g|
        g.training_types.each do |tt|
          ttypes << tt
        end
      end
      ttypes.flatten.uniq!
      retval.delete_if { |expir| !ttypes.include?(expir.training_type) }
    end
    retval.flatten
  end

Answer 1

You could benchmark and see if a INNER JOIN would be faster on the Expiration model w/ the User model. I'm assuming they are not one to one, since you are grabbing multiple expirations per user.

How big is the Users table? If it's relatively small, and not growing exponentially you might not need to use a join.

If it has millions of records, you are essentially doing a million SELECTs:

Benchmark.realtime  { User.all.each { |x| Expiration.where(:user_id => x.id) }

Which can be bad for performance and cause your MySQL server to be overloaded.

There is also something to look out for is the N + 1 problem, which is a very similar problem to what might be happening to you:

https://www.codemy.net/posts/optimizing-your-rails-app-part-1-n-1-queries

Edit:

N+1 queries occur when you write a piece of code that executes a SQL query many times (N times depending on the code), where one query would have been appropriate. It occurs commonly in loops, maps, and places where lazy loading is not enabled. It's very common in views that display tabular info that uses other tables, and is not noticeable in a development environment where you're not testing a ton of data.