Optimize your Active Record queries

by Andreas Knöpfle about 3 years ago
In this post I give some simple tips on how to use pluck, preload and include to optimize your Active Record queries.

From time to time you notice that a Rails application that uses ActiveRecord will have performance or memory issues. Let's say you just have some has_many or habtm relations and you want to display some page where you render your models. 

Lets say we have the following models (yeah I know it always Post and Comments)

class Post < ActiveRecord::Base
  has_many :comments
  belongs_to :user
end

class Comments < ActiveRecord::Base
  belongs_to :post
  belongs_to :user
end

class User < ActiveRecord::Base
  has_many :comments
  has_many :posts
  has_one :avatar
end

class Avatar < ActiveRecord::Base
  belongs_to :user
end

When you develop an index-view for your posts and you want to show the avatar picture of the user that created the post you would probably do the following:

def index
 @posts = Post.page(params[:page])
end
<% @posts.each do |post| %>
  <div class="post">
    <h1> <%=  post.title %> </h1>
    <%= image_tag post.user.avatar.image_url %>
  </div>
<% end %>

 

This would create a lot of SQL queries for your database. For example if you show 10 posts per page you will get:

  • 1 x get the posts "SELECT * FROM 'posts' LIMIT 10 OFFSET 0"
  • 10 x get each user "SELECT * FROM 'users' WHERE ID = ..."
  • 10 x get each avatar for the user "SELECT * FROM 'avatars' WHERE ID = ..."

So in total you will end up with 21 queries. These additional queries are called n+1-queries and can really slow your page loding time down.

 

Prevent n+1 queries

ActiveRecord has build-in mechanisms to prevent such scenarios:

def index
 @posts = Post.includes(user: :avatar).page(params[:page])
end

In the same setup we only get 1-3 queries. Either AR does a Database-Join and grabs everything at once, or it preloads your models, resulting in the following three queries:

  • 1 x get the posts "SELECT * FROM 'posts' LIMIT 10 OFFSET 0"
  • 1 x get each user "SELECT * FROM 'users' WHERE ID IN (1,2,3 ...)"
  • 1 x get each avatar for the user "SELECT * FROM 'avatars' WHERE ID IN (1,2,3 ...)"

 

Know your tools

If you use #includes, AR decides for you which technique it prefers. You can control the behaviour yourself if you replace #includes with:

 

Hint: There is gem called bullet that you can use to find n+1 quries.

Lets say we wan't to display the last two comments for each post on our posts#index page.

<% @posts.each do |post| %>
  <div class="post">
    <h1> <%=  post.title %> </h1>
    <%= image_tag post.user.avatar.image_url %>
    <% post.comments.order(created_at: :desc).limit(2).each |comment| %>
      <p> <%= comment.text %> </p>
      <%= image_tag comment.user.avatar.image_url %>
    <% end %>
  </div>
<% end %>

 If you would just include the comments in your post query you would get something like this:

def index
 @posts = Post.includes( comments: { user: [:avatar] }, user: [:avatar]).page(params[:page])
end

This would load all comments for each post, which is a bad idea for posts with many comments.

 

Scoping relations

In this case you can add scoped relations to your models and use them to limit the results like this.
 
class Post < ActiveRecord::Base
  has_many :comments
  has_many :last_comments, -> { last } , class_name: 'Comment' # use the scope here
  belongs_to :user
end

class Comments < ActiveRecord::Base
  belongs_to :post
  belongs_to :user

  scope :last, -> { order(created_at: :desc).limit(2) } # limit through scope
end

In order to use the relation that you limited, just swap "comments" with "last_comments" in your includes-statement and in the views. 

 

Next article on rails optimizations.

 

Back

Announcing treasures.io

Discover the internet of islands and sail through the online ocean.


asdf Version Manager & Postgres UUID

Compiling Postgres with UUID to use in asdf


Lombok Impressions

Our trip to the Indonesian island Lombok in October 2015


Deploying Phoenix app with dokku-alt and Dockerfile

My experiences in deploying a Elixir Phoenix app on my dokku-alt instance.


Moved to netcup and dokku-alt

How i moved this page to run on netcups vhosts with dokku-alt Docker instances