Optimizing Ruby on Rails Database Query Performance

One important step in optimizing Rails performance is at the query level. This can be done in a number of steps.

1. Database Gluttony: No optimization

While you’re learning Rails or first developing an application it’s very convenient to set up your controller to find @albums this way:

@albums = Album.find(:all, :limit => 20)

With this basic find you can access associations in the view this way:

@albums.band.name

The deficiency here is that if you are looping through 20 records, your database is going to get hit one time for each iteration of that loop, for a total of 21 queries performed.

2. Reducing Excess Queries: Eager Loading

One first step you can take is to include associated records you’re going to later require in the view:

@albums = Album.find(:all, :limit => 20, :include => [:band])

This is known as eager loading and it means your queries will drop in number from 21 to 2 in this example, Rails first loads the albums, then loads the bands. Your view code need not change at this point.

3. Reducing Excess Data: Limiting Selected Fields

@albums = Album.find(:all, :limit => 20, :select => 'albums.name, albums.band_id', :include => [:band])

If all you require is album.title, you can optimize your query a bit more to only select only those fields. Make sure to select any foreign keys that Rails will need to perform the second association query - here I’ve selected albums.name as well as albums.band_id for this reason.

4. One Less Query: Using :joins

Step three reduced the number of fields selected from the albums table but the association table is still loading all fields. If you only need a single field out of that table you might want to join the two queries and just select the few fields you need from each table:

@albums = Album.find(:all, :limit => 20, :select => 'albums.name, bands.name AS band_name', :joins => [:band])

This is now a single query, but it will require some reworking in the view, because you will no longer be able to access the band name through association. Your view code will now referencealbum.band_name instead of album.band.name

Note: This technique of joining tables into a single query can come in handy if you begin to experiment with the model caching new to Rails 2.1.

5. Model caching

Let’s say you’re constantly hitting the database for recently added items from a number of categories to build a sidebar. You might be showing the 10 most recent albums, and the 10 most recent bands down the side of each page. Rails 2.1 now offers more built-in caching options that are very easy to use and can drastically reduce the number of database hits you’re making by storing that list of recent items in the cache for constant reuse. So you might set up this scenario in your Album mode:

def self.recent_cached
  Rails.cache.fetch('Album.recent_cached') { Album.recent }
end

def self.recent
  Album.find(:all, :limit => 10, :order => 'id.desc')
end

This might reduce your queries from 2 to 1 - one for your main page item, one for your sidebar of recent albums, and if you were also searching for recents bands your queries drop from 3 to 1. Furthermore, if you’d previously been using a single association on each of those recent sidebar items without eager loading your queries could drop from 23 to 1.

Further Optimization

These techniques specifically focus on optimising database hits. If the situation allows, you can benefit even more from other, more complete methods of caching, page caching in particular. Page caching can insure that Rails will never even get touched: when a user requests a page, it will get served directly to them from a cached HTML file store on your server.

I am available for Ruby on Rails consulting work – get in touch to learn more.

Aug 10, 2008