Active Record is slow, how to make it your friend

Note: this blog post about how to make Active Record better comes to us from Engine Yard friend JoseLuis Torres of Powhow. Thanks JL!

I’m sure everyone has faced the following situation: a mix of experienced and inexperienced Ruby on Rails developers working on the same project with many features in various states of completion: multiple landing pages, loads of dynamic content, searches, etc. All of this in the context of developers preferring Ruby and/or Ruby on Rails because it is elegant, fun to work with, and easy to read.

For the most part, all of those things are true; except for the fact that small applications can become very slow and unresponsive when all of the developers’ experience combined doesn’t stop them from, for example, using something this in a view:

<% @user.profile.tickets.each do |ticket| %>
 <% ticket.reviews.each do |review| %>
   <%= review.rating %>
 <% end %>
<% end %>

…when trying to show all ratings a class/product/service has.

Experience aside, the sooner we all realize that, although Active Record syntax is glamorous and beautiful to read, it can leave much to be desired in terms of performance. Continuing with the “showing all ratings for a class/product/service” scenario, we shouldn’t be using more than 1 relationship to find something by using Active Record… but why?

To explain:

1. Using the User.find(1).profile will automatically load all the columns from user and profile. As we know, those tables usually contain many fields that might not be needed in this scenario.

2. Trying to do it with 3, 4, or 5 tables will have the same effect, but there will be a performance cost because it is unnecessarily adding an additional query each time.

3. Your product owner will yell at you many times.

So what should we do?.

Verify this is the case. Read your logs or install the New Relic gem, and take a look at the thousands of unnecessary queries the app runs.

Once that is done, it’s time to start thinking about the SQL query. Many people ignore this as an important part of the true interaction with Active Record. Asking the following questions may help to define the most efficient way to extract the data needed:1. What columns do I need to show the information I want? Choose wisely and remember that Model.select() or pluck is your friend.

1. Is there a mathematical operation that can done in order to show the correct result? Check if you can do it by using embedded SQL functions from whatever DB you use.

2. Do you need to include a specific concatenated list of columns from another table or a specific format? If so, use subqueries.

3. Create your query in your favorite SQL editor, run it against your local first.

4. Now generate the SQL with the correct parameters. Don’t use more than one relationship in your Active Record.

This will reduce the time consumed, because you won’t be generating a query +1 each time when looping through the results. Remember also that you don’t need to add “select *” to grab every single column in the tables. When in doubt, remove all the columns and add one at a time until you get the correct results.