CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Andreas Lyngstad
Andreas Lyngstad

Reputation: 4927

Optimize ruby on rails SQL queries

Update start

This problem was with rendering a form for each project, not with SQl queries. To optimize I will add the form as needed with javascript.

It seems like I didn't read the miniprofiler log correct. I apologize, but leave the question for others that might have similar questions.

Update end

I am using miniprofiler to find bottlenecks in my app. And I found one!

SELECT "projects".* FROM "projects" INNER JOIN "memberships" ON 
"projects"."id" = "memberships"."project_id" WHERE 
"memberships"."user_id" = 1 AND (active = 't')   
1059.50 ms  
Rendering: projects/_index — 1023.18 ms

It is finding 185 projects in 1 whole second.

How can I make a query that does this more efficient?

I have this in my projects_controller index

@projects = current_user.projects.is_active

The is_active scope in the projects model

scope :is_active, where(["active = ?", true])

The projects and users has a many to many relationship, with a membership join table

The membership model 

class Membership < ActiveRecord::Base
  attr_accessible :project_id,:user_id,:created_at,:updated_at
  belongs_to :user
  belongs_to :project
end

The membership table

def self.up
    create_table :memberships do |t|
      t.integer :project_id
      t.integer :user_id

      t.timestamps
    end
    add_index :memberships, [:project_id, :user_id], :unique => true
end

I am running this in the production environment on the local computer with postgreSQL as the database

Adding explain by Jiří Pospíšil request. In the console it does not seem to be slow at all. This explain is done in development. Have the same problem there

User.first.projects.is_active.explain
  User Load (0.3ms)  SELECT "users".* FROM "users" LIMIT 1
  Project Load (2.3ms)  SELECT "projects".* FROM "projects" INNER JOIN "memberships" ON "projects"."id" = "memberships"."project_id" WHERE "memberships"."user_id" = 1 AND (active = 't')
  EXPLAIN (0.2ms)  EXPLAIN QUERY PLAN SELECT "projects".* FROM "projects" INNER JOIN "memberships" ON "projects"."id" = "memberships"."project_id" WHERE "memberships"."user_id" = 1 AND (active = 't')
 => "EXPLAIN for: SELECT \"projects\".* FROM \"projects\" INNER JOIN \"memberships\" ON \"projects\".\"id\" = \"memberships\".\"project_id\" WHERE \"memberships\".\"user_id\" = 1 AND (active = 't')\n0|0|1|SEARCH TABLE memberships USING INDEX index_memberships_on_user_id (user_id=?) (~10 rows)\n0|1|0|SEARCH TABLE projects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)\n"

The view

<% @projects.each do |project| %>
<li class="tab_list" id="project_<%= project.id.to_s %>"> 
  <div class="tab_list_text"><%= link_to project.name, project_path(project) %></div>
  <span class='open_project_update button edit' id="project_update" data-id="<%= project.id %>" data-object="project" title="Edit project">Edit</span> 
  <div class="dialog_form" id="project_update_<%= project.id %>_form" title="Update project" style="display:none;">
      <%= form_for(project) do |f| %>
      <ul>
      <li><%= f.label :name %><%= f.text_field :name %></li>
      <li><%= f.label :description %><%= f.text_field :description %></li>
      <li><%= f.label :due %><%= f.text_field :due, :value => project.due.strftime("%Y-%m-%d"), :id => "date_project_#{project.id}"  %></li>
      <li><%= f.label :customer_id %><%= f.select(:customer_id, @customers.map {|customer| [customer.name, customer.id]}, {:include_blank => 'None'})%></li>
      <li><%= f.submit 'Save', :class => 'submit' %></li></ul>
      <% end %>
  </div>
  <a class="activate_project button" data-object="project" data-id="<%= project.id.to_s %>">Archive</a>
</li>
<% end %>

Upvotes: 2

Views: 4089

Answers (3)

Sia
Sia

Reputation: 9212

Since no one has mentioned it yet, the bullet gem is excellent for identifying pages in your apps where you inadvertently created N+1 queries (your specific case) that could benefit from eager loading (using includes as well-outlined in the accepted answer) and counter-caching.

This great post outlines both the problems and solutions in simple, clear language as well as giving an intro to using the bullet gem. There is also a RailsCast on implementing bullet though I have not gone through it.

Upvotes: 0

ajooba
ajooba

Reputation: 110

You can use a new released gem 'query_optimizer' now . query_optimizer is the best gem for optimizing query in rails for has_many and belongs_to relationship two tables

Upvotes: 1

Sean Hill
Sean Hill

Reputation: 15056

Project load is only taking 2.3ms. The time to render projects/_index is 1 second. The query is not your bottleneck.

Based on your comment, you say that you're lazy loading the relationships. Make sure that you eager load the relationships by using includes.

For example:

@user.projects.is_active.includes(:some_association).includes(:another_association)

includes will cause the relationship to be eager loaded.

If you're iterating over a list of users, to get the active projects, you'll need to do something like:

User.includes(:projects)
     .merge(Project.is_active)
     .includes(projects: :some_other_association)

It is good practice to not put DB queries in your views. Try to do that via the controller.

Upvotes: 6

Related Questions