Optimize ruby on rails SQL queries

Question

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 %>

Project load is only taking 2.3ms. The time to render projects/_index is 1 second. The query is not your bottleneck. Or maybe I'm wrong... I've been up all night. — Sean Hill
– Sean Hill, Commented Dec 14, 2012 at 12:38
Could you show me the view the is being rendered and the controller, if possible? — Sean Hill
– Sean Hill, Commented Dec 14, 2012 at 12:42
The query takes place when the view gets rendered. It's lazy loading. Updating with view code. — Andreas Lyngstad
– Andreas Lyngstad, Commented Dec 14, 2012 at 13:20

Andreas Lyngstad · Accepted Answer · 2012-12-14 13:51:04Z

6

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.

edited Dec 14, 2012 at 13:51

Andreas Lyngstad

4,9372 gold badges39 silver badges70 bronze badges

answered Dec 14, 2012 at 13:24

Sean Hill

15.1k2 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Valery Kvon Over a year ago

+ 1 agreed. :includes would be faster and don't init DB request in the view. ActionView - is one big bottleneck of whole Rails. imho.

Andreas Lyngstad Over a year ago

+1 agreed Use includes whenever I can. Turns out the query was OK. It was a rendering problem. I have updated question. Meta; Should I answer my own question and accept it. No one could answer this correct based on my question. This is a really good answer even though it didn't solve the problem

Sean Hill Over a year ago

You can probably just answer your own question. I briefly looked at the view, and nothing stood out at me on first glance.

Andreas Lyngstad Over a year ago

If I remove the form_for I am down at 100ms. I edited you answer with your first comment on top. When approved I'll accept your answer.

Andreas Lyngstad Over a year ago

Yes I was also looking at that too. Thank you too, sir! No get some sleep!

|

ajooba · Accepted Answer · 2015-10-29 07:49:07Z

1

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

edited Oct 29, 2015 at 7:49

answered Oct 21, 2015 at 8:07

ajooba

1106 bronze badges

3 Comments

Drenmi Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

ajooba Over a year ago

Yes the link is related to question user can get all project and its memberships in single query as QueryOptimizer.optimize_query(Project,Membership)

ajooba Over a year ago

More accurate QueryOptimizer.optimize_query(Project,Membership).select {|user| user["users_id"] == "#{current_user.id}" }

Sia · Accepted Answer · 2016-01-08 22:01:16Z

0

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.

answered Jan 8, 2016 at 22:01

Sia

9,2416 gold badges33 silver badges52 bronze badges

Collectives™ on Stack Overflow

Optimize ruby on rails SQL queries

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related