Fix N+1 Queries Without Eager Loading Using SQL Subqueries

I’ll start with a confession: I’m still quite new to the world of performance analysis and optimization.

Monitoring how Rails apps behave in the real world, especially when it comes to database queries and SQL performance is something I’ve only recently started paying attention to after a deep dive on a recent client project, and I’ve been really enjoying it so far. But it’s a rabbit hole I’ve just begun to explore, and the deeper I go, the more I realize how much I don’t know.

If you spot any gaps in my understanding or obvious mistakes in how I’ve profiled or interpreted the data, I’d really appreciate your feedback. I’m sharing this as someone who’s learning in public, hoping to get better with each post.

Fetching a single record from a has_many association is a common task in most applications. Whether you're retrieving the latest comment on a post, the most recent order for a customer, or the latest activity for a user, this pattern shows up frequently in everyday programming.

However, it can become a silent performance bottleneck—especially when each parent record has many associated records. The typical solution of using includes for eager loading helps avoid N+1 queries, but it often comes at the cost of increased memory usage, since it loads ALL associated records into memory, even if you're only interested in one of them.

In this post, we’ll look at a more efficient alternative using SQL subqueries. Instead of eager loading every associated record, we’ll fetch just the specific record you need, like the most recent one, directly within your main query.

This solution avoids N+1 query problem, avoids unnecessary memory consumption and keeps your SQL queries fast. This post will walk you through a real-world example, and show how to use a subquery for better performance.

We’re continuing from where we left off in the last post. If you want to follow from the start, check out the previous two posts:

• Reduce Memory Usage of Your Rails App by Selecting Specific Columns
• Profiling Ruby on Rails Applications with Rails Debugbar

  
  

That said, you don't have to. Our starting point is a standard Rails index page that shows a list of 500 members at the /members endpoint (without pagination). Each row lists the member’s name and email. That's it.

Here's the new requirement: to add a third column showing each member’s latest activity timestamp, so the management can see the most active members.

This simple request presents some interesting challenges, especially as the number of associated records grows. There're a few different ways to solve it, each with different (speed and memory) performance characteristics. We’ll walk through each approach and compare them.

Let’s start by setting up the necessary data: we’ll update our schema to track member activities and seed the database with realistic data to test performance.

Tracking Member Activities

We’ll track member activity in a new table, where each row represents an action taken by a member and when it happened. This gives us the data we need to display their most recent activity later.

Create the Migration

Let's write a Rails migration to create this table.

$ rails generate migration CreateActivities action:string member:references

$ rails db:migrate

This will create an activities table with an action (like "created_task" or "commented") and a foreign key reference to the member.

Add the Associations

Next, let's create the Activity model and add the related associations.

• An activity belongs to a member.

class Activity < ApplicationRecord
  belongs_to :member
end

• A member has many activities.

class Member < ApplicationRecord
  belongs_to :team
  has_many :activities, dependent: :destroy
end

Seed the Database with Sample Data

We’ll create a few teams, each with 50 members, and generate 10 activities for every member. This will give us around 5,000 activity records to work with—enough to surface real performance issues.

require "faker"

10.times do
  team = Team.create(name: Faker::Company.name)
end

Team.find_each do |team|
  50.times do
    member = Member.create(
      name: Faker::Name.name,
      email: Faker::Internet.email,
      team_id: team.id
    )

    # Create 10 activities for each member
    10.times do
      Activity.create!(
        member: member,
        action: %w[created_task commented uploaded_file].sample,
        created_at: Faker::Time.between(from: 1.year.ago, to: Time.current)
      )
    end
  end
end

Finally, reset the database so it's ready with the sample data.

$ bin/rails db:reset

Now that we’ve seeded the data, we’re ready to update the /members page to show each member’s latest activity timestamp as a third column.

The Simplest Solution that Works: But at a Cost

Let's start with the simplest possible implementation: show each member's latest activity timestamp as a third column. We'll do this by querying the activities association for each member, sorted in descending order by their creation date, and picking the latest activity.

Let's add a latest_activity method on the Member model which will calculate and return the latest activity in a naive way.

class Member < ApplicationRecord
  belongs_to :team
  has_many :activities, dependent: :destroy

  def latest_activity
    activities.order(created_at: :desc).first
  end
end

Here's the Members controller class which fetches the members.

class MembersController < ApplicationController
  def index
    @members = Member.select(:id, :name, :email)
  end
end

Finally, let's use the latest_activity method on the members index page. We’re rendering a third column with the latest activity timestamp.

<div class="container mx-auto px-4 py-8">
  <h1 class="text-2xl font-bold mb-6">Members</h1>

  <div class="overflow-x-auto bg-white rounded-lg shadow">
    <table class="min-w-full divide-y divide-gray-200">
      <thead class="bg-gray-50">
        <tr>
          ...
          <th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
            Latest Activity
          </th>
        </tr>
      </thead>
      <tbody class="bg-white divide-y divide-gray-200">
        <% @members.each do |member| %>
          <tr class="hover:bg-gray-50">
            ...
            <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">
              <%= time_ago_in_words(member.latest_activity.created_at) %> ago
            </td>
          </tr>
        <% end %>
      </tbody>
    </table>
  </div>
