Channels ▼


Performance on Rails

Solution 1: Eager Fetching with :include

Rails provides an easy way to solve basic n+1 issues: eager fetching with the :include option. When fetching the post, we can fetch all of its comments and each corresponding user in far fewer SQL queries:

def show
 @post = Post.find params[:id], :include => { :comments => :user }

In versions of Rails prior to 2.1, this statement would fetch the post, its comments, and the users with a single complicated query composed of SQL JOINs. The current version (2.1 at this writing) does not use JOINs, instead favoring one query per model that you specify in your :include parameter:

Post Load (0.000537) SELECT * FROM "posts" WHERE ("posts"."id"=1) 
Comment Load (0.001683) SELECT "comments".* FROM "comments" WHERE    
("comments".post_id IN (1)) 
User Load (0.001375) SELECT * FROM "users" WHERE ("users".id IN 

In this example, one query fetches the post, a second query fetches the comments for that post, and a third query fetches the users that wrote the comments. This approach makes for a couple of extra queries, but JOINs can be expensive and become a bottleneck in their own right. Either approach is a big improvement over the original n+1 problem.

Solution 2: Eager Fetching with JOIN

There are times that the :include option alone isn't sufficient. What happens if a post is wildly popular and has comments by hundreds, even thousands, of users? The IN clause in the users query will have a lot of IDs, and performance will begin to suffer—the query may even fail if the list of IDs is too long. The solution is to fetch even more eagerly using a JOIN in your query. Instead of writing a query for find_by_sql, encourage Active Record to use a JOIN for you:

include => :user, :conditions => ' is not null'

When you include a filter on in the :conditions option, Active Record smartly fetches the users with a JOIN (specifically a LEFT OUTER JOIN) to satisfy the dependency you've introduced in the WHERE clause on the users table. It's probably a good idea to comment code like this to document the intent of the :conditions option.

Solution 3: has_many :through

Posts have a transitive dependency on users: A post has comments, and each comment has a user; therefore, a post has commenters. Use Active Record's has_many :through to declare this dependency:

# post model
has_many :commenters, :through => :comments, :source => :user

Now each post provides a #commenters method, which returns the list of users who have commented on the post by executing a SQL query like this:

User Load (0.001009) SELECT "users".* FROM "users" INNER JOIN comments ON = comments.user_id WHERE (("comments".post_id = 1))

To get the user from that list for a particular comment, we could use some simple Ruby in the view:

# this is not optimal!
@post.commenters.detect { |u| == comment.user_id }

Note that I'm calling #detect, not #find. This is because I want to invoke the method provided by the Enumerable module, and ActiveRecord::Base#find overrides Enumerable#find.

This strategy works pretty well for a small number of commenters; however, #detect performs a sequential search, an O(n) operation. This method won't perform well for a large value of n, when there are a lot of commenters. You might want to build a lookup hash for constant-time lookup, or O(1):

# controller
@commenter_lookup = post.commenters.inject( do |hash, user|
  hash[] = user; hash

Now the view can fetch the user for a given comment from the hash:

# view

Solution 4: Aggregate

Another example of when the :include option isn't enough is when you are fetching aggregated data. Let's say you'd like to fetch the number of comments each user has created to display that next to the user's name. If you calculate it user by user (via comment.user.comments.count), you'll have another n+1 problem. One approach is to calculate the data for all relevant users in a single SQL query and build a lookup hash.

# controller
@comment_count_lookup = @post.comments.all(
:select => 'user_id, COUNT(*) as num_comments', 
:group => 'user_id').group_by(&:user_id)

This code, which could be in a controller or model, gets the number of comments for each user by grouping by user_id and counting the rows in each group with the SQL COUNT function. The #group_by method creates a hash where the key is the user_id (because it's returned by the block), and the value is an array containing all items with that key. The view can use this hash to look up the count for a given user:

# view
<%= @comment_count_lookup[comment.user_id].first.num_comments) %>

The view looks up the record for the comment's user_id in the hash, and needs to call #first to pull the one and only record out of the array before getting the count.

Code like this should be well commented, particularly the fact that it addresses a performance issue. There is another option for this kind of problem: denormalization.

Solution 5: Denormalize

"Normalize until it hurts. Denormalize until it works."

In a perfectly normalized database, there is only one representation of any particular fact. Taken to the extreme, this results in a space-efficient database with no chance of duplication or inconsistency. This is a wonderful ideal, but it comes at a cost: time-efficiency. We software developers walk a fine line between idealism and pragmatism.

Active Record makes it very easy to denormalize the number of comments that a user has created. Just enable a counter cache on the association:

class Comment < ActiveRecord::Base
  belongs_to :post
  belongs_to :user, :counter_cache => true

And create a migration to add an integer column named comments_count to the users table. Here's a migration that adds the column and calculates the count for each user because our users have created comments before we added this denormalization:

class AddUsersCommentsCount < ActiveRecord::Migration
  def self.up
    add_column :users, :comments_count, :integer,  :default => 0

    User.all.each do |u|
 User.update_counters, :comments_count => u.comments.count

  def self.down
    remove_column :users, :comments_count

Every time users create a new comment, their comments counter cache is incremented, and if they delete a comment, the counter cache is decremented. Now the view can display the user's comment count without any additional database queries:

<%= comment.user.comments_count %>

Counter caches are a simple type of denormalization that is built into Active Record, but your requirements might be more complex. Let's say you want to denormalize the date and time of the user's first comment. I would use the before_create and before_destroy lifecycle hooks to keep the data in sync:

# comment.rb
def before_save
  # time can only move forward, so this is pretty simple
  user.update_attribute (:first_comment_at, if        user.first_comment_at.nil?

def before_destroy
  # need to handle case where user deletes their first comment
  earliest = user.comments.first :conditions => ['id <> ?',], 
   :order => 'created_at'
  user.update_attribute(:first_comment_at, earliest.created_at)


Ruby the language and Rails the framework often take a beating from detractors on the question of performance. Ruby's not in the running to be the fastest language, and it's not even the fastest interpreted language. Rails isn't the fastest framework. However, if Ruby or Rails is your bottleneck, consider yourself lucky! Most unexpected performance challenges are related to querying the database and aren't detectable until the app has been in the wild, which is no different than any other software development framework.

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.