N + What? (Or: a brief introduction to n + 1 queries and how to avoid them)
Alec DuBois
Posted on September 29, 2020
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?
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:
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? 893? 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.
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:
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:
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 (buzzword alert) of this action.
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.
Posted on September 29, 2020
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
September 29, 2020