Techniques for reducing database queries in a Rails app

Question

If you have a Rail app with many complex associated models, what techniques do you employ to reduce database queries?

In fact, I'll extend that question a little further and ask, what do you consider "too many" queries for any page?

I have a page that I expect will end up hitting the database about 20 times each page load. That concerns be but don't know whether it should concern me, or what I can do to reduce the load?

Answer 1

It is really difficult to estimate a limit for queries. This is related at the concept/design of your application.

If you don't have to reload the whole page, I suggest you consider javascript (or rjs) in order to update only the data you need. This should be also an UI improvement, your users will love it!

Check the SQL generated from your ActiveRecord queries. Be sure that everything is like expected.

Consider to denormalize your db in order to improve performance. (be carefully)

This is what I see from the "code side".

Answer 2

One common practice is judicious use of the include => :attribute option.

For instance on a controller you might do:

def show
    @items = Item.find(:all) 
end

...and the show view would do something like:

<% @items.each |item| %>
    <%= item.some_attribute %>
<% end %>

This will create a query for every call to some_attribute. But if you declare the attribute included as follows, you get eagerly-loaded attributes in one query:

def show
    @items = Item.find(:all, :include => :some_attribute)
end

As always, check your console for query times and such.

Answer 3

Check out: bullet

Its a great way to identify n+1 queries and it offers suggestions to minimize it.

It does slow down development mode, so be sure to disable it when you are not performance tuning.

While we are at it, also checkout: rails_indexes

A simple way to identify which indexes your app could be missing.

Happy tuning.

Answer 4

I am useing :joins and :select options if you need just to display data. I found very useful named_scope to define all possible :joins and one :select_columns named_scope. Example

    class Activity < ActiveRecord::Base
      belongs_to :event
      belongs_to :html_template
      has_many :participants

      named_scope :join_event, :joins => :event
      named_scope :join_owner, :joins => {:event => :owner}
      named_scope :left_join_html_template, 
      :joins => "LEFT JOIN html_templates ON html_templates.id = activities.html_template_id"
      named_scope :select_columns, lambda { |columns| {:select => columns}}
      named_scope :order, lambda{ |order| {:order => order}}
end

So now you can easly build queries like this:

columns = "activities.*,events.title,events.owner_id,owners.full_name as owner_name"
@activities = Activity.join_event.join_owner.order("date_from ASC").select_columns(columns)

I consider this is not the best and safest way, but in my case it really minify query count that executes per request and there are no errors rised about some wrong generated queries yet.