</div>

This works as expected. It loads the members and shows their most recent activity in human-readable format like “3 minutes ago” or “2 days ago.”

However, if you look at the Mini Profiler and expand it, you'll notice a little problem. To load the 500 members, we're running 501 SQL queries which take about 203 ms. Also, the page got slower. On my machine, it takes around 1.5 seconds on average to render the page.

Why? That’s because for every member, Rails runs a separate query to get their latest activity. As you can see in the screenshot below, we're running one query to fetch all members, and one query for each member to fetch their activities.

We've just created an N+1 problem.

Memory Profile

Let's inspect the memory by reloading the page with the additional query params:

http://localhost:3000/members?pp=profile-memory&memory_profiler_allow_files=app

It reports around 3.8 KB memory and 49 objects allocated. Not bad, actually.

💡Just to be clear, this simple solution is totally fine if you're only dealing with a handful of records. If you’re paginating and showing 10 or 20 members on a page, you probably won’t notice any performance hit at all. Rails makes it easy to reach for associations, and sometimes that’s all you need.

But once the number of records starts to grow, the cracks begin to show. What worked fine in development can quietly become a bottleneck in production.

Let's see if we can reduce the number of SQL queries.

Attempt 1: Eager Loading - Better Queries, Worse Memory

Let’s fix the N+1 problem with the traditional solution: Eager-loading the associated records.

In the MembersController, add the .includes(:activities) method to load all activities up front:

class MembersController < ApplicationController
  def index
    @members = Member
                .includes(:activities)
                .select(:id, :name, :email)
  end
end

Next, let's update the latest_activity method slightly. Since activities are now eager-loaded in memory, we can't sort them using the order method, which sorts the records in the database. We'll have to sort them in Ruby using the sort_by method and pick the latest one:

class Member < ApplicationRecord
  belongs_to :team
  has_many :activities, dependent: :destroy

  def latest_activity
    activities.sort_by(&:created_at).last
  end
end

Let's reload the page and inspect the Mini Profiler results.

At first glance, this seems like a win:

• We’ve gone from 501 queries to just 2 — one for members, one for their activities.
• The page now loads in 342 ms, down from 1.5 s, and the queries took 122 ms.

To summarize,

Success? Well...not exactly. Yes, we did fix our N+1 problem. However, we've introduced another, much bigger problem. Let's examine the memory usage and allocations.

Reload the members page with the following URL:

http://localhost:3000/members?pp=profile-memory&memory_profiler_allow_files=app

If you remember, earlier our naive solution used up ~3.8 KB of memory and allocated 49 objects.allocated.

Now that we're eager loading the activities, we are:

• using ~168 KB of memory, which over 44x the memory it took before
• allocating 550 objects, which is 501 objects more than before

Why?

Because we’re loading every activity for every member into memory.

In this example, each member has just 10 activities—but in a real-world app, that number could be in the hundreds or thousands. Eager loading will load every one of them, and Rails will create an ActiveRecord object for each, resulting in hundreds of thousands, or even millions of records being loaded in memory.

That’s a lot of memory and CPU overhead—just to show one value per row.

Agreed, you can mitigate a lot of these issues with techniques like pagination, but in the applications running in memory-constrained environments, handling thousands of concurrent users, or processing large datasets in background jobs - these kinds of inefficiencies add up fast.

To summarize, here're all the metrics before and after:

When Eager Loading Backfires

This is why I find this example interesting: when we see N+1 queries, our first instinct is often to eager load. And most of the time, that is the right move.

But in this case, eager loading might actually could make things worse than simply running extra database queries. You can paginate the number of members you fetch, but Rails will still load all the associated activities for the paginated records — even if you only need one per member.

We’ve fixed the query count. But we’ve quietly created a memory problem that doesn’t scale.

Should we just cache it?

At this point, you might be wondering: why not just cache the latest activity?

For example, by adding a latest_activity_id foreign key on the members table, you can add a has_one :latest_activity association on the Member model. So each member has one latest activity.

class Member < ApplicationRecord
  has_one :latest_activity, class_name: "Activity"
end

Now, every time a member performs an action, you create a new activity and update the member:

activity = member.activities.create!(action: "commented")
member.update!(latest_activity: activity)

This works. And in some cases, this denormalization is exactly what you want—especially if you frequently need the latest activity and don’t mind the overhead of keeping that pointer up to date.

However, caching can get tricky quite fast. You’re also introducing data duplication and increasing the complexity of keeping it accurate over time—especially if activities can be deleted or updated later. As they say:

“There are only two hard things in computer science: cache invalidation, naming things, and off-by-one errors.”

Let’s see if we can avoid the complexity by solving this cleanly with SQL—specifically, using subqueries.

The Better Way: Using a SubQuery

A SQL subquery is just a query inside another SQL query. It is used to perform operations that require multiple steps or complex logic, involving multiple tables.

Some common use cases for subqueries include:

