Refactoring Nested Queries (n+1) to Improve Performance

The most significant bottleneck in web applications typically lies within the databases. Therefore, the goal is to minimize the number of queries made, especially inside loops, as much as possible.

There are exceptions though, like running a one-time script to clean some data.

Here's an example that I'm working on:

I have a User model that has_many attendances.

Consider this first iteration that I've written.

I'm pre-loading attendances for all Users which sounds nice. However, inside of the map, which basically loops inside of the data, I have another map that generates week days.

So imagine if I have 5 users. The first query `user.attendances.find_by` runs 5x5 times. Now we have three of them.

dates = (Date.current.beginning_of_week..Date.current.end_of_week(:saturday)).to_a

@users = User.all.includes(:attendances).map do |user|
  {
    employee: user,
    dates: dates.map do |date|
      {
        date: date,
        present: user.attendances.find_by(date: date).presence,
        icon: user.attendances.find_by(date: date).presence ? 'check' : 'times',
        colour: user.attendances.find_by(date: date).presence ? 'text-green-500' : 'text-red-500'
      }
    end
  }
end

Now an immediate improvement can be reducing the number of queries to one and then calling the variable. Nevertheless, we are still querying inside of a loop.

@users = User.includes(:attendances).all.map do |user|
  {
    employee: user,
    dates: dates.map do |date|
      attendance = user.attendances.find_by(date: date)
      present = attendance.present? ? 'check' : 'times'
      colour = attendance.present? ? 'text-green-500' : 'text-red-500'
      {
        date: date,
        present: attendance.presence,
        icon: present,
        colour: colour
      }
    end
  }
end

To optimise this part of the application, one solution is to get the data and load the associations first-hand, then use an array of dictionaries or hashes to keep the indexes.

You can then refer directly to the hash key in the array to retrieve the data. In this case, we have the key user.id and we use the Rails utility method index_by to index by date.

Here's an example:

@users = User.includes(:attendances).all
attendances_by_user_id = {}

@users.each do |user|
  attendances_by_user_id[user.id] = user.attendances.index_by(&:date)
end

# Construct the data structure
@users = @users.map do |user|
  {
    employee: user,
    dates: dates.map do |date|
      attendance = attendances_by_user_id[user.id][date]
      present = attendance.present? ? 'check' : 'times'
      colour = attendance.present? ? 'text-green-500' : 'text-red-500'
      {
        date: date,
        present: attendance.presence,
        icon: present,
        colour: colour
      }
    end
  }
end

A list of users is returned containing:

• date: week days from Monday to Friday
• present: boolean
• icon: the font-awesome icon to display
• colour: red or green