Advanced Query Optimization

What are N+1 Queries

N+1 queries are a common performance issue when querying related data. A typical example would be a page showing a lits of posts, and the associated comments for those posts.

The default query structure would involve one query for the group of posts, and then n, where n is the number of posts, queries for comments. The problem with a situation like this is that your page will perform worse and worse as your data set grows.

The majority of the time, it is much more efficient to make 2 or 3 larger queries, batching up similar queries, than to query multiple times as would be the case with N+1s.

Simplified Practice Page

For this example, we'll be working with a simplified version of the [Practice page on Upcase][]. The code of interest is in the #index action and its query for trails:

def index
  @trails = Trail.joins(:topic).order("topics.name")
end

[Practice page on Upcase]: https://upcase.com/practice

Tooling for Query Analysis

There are number of tools available for analyzing queries and database performance. Two particular tools used on Upcase are:

• [Rack Mini Profile][] - Analyzes query and view rendering, displays a nice little widget in development
• [Bullet gem][] - Tracks down and reports N+1 queries found in development.

While both are great tools, they won't catch everything. In the end, your logs are often the most trustworthy source of information about how many queries are being made for a given request.

One caveat on using the logs is that they have to readable. By default, Rails will log every asset request in development which is extremely noisy and can make it hard to understand the actual queries and view rendering. To silence this noise, add the [quiet-assets gem][] to your development group for a better tomorrow!

Similarly, if you're tailing the logs, they are just lines of text which the command line is super good at manipulating. Want to see all the SELECT statements? Then pipe it through grep:

$ tail -f log/development.log | grep SELECT

Now we'll have a nice focused stream of just the SELECT statements which we can focus in on.

[Rack Mini Profile]: https://github.com/MiniProfiler/rack-mini-profiler [Bullet gem]: https://github.com/flyerhzm/bullet [quiet-assets gem]: https://github.com/evrone/quiet_assets

Eager Loading - Our First Line of Defense

Based on a quick review of the logs, it's clear that we're making multiple queries for the topics relation. This is one of the easiest query problems to fix, using an eager load of the relation.

Eager Loading with Includes

@trails = Trail.includes(:topics).joins(:topic).order("topics.name")

The includes(:topics) instructs Rails to load the topcis once at the outset, but then we don't need to query topics again after that. This is the sort of thing that the [Bullet gem][] would indeed find, and why it's likely a good idea to just have that running in the background.

Eager Loading Polymorphic Relations

Similar to the topics N+1, we have an N+1 for the steps in each of the trails, as well as the video or exercise associated with that step.

The simplest solution would be to eager load steps just like we did with topics:

@trails = Trail.includes(:topics, :steps).joins(:topic).order("topics.name")

This works for the steps, but we want to go even further and optimize the queries for the dependent video and exercise records:

@trails = Trail.
  includes(:topics, steps: [:video, :exercise]).
  joins(:topic).
  order("topics.name")

This, unfortunately, crashes the app as steps don't have videos or exercises, but instead have the polymorphic completeable representing either. For this case, we can use an alternative eager loading syntax:

@trails = Trail.
  preload(steps: :completeable).
  includes(:topic).
  joins(:topic).
  order("topics.name")

Here we add preload(steps: :completeable) and Rails will properly eager load, taking into account the polymorphic nature of the completeable relation.

Contrasting the Eager Loading Strategies

• includes - works for anything that is not polymorphic, and it allows you reference something from the query in the association.
• preload - works with polymorphic associations, but doesn't allow you to reference something in the query.

More Complex N+1s

Thus far we've been able to solve all of our querying woes with simple eager loading, but we were cheating a bit and ignoring the fact that the /practice page actually renders each step taking into account the user's status on that step. This sort of parameterized query can not be solved with the ActiveRecord querying tactics shown above. We have to go deeper.

<section class="progress">
  <% trail.steps.each do |step| %>
    <%= render(
      "trails/step",
      completeable: step.completeable,
      state: step.state_for(current_user)
    ) %>
  <% end %>
</section>

In the above snippet from the trail partial, we iterate over the steps, passing in the state for the current user. This parameterized style of query makes it difficult for Rails to eager load around.

Extracting a Query Object

As a first step, we can extract a new class to wrap up our query logic. Typically this style of class is called a "query object". We'll name ours StatusFinder. It's job will be to intelligently preload the needed objects based the known context (the current_user and the completeables).

