N + What? (Or: a brief introduction to n + 1 queries and how to avoid them)
Tags: beginners, sql, ruby, rails
Published: 2020-09-29 05:17:46

Heads up: this is an extremely simplified introduction written for the perfect beginner.

For more on this topic, I recommend leaving an offering at your local altar to Google and whispering your question into the search bar. Quietly, though! Don't want the neighbors to hear.

_Extra heads up: This tutorial uses Ruby on Rails and ActiveRecord to demonstrate the topic, but the general idea applies in a much wider context_

_Last heads up, I promise, please don't stop reading yet: notes for absolute beginners may appear throughout this article that look like this_

### Jump to:

- [N + What?](#intro)
- [How To Avoid Them](#avoid)
- [In Closing](#closing)

<a name="intro"></a>

#### N + What?

To put it simply, n + 1 queries are when an action in an ORM generates (1) query for the object you're looking for, then (n) queries for the associations of that object.

Pretty dry, right? Let me demonstrate:

Let's say you're building a monster tamer application. (Does anyone else remember Azure Dreams?)

You have a model for Tamer, a model for each Monster, and a MonsterTamer model as a join table - the MonsterTamer model keeps track of which Tamer owns which Monster - a _has-many-through_ relationship.

_Bear with me: The models aren't the point here, the queries for the relationships are_

I want to call up my database for all of the Tamers and each of their Monsters. In a Ruby on Rails application, it might look like this in a Controller action:

```ruby
def index
  render json: Tamer.all, include: [ :monsters ]
end
```

The actual SQL query generated by this expression might look something like:

_For the curious: I'm pulling these queries from the log generated by Ruby on Rails after running `rails server`, and in this case executing a simple GET request by cheating and manually calling fetch() in the javascript console in my browser._

```
Started GET "/tamers" for ::1 at 2020-09-28 21:42:35 -0700
   (0.6ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
Processing by TamersController#index as */*
  Tamer Load (0.7ms)  SELECT "tamers".* FROM "tamers"
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (0.8ms)  SELECT "monsters".* FROM "monsters" INNER JOIN "monster_tamers" ON "monsters"."id" = "monster_tamers"."monster_id" WHERE "monster_tamers"."tamer_id" = $1  [["tamer_id", 11]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (0.6ms)  SELECT "monsters".* FROM "monsters" INNER JOIN "monster_tamers" ON "monsters"."id" = "monster_tamers"."monster_id" WHERE "monster_tamers"."tamer_id" = $1  [["tamer_id", 12]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (0.6ms)  SELECT "monsters".* FROM "monsters" INNER JOIN "monster_tamers" ON "monsters"."id" = "monster_tamers"."monster_id" WHERE "monster_tamers"."tamer_id" = $1  [["tamer_id", 13]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (1.8ms)  SELECT "monsters".* FROM "monsters" INNER JOIN "monster_tamers" ON "monsters"."id" = "monster_tamers"."monster_id" WHERE "monster_tamers"."tamer_id" = $1  [["tamer_id", 14]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (0.6ms)  SELECT "monsters".* FROM "monsters" INNER JOIN "monster_tamers" ON "monsters"."id" = "monster_tamers"."monster_id" WHERE "monster_tamers"."tamer_id" = $1  [["tamer_id", 15]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (0.7ms)  SELECT "monsters".* FROM "monsters" INNER JOIN "monster_tamers" ON "monsters"."id" = "monster_tamers"."monster_id" WHERE "monster_tamers"."tamer_id" = $1  [["tamer_id", 16]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (0.8ms)  SELECT "monsters".* FROM "monsters" INNER JOIN "monster_tamers" ON "monsters"."id" = "monster_tamers"."monster_id" WHERE "monster_tamers"."tamer_id" = $1  [["tamer_id", 17]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
Completed 200 OK in 80ms (Views: 60.0ms | ActiveRecord: 15.6ms | Allocations: 23215)
```

**Whoa whoa whoa.** Look at all those queries! Our tamers might only have a few monsters now, but what happens when they have 150? <a href="https://en.wikipedia.org/wiki/List_of_Pok%C3%A9mon" target="_blank">893</a>? This is only with a handful of Tamers, too!

This is an example of an **n+1 query**, where we have (1) query for the initial model plus (n) queries for its associations.

<a name="avoid"></a>

#### How To Avoid Them

We've looked at a bit of what a layer like ActiveRecord is doing under the hood: generating and executing queries to the database based on function calls written in another language - in this case, Ruby.

Let's look more closely at this action I'm asking it to perform:

```ruby
def index
  render json: Tamer.all, include: [ :monsters ]
end
```

In this, I'm asking for **all** of the Tamers, **then** asking for each of those Tamer's monsters when I express `include: [ :monsters ]` further along in the line.

The problem is that my application doesn't have the astoundingly powerful predictive capacity I do as a human being. When I ask it to draw `Tamer.all` - _and this is an oversimplification_ - it has **absolutely no idea** that I'm going to ask it for those Tamers' associated monsters a few characters later.

Let's try something else:

```ruby
def index
  render json: Tamer.includes(:monsters).all, include: [ :monsters ]
end
```

See that `.includes(:monsters)`? That's telling the query generator - ActiveRecord in this case - that I want to draw Tamer.all, _but include each of their associated monsters_.

Here's the query that generates with three (3) Tamers:

```
Started GET "/tamers" for ::1 at 2020-09-28 21:54:02 -0700
   (5.0ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
Processing by TamersController#index as */*
  Tamer Load (0.6ms)  SELECT "tamers".* FROM "tamers"
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  MonsterTamer Load (1.0ms)  SELECT "monster_tamers".* FROM "monster_tamers" WHERE "monster_tamers"."tamer_id" IN ($1, $2, $3)  [["tamer_id", 18], ["tamer_id", 19], ["tamer_id", 20]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (0.5ms)  SELECT "monsters".* FROM "monsters" WHERE "monsters"."id" IN ($1, $2, $3)  [["id", 24], ["id", 25], ["id", 26]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
Completed 200 OK in 92ms (Views: 68.5ms | ActiveRecord: 13.6ms | Allocations: 18700)
```

Huh. That looks a lot lighter - it's only three queries!

Let's _doubleplusone_ the number of Tamers to seven (7) and see what happens:

```
Started GET "/tamers" for ::1 at 2020-09-28 22:45:38 -0700
   (0.6ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
Processing by TamersController#index as */*
  Tamer Load (0.7ms)  SELECT "tamers".* FROM "tamers"
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  MonsterTamer Load (1.0ms)  SELECT "monster_tamers".* FROM "monster_tamers" WHERE "monster_tamers"."tamer_id" IN ($1, $2, $3, $4, $5, $6, $7)  [["tamer_id", 24], ["tamer_id", 25], ["tamer_id", 26], ["tamer_id", 27], ["tamer_id", 28], ["tamer_id", 29], ["tamer_id", 30]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
  Monster Load (1.2ms)  SELECT "monsters".* FROM "monsters" WHERE "monsters"."id" IN ($1, $2, $3)  [["id", 30], ["id", 31], ["id", 32]]
  ↳ app/controllers/tamers_controller.rb:3:in `index'
Completed 200 OK in 72ms (Views: 50.6ms | ActiveRecord: 15.0ms | Allocations: 20559)
```

**Hold up.** That's the **same** amount of queries generated by _half_ the number of records!

**That's right.** We've now solved for n + 1 queries for this model in our application, meaning that _no matter how many records there are, the number of queries will never increase_.

While for a small application with a handful of records and few concurrent users you may see absolutely no actual difference in performance, this represents a mathematically significant decrease in the time complexity _<a href="https://en.wikipedia.org/wiki/Time_complexity">(buzzword alert)</a>_ of this action.

<a name="closing"></a>

#### In Closing

There's no punchline; play with this the next time you find yourself drawing a resource out of a database and tell your barista tomorrow that you learned about n + 1 queries. _(Probably don't do that last bit, actually)_

Leave a comment if you think I left anything out, or if you want to tell me why Nutella is better than peanut butter.