SHARE:
Uncategorized

What Is a N + 1 Selects Statement?

Flatiron School / 17 January 2013

The following is a guest post by Li Ouyang and originally appeared on her blog. Li is currently a student a The Flatiron School. You can learn more about her here, or follow her on twitter here.

No one wants their applications getting all chubby and slow. 

From several different people, it’s been made clear to me that how you query your database will greatly affect the speed of your application. A particular problem that I learned about is the n + 1 selects statement. Don’t get confused with Big O notation because it’s entirely different. This is more specific to how SQL queries data.

Specifically in join statements, you may find yourself falling into the trap of writing n + 1 selects statement, when it can simply be just one select statement. In my application, a yoga_class object belongs to a yoga_teacher. In order to try to find yoga classes that are taught by teachers which have a certain attribute, I first did this: 

image

My terminal spit out a long line of select statements, which I’ve omitted most of. It makes the first select statement, which corresponds to our ruby YogaClass.all. Then the long list of other select statements is iterating over all the teachers of the yoga classes. This is our classic n + 1 selects statement. The 1 represents your first query, which returns a subset (in my case the whole table). The n represents a select statement for each row in the subset.

Not very efficent. How do we get around this? ActiveRecord provides finder methods called joins that act like JOIN clauses in SQL.

image

Here we only have one select statement, making everything, cleaner, faster, and easier for our database.

Class Variables vs Class Instance Variables Previous Post Adding Indices in Rails for Efficiency Next Post