A newbie's look at N+1 queries

N+1 queries usually happen when there is a one-to-many relationship, where we (I) try to call a record related to an object that is the child(many) of the main parent(one) object.

Let's use for this article an Author(one) and her books(many).

If you're not sure whether it IS an N+1 problem query or not, check your log (bin/dev).
You'll know you have an N+1 error when your parent object is loaded fine, but then underneath you'll have a bunch of children object loaded separately from one another, back-to-back.

So in our example, you'd see first that you'd be loading the author.

SELECT "author" FROM "authors" WHERE "authors"."id" IN "Fantasy" AND "books"."published blablabla"

That's fine, but then the problem arises when you see something like this after.

SELECT "book" FROM "books" WHERE "books"."id" blablabla ["id", 4]
SELECT "book" FROM "books" WHERE "books"."id" blablabla ["id", 7]
SELECT "book" FROM "books" WHERE "books"."id" blablabla ["id", 9]
SELECT "book" FROM "books" WHERE "books"."id" blablabla ["id", 11]
SELECT "book" FROM "books" WHERE "books"."id" blablabla ["id", 15]
...etc

So over and over again we're loading these books, and each time we do that, we're making a request to the database. Sure, that's not a big deal in my example because we made 5 requests, but it does start to become an issue when authors or author groups have hundreds of books. Publishing houses that have tens of thousands. Obviously this wouldn't be a good way to go about getting that data.

The solution to this lies in the controller, where we are selecting the data, and on how we include the author data in our book query... See what I did there?

We are telling Rails that we want to run just one statement to select all that related data at once, instead of running multiple individual statements for each individual record later.

@books = Book.includes(:author).order(published_at: :desc)

Looking back at our log, we should see something like this

SELECT "book" FROM "books" WHERE "books"."id" blablabla [["id", 4], ["id", 7], ["id", 9], ["id", 11], ["id", 15]]

It's really good form to go back in your logs and look at how your data is being loaded, and where the load is coming from in your application's code! ☺︎

And for when we need a little extra help, there are always neat gems like this one

Let's keep doing our best!