Rails find_by_sql (or joins!) - boost your rails app performance with this incredibly easy SQL tweak

Does your rails app feel sluggish?   Do your app users exhale loudly in utter desperation as they wait for pages to load?  Does it need a red bull and a few shots of espresso?

Good news!  Injecting the rails find_by_sql select method in a few key places can turbocharge your app performance.   Read below and transform those ugly duckling slow database queries into majestic swans that preen in their snappy response time glory.  

Spend an hour or two and I promise your app users will thank you.

Man do I love Ruby on Rails!   True story - many years ago I locked myself in my apartment for a whole weekend to learn Rails.  I forced myself through version one of Agile Web Development With Rails.  No one every accused me of acting like a normal person.

I dove into this before Rails hit version 1.0 and before useful things like . . . migrations.  Man, I am old.  Coming in as a .Net refugee, rails opened my eyes to what tight, beautiful, efficient code could look like when building web apps. Thank you DHH & crew!

Back to the story.   I mentioned I came to rails as a .Net refugee.   In my years as a .Net developer, I'd spend says staring at SQL Server, writing SQL queries, tuning existing ones, and diving into the depths of SQL performance.  

TABLE SCANS!  WE DON’T NEED NO STINKING TABLE SCANS!

One of the biggest things Scott, our lead developer, pounded into our heads was using the power of SQL joins to avoid looping at all costs.

Why does this matter to your rails app?   Because rails by default creates inefficient looping SQL when you chain models in your views.  The great news is that using the find_by_sql active record command can solve this!

"Huh?" you say?   Let me explain with an example.

I spent a few hours this week building out a simple rails app to store real estate agent information.  I own  MovingCompanyReviews.com, a site where consumers can find trustworthy movers (and get free pizza on their move day!).   We offer a cool program to real estate agents where they can set up their own "recommended movers" page. We want to reach out to more real estate agents, so we snapped up a bunch of agent data.

To make the data useful, I created a simple rails app with two tables, agents and emails (since agents collect emails like freebies at a trade show).   I enjoy rails scaffolding for simple apps, so I used that for the views. After loading the data in, I fired up the app and went to the agents index page.   I show 1,000 agents at a time, well, because I am greedy and I want ALL the info.  

I show the count of agent emails along with some agent info, cool huh?

Weeeelllllll, I notice the page loads a bit, ahem, slowly.  On my machine I waited patiently for ~12 seconds. Too slow! Let's pop over to the console to peek under the covers.

Whoaaaaa Nelly!   Would you look at all those database queries!   The console output shows rails submits a separate query EACH time we call this in our view:

agent.emails.count

Curses!

Each query only takes ~6 milliseconds, but when you multiply that times 1,000 you step in a steaming pile of . . . slowness.

What can we do to fix this ugliness?

Time for the Rails find_by_sql method to save the day!

Let's change our controller code from this:

def index
   @agents = Agent.all.limit(1000)
end

To this:

def index
  @agents = Agent.find_by_sql(
     "SELECT
        agents.id,
        agents.first_name,
        agents.last_name,
        agents.email,
        COUNT(emails.id) as email_count
      FROM agents
      INNER JOIN emails
        ON agents.id = emails.agent_id
      GROUP BY
        agents.id,
        agents.first_name,
        agents.last_name,
        agents.email
      LIMIT 1000"
      )
end

And let’s change our view code from this:

       <td><%= agent.first_name %></td>
       <td><%= agent.last_name %></td>
       <td><%= agent.email %></td>
       <td><%= agent.emails.count %>

To this:

       <td><%= agent.first_name %></td>
       <td><%= agent.last_name %></td>
       <td><%= agent.email %></td>
       <td><%= agent.email_count %>

What do we get?

Wow!   The view returned back in 342 milliseconds, with the database work only taking 30.2 milliseconds!   We just made it 35 times faster! 3 cheers for us, let’s guzzle some beer!

As you can see, optimizing your Rails queries a bit with find_by_sql can massively improve performance if your current queries throw a ton of database queries when a view loads.

But wait, there’s more!  

Please Meet the Rails Joins Clause

The rails find_by_sql clause gives us one way to solve this pickle, but you can use another Rails bag of goodies as well - the rails join operators!   I find them a bit more cumbersome to work with, but they follow the Rails “path more traveled” a bit more. If you strive for Rails purity, follow in these footsteps.

So, how do Rails joins work?  Let’s take a look at our query above:

def index
  @agents = Agent.find_by_sql(
     "SELECT
        agents.id,
        agents.first_name,
        agents.last_name,
        agents.email,
        COUNT(emails.id) as email_count
      FROM agents
      INNER JOIN emails
        ON agents.id = emails.agent_id
      GROUP BY
        agents.id,
        agents.first_name,
        agents.last_name,
        agents.email
      LIMIT 1000"
      )
end

Rails’ ActiveRecord query interface let’s you write the same query like this:

def index
   @agents = Agent.all.select("agents.id, first_name, last_name, agents.email, COUNT(emails.id) as email_count").joins(:emails).group("agents.id, first_name, last_name, agents.email").limit(1000)
 end


Nifty huh?   Firing up your Rails server and loading your page gives us the same query and performance as our find_by_sql query.  Cool!

Conclusion

Uh duh?   Use Rails find_by_sql or Rails joins to make your queries sing the exulted song of fast execution time!  Put smiles on the faces of your users as pages appear before their eyes before they can flip their browser to Instagram or Twitter.   

So get to work finding those looping queries and transform them into lighting-quick things of beauty with your newfound knowledge!

And if by chance you need a dead simple way of running SQL reports in Slack or emailing them (coming soon) give SQLBot a try!

UPDATE:  Check out the bullet gem if you want a way to monitor your app for these,  thanks reddit for that suggestion.

UPDATE #2:  Scout presents a nice option for sniffing out n+1 Rails queries too.  They offer a free plan on Heroku.   Good stuff.

  

Try SQLBot.co, it's Free!

Do you ever write SQL reports that you’d like to share regularly in Slack or over email?   Instead of using something like sp_send_dbmail, sign up for a free account on SQLBot to see how easy it is to get SQL reports into your Slack.  What are you waiting for, it's free!