Fixing the N+1 problem in my Rails application

UpLearn Social Learning Rails app
UpLearn – an educational sharing Rails app

Earlier this year, I finished General Assembly’s Back-End Web Development course in Washington DC, where I learnt Ruby and how to build data-driven websites using the Rails framework. It was a brilliant experience. I did a lot of work outside the course to learn as much as I could about web development, treating the 10 weeks as my own full-time dev bootcamp.

My final project was an educational web application called UpLearn, built with Rails and hosted on Heroku, where users can find and share online learning resources. It’s a community-driven website, which helps users find the best online educational content, for example, a well-done tutorial on learning Javascript.

You can view the project here and see the source code here on GitHub. More information about the project can also be found on the app’s About page.

Now the dust has settled on the course, I’ve had a chance to reflect on what I learnt and begin the next stage of knowledge acquisition. I’m doing this by improving the performance of my final project app and refactoring the code, and, over the coming weeks, adding more advanced features and implementing a testing framework.

Improving the performance of the app

I’m aware of two big performance drags on the application at the moment, one being the multiple calls to the database when rendering the index page (the N + 1 problem that is the topic of this post), and the other being the screenshot API, which I plan to improve by storing and retrieving images from an Amazon S3 bucket instead. That’s a topic for another day. In this post I’m going to address the first of these issues.

As mentioned, the app has an N+1 problem, in a nutshell making lots of calls to the database when it only needs to make one. Loading the index page makes WAY too many calls to the database. The first is to get all the links, which is fine, but then as the app loops through each of those links in turn, it makes new calls to the database for the upvote counts, comment counts and usernames of those who posted the link. Check out the server log from the terminal (and this is just a snippet!):

Server Log N + 1 problem

See all those SQL queries? I went through the server log and counted that the application was making 62 calls to the database. Not good! Let’s see what they are:

  1. 1 query to load username if user logged in
  2. 1 query to load 15 links to display on home page
  3. 15 queries to count upvotes for each of the 15 links
  4. 15 queries for the username who shared each link
  5. 15 queries to count comments for each link
  6. 15 queries to get tags for each link

Wow, no wonder my app was slow before. I’m using eager loading to speed this up, improve my code and reduce the number of SQL calls to the database down to single digits.

Benchmarking this by repeatedly reloading my app and taking an average of last three loads, it took around 6ms for the Active Record load when rendering the index page, as shown in the highlighted area of the following screenshot of my terminal window (and yes, although Rails does some caching, it’s minimal in this scenario):

Server log time

The API’s (one of which is the screenshots mentioned above) are a larger problem but let’s fix one issue at a time, and deal with that in a future blog post. (Note: the overall load time for app was influenced by the load time of the APIs, which in turn were determined by the internet speed of the coffee shop where I was working 😉 .)

Step 1: Dealing with the 15 queries that count upvotes for each link

In my index page I have a partial that contains the upvote buttons. Within this partial I have a line of code, <%= link.upvotes.count %>, that gets called each time I loop through all my link posts, making a call to the database. This is the issue, the N part of the N + 1 problem. I’m making N calls to the database when I should really just make one.

So the first thing I want to do is to jump into my rails console and re-create the problem there to get a better understanding, as follows:

Upvotes count in rails console
Upvotes count in rails console

You can see the multiple calls to the server to count the number of upvotes for each link id in turn. Instead of doing this, we can make one call to the database and return a hash of link id’s with their upvote counts, as shown in the following SQL query in the terminal:

SQL query in rails console
SQL query in rails console

The hash {36=>1, 19=>1, 35=>1, 32=>1, 38=>2} in the console above now contains a list of link id’s that have non-zero upvote counts, which we’ll easily be able to access in the Rails app index view.

So firstly I put a new Ruby line to create a SQL query, into the controller of my rails app:

Then I can replace this line of code <%= link.upvotes.count %> in the view (which makes a call to the database each time in the loop) with:

which just accesses the hash table of upvote counts I created and uses the “link_id” as a lookup value. The ruby double pipe with the 0 is needed to set the value to 0 in the absence of a value in the hash table.

Great! When I now check my server logs, those 15 calls to the server to get the upvote counts for each link are now gone. Instead, I made only one call to the database, which is much faster.

Step 2: Dealing with the 15 queries that count the comments for each link

This is the same issue as above, where I’m making separate database calls on each loop. The offending code is the link.comments.count:

So again, the solution is to write a new SQL query with a group_by clause that creates a hash table of the comment counts, and put it into my links controller:

This creates a hash table again, similar to the one above, with each link id that has at least one comment listed. Then I can replace the link.comments.count with the @comments_count[]. The final step is to put an “if” clause into my code to deal with links that have no comments (i.e. the ones that aren’t in the hash table). This is achieved as follows:

I also moved this into the comments helper to make my code cleaner, but this had negligible affect on the load speed when I looked at the server logs.

As with Step 1, this change replaced those 15 calls to the server with a single one.

So, after a little work, I’ve reduced the number of database calls down from 62 to 34. Let’s keep going and get this down to single figures.

Step 3: Dealing with the 15 queries that surface all the tags for each link

This is my code in the view (an html.erb file) to handle rendering tags for each link:

So, for each link it calls the database and gets the tags associated with that link (i.e. once per loop). There’s a better way, by telling Rails about this association ahead of time, using Rails eager loading functionality.

To get a better understanding of what’s happening, I re-created it in the rails console:

Tags database calls in the terminal
Tags database calls in the rails console

So, you can clearly see those problematic calls to the database in each loop. Now there are two ways of solving this with eager load: preload or eager_load. We can delegate the decision of which method to use to Rails, by using the “includes” method, with the following code:

When I re-run the loop in the Rails console, check out how it looks:

Tags in rails console with eager loading
Tags in the rails console with eager loading

All those SQL queries have gone!

In my code, it’s as simple as adding this .includes(:tags) into my controller code when it calls the database. So, this:


That’s it! Another 15 calls to the database removed from the app, which is good news from a speed perspective. The active record time has now decreased from about 6ms to 2ms, which is pretty good. The number of calls is now down from 62 to 20.

Let’s work on the last N + 1 problem: querying the database to get the user for each link as we loop through. Another 15 queries we should be able to remove.

Step 4: Dealing with the 15 queries that get the username for each link

This is the same problem and same solution as the tags association above. We can tell Rails about the association with users in advance, thereby removing the need to repeatedly query the database. The code is as follows, with the new addition in red:


After implementing the four steps above, I’ve reduced the number of SQL queries when rendering the index page from a gargantuan 62 down to a much more reasonable 6. Nice!

The active record call speed has dropped from almost 6ms to about 1.5ms.

Active Record speed improvement
Active Record speed improvement

Go hunt those N + 1 issues!


Eager loading with Active Record from

Explanation of preload, eager_load and includes in Rails 4

Active Record Query documentation

Leave a Reply

Your email address will not be published. Required fields are marked *