• Filtering records based on data from related tables
• Reducing memory usage by offloading computation to the database
• Conditionally selecting rows without requiring explicit joins or external code logic.

In our case, a subquery gives us a clean way to fetch just the latest activity timestamp for each member as another column, directly in the SELECT clause of our main query.

No eager loading, no sorting in Ruby, no extra memory usage.

Let's give it a try.

Subqueries in Action

Let’s implement the subquery-based solution and see how it performs.

First, update the MembersController. We’ll remove the includes(:activities) eager load and instead embed a subquery directly into the SELECT clause:

class MembersController < ApplicationController
  def index
    @members = Member
                .select(:id, :name, :email,
                        "(SELECT activities.created_at
                         FROM activities
                         WHERE activities.member_id = members.id
                         ORDER BY activities.created_at DESC
                         LIMIT 1) as latest_activity_at")
  end
end

What this query does

This subquery runs once for each member and fetches the created_at value of their most recent activity. It runs entirely in SQL, in the database engine. No Ruby objects, no eager loading, no memory overhead.

In simple terms, it says: “For each member, look up the most recent activity timestamp, and include it as an extra column called latest_activity_at in the result.”

Next, let's update the view to use this new column. We won't need the latest_activity method on the Member model anymore. Just use the new latest_activity_at column added by the subquery.

<%= time_ago_in_words(member.latest_activity_at) %> ago

That's it, we're done. Let's reload the page and check out the results.

After using a subquery, here're the results:

• We're running a single SQL query
• The query took just 6.7 ms to run
• The full page rendered in about 170 ms on average
• Allocated 550 objects
• Using nearly 24 KB of memory

We're now running a single SQL query, which is a huge improvement. Here's the SQL query containing the subquery that is getting executed.

And here's the memory consumption.

It performs better on almost every metric.

To summarize,

The subquery performed better on almost every metric (except memory and number of allocated objects).

Using ActiveRecord Instead of Raw SQL

If you prefer to avoid inline SQL, you can generate the same query using ActiveRecord and .to_sql. Here’s how that looks:

class MembersController < ApplicationController
  def index
    latest_activity_subquery = Activity
                                .select(:created_at)
                                .where('activities.member_id = members.id')
                                .order(created_at: :desc)
                                .limit(1)
                                .to_sql

    @members = Member.select(:id, :name, :email, "(#{latest_activity_subquery}) AS latest_activity_at")
  end
end

Ultimately, it results into the same SQL that's getting executed.

SELECT
	"members"."id",
	"members"."name",
	"members"."email",
	(
		SELECT
			"activities"."created_at"
		FROM
			"activities"
		WHERE (activities.member_id = members.id)
	ORDER BY
		"activities"."created_at" DESC
	LIMIT 1) AS latest_activity_at
FROM
	"members"

Let's walk through what's happening in the subquery:

SELECT activities.created_at
FROM activities
WHERE activities.member_id = members.id
ORDER BY activities.created_at DESC
LIMIT 1

We’re:

1. Looking in the activities table
2. Filtering by member_id to match the current row in the outer members query
3. Sorting by created_at in descending order
4. Grabbing the most recent one (LIMIT 1)
5. And exposing it as a computed column called latest_activity_at

The last column called latest_activity_at is computed on the fly in our subquery. No joins, no eager loading, no extra Ruby objects—just clean SQL, embedded directly into your ActiveRecord query.

The last thing we can do before wrapping up is to extract the subquery into a scope on the Activity model. This keeps the controller clean and makes the intent more obvious.

class Activity < ApplicationRecord
  belongs_to :member

  scope :latest_for_member, -> {
    select(:created_at)
      .where('activities.member_id = members.id')
      .order(created_at: :desc)
      .limit(1)
  }
end

Now the controller becomes much simpler:

class MembersController < ApplicationController
  def index
    @members = Member.select(:id, :name, :email, "(#{Activity.latest_for_member.to_sql}) AS latest_activity_at")
  end
end

I prefer moving complex queries from my controllers to my models. This is easier to read and easier to test. Plus, if you ever need to reuse this subquery elsewhere, you now have a well-named scope ready to go.

Isn't this N+1 Query Again?

Now, when I first came across subqueries, my first thought was - aren't we just running multiple queries in the database? That is, we are running a query to find the latest activity query for each member. So have we not simply moved our N+1 query problem from ActiveRecord to the database layer?

Well, the answer is quite involved, and to be honest, I am not sure I understand it fully.

Logically, we are running multiple queries in the database to get all members and individual queries to fetch the latest activity. But it's not like Rails is sending multiple queries to the database. Also, this is exactly the kind of work databases are designed to do—and they’re incredibly good at it. Query planners, indexes, and tight memory management all make subqueries like this very efficient, even at scale.

💡 If you want to see exactly what the database will do, you can prefix the query with EXPLAIN and the database will tell you how it will process it (I'm using PostgreSQL).

More importantly, from Rails’ point of view, we’re now making just one database call. That means one network round-trip, far less memory usage, and no extra Ruby objects being instantiated.

The end result is so much better performance.