class StatusFinder
  def initialize(user, completeables)
    @user = user
    @completeables = completeables
  end

  def state_for(completeable)
    statuses.detect do |status|
      status.completeable_id == completeable.id &&
        status.completeable_type == completeable.class.name
    end.try(:state) || Status::UNSTARTED
  end

  private

  def statuses
    @statuses ||= @user.
      statuses.
      where(completeable_id: @completeables.map(&:id)).
      order(:created_at)
  end
end

This query object exposes a state_for method that takes a specific completable, and returns the status for the user by finding the needed status in the cache of statuses. We can provide this to the view by building a StatusFinder instance in the controller:

def index
  @trails = Trail.
    preload(steps: [:completeable]).
    includes(:topic).
    order("topics.name")

  @status_finder = StatusFinder.new(
    current_user,
    @trails.flat_map(&:completeable)
  )
end

And we can use this initialized @status_finder in our trail partial to query for the state:

<section class="progress">
  <% trail.steps.each do |step| %>
    <%= render(
      "trails/step",
      completeable: step.completeable,
      state: @status_finder.state_for(step.completeable)
    ) %>
  <% end %>
</section>

And voila, our query problems are solved! We are now making all our queries from a pre-build cache, fully aware of the current_user and the completeables of interest.

Decorate Like It's Hot

With the query performance optimized, we can now turn our eye to refactoring our optimized code. One unfortunate side effect of this optimization is that now we have this @status_finder object that we need to pass around and reference. Wouldn't it be great if instead of:

state: @status_finder.state_for(step.completeable)

we could simply use this?

state: step.state

We can support this by decorating the trails and steps to produce a StatusFinder aware version of the methods, but allow our view code to be blissfully unaware of the StatusFinder.

To start, we'll update controller action to decorate the trails:

def index
  trails = Trail.
    preload(steps: [:completeable]).
    includes(:topic).
    order("topics.name")

  status_finder = StatusFinder.new(
    current_user,
    trails.flat_map(&:completeable)
  )

  @trails = trails.map { |trail| TrailWithState.new(trail, status_finder) }
end

Now we need to define the TrailWithState decorator, and nested StepWithState decorator as well:

class TrailWithState < SimpleDelegator
  def initialize(trail, status_finder)
    super(trail)
    @status_finder = status_finder
  end

  def steps
    super.map do |step|
      StepWithState.new(step, @status_finder)
    end
  end

  class StepWithState < SimpleDelegator
    def initialize(step, status_finder)
      super(step)
      @status_finder = status_finder
    end

    def state
      @status_finder.state_for(completeable)
    end
  end
end

To break this down, we're using [SimpleDelegator][], which ships with Ruby, to build our decorator. We initialize our decorator instances with a trail instance, and SimpleDelegator will then forward on all methods to that trail object. The one expectation is the steps method which we implement directly to provide our custom behavior.

In the steps method, we call super to get the steps from the decorated trail instance, then decorate each of those with a similar StepWithState decorator. Now each step we work with in our trail partial will expose a custom StatusFinder aware version of the state method, but otherwise behave as a normal Step instance.

[SimpleDelegator]: http://docs.ruby-lang.org/en/2.2.0/SimpleDelegator.html

Real World Implementation

The above example used a simplified form of the practice page and thus the exact implementation shown above won't work completely. For an actual class using a StatusFinder like query object, check out the [TrailWithProgress class][] in the Upcase repo.

In addition, Joe went ahead and performed the same sort of optimizations on the real practice page in Upcase. The final implementation is, by necessity, more complex than what was shown in this episode, but it does a great job of showing how to scale up this sort of optimization. You can check out that work in [PR 1493][].

[TrailWithProgress class]: https://github.com/thoughtbot/upcase/blob/93b28e6e629979382abea6ee8302f3ddbb096030/app/models/trail_with_progress.rb [PR 1493]: https://github.com/thoughtbot/upcase/pull/1493

A Note On Process

While we would normally go through the steps of this process under a full TDD red, green, refactor loop, for the sake of time we chose to walk through this example using "refresh driven development" and eschew testing. Be sure to check out the videos on our [Testing Track][] for a better view of what this would look like with a normal TDD workflow.

Likewise, the full process Joe walked through would likely map to five or more incremental Git commits. Remember; Always. Be. Commiting! Check out the Weekly Iteration episode on [the thoughtbot Git workflow][] for more on our Git process.

[Testing Track]: https://upcase.com/testing [the thoughtbot Git workflow]: https://upcase.com/videos/git-workflow