Eager Loading, Model methods, fields_for, each and N + 1
I have a fairly complex application (over 30 tables) that has some persistent N + 1 problems, and I guess that's because I am not doing something "The Rails Way ™"
I will give an example of one of the more complex ones. This includes four tables: clins, positions_tasks, tasks
and labor_hours
.
positions_tasks
is a many-to-many three-way connection between clins, positions
(not required for this example) and tasks
, and this has_many :labor_hours
. The labor_hours table has a whole field for each month of the year and some other data. The method total_hours
sums all months by the total number of hours in a year. In the clinic view, it displays information about the clinic and a table of all related tasks [with other related data] and sums up the hours for each of the tasks that has_many :labor_hours, :through => :positions_tasks
. I am eagerly downloading all relevant tables, including labor_hours and all N + 1 issues are gone except labor_hours.
Below are the code snippets.
Expected loading of clins_controller:
@clin = Clin.includes(:proposal).includes(:positions_tasks).includes(:tasks).includes(:labor_hours).includes(:wbss).find(params[:id])`
Displaying table rows inside clins / _form.html.erb:
<tbody>
<% @clin.tasks.distinct.each do |t| %>
<%= f.fields_for :task, t do |builder| %>
<%= render "tasks/task_row", f: builder %>
<% end %>
<% end %>
</tbody>
Partially _task_row:
<tr>
<td><%= f.object.wbs_line_item.wbs.wbs_title %></td>
<td><%= f.object.wbs_line_item.wbs_line_item %></td>
<td><%= f.object.description %></td>
<td><%= f.object.labor_hours.distinct.each.sum(&:total_hours) %>
<td><div id="jump">
<%= link_to "Edit", {:controller => :tasks, :action => :edit, :id => f.object.id } %>
</div></td>
</tr>
Clinic model:
class Clin < ActiveRecord::Base
nilify_blanks
belongs_to :proposal
belongs_to :parent, :class_name => "Clin"
has_many :children, :class_name => "Clin"
has_many :positions_tasks
has_many :labor_hours, :through => :positions_tasks
has_many :tasks, :through => :positions_tasks
has_many :wbs_line_items, :through => :tasks
has_many :wbss, :through => :wbs_line_items
has_many :pws_line_items, :through => :wbs_line_items
has_many :pwss, :through => :wbss
end
Working time model:
class LaborHours < ActiveRecord::Base
nilify_blanks
belongs_to :positions_task
belongs_to :year
has_one :proposal, :through => :positions_task
has_many :valid_years, :through => :proposal, :source => :years
def total_hours
m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12
end
end
Positional model:
class PositionsTask < ActiveRecord::Base
nilify_blanks
belongs_to :task
belongs_to :position
belongs_to :clin
has_many :labor_hours
has_one :company, :through => :position
has_one :proposal, :through => :clin
has_one :wbs_line_item, :through => :task
delegate :wbs, :to => :wbs_line_item
delegate :pws_line_items, :to => :wbs_line_item
delegate :pwss, :to => :wbs_line_item
validates_presence_of :task
validates_presence_of :position
validates_presence_of :clin
accepts_nested_attributes_for :labor_hours, allow_destroy: true
end
Problem model:
class Task < ActiveRecord::Base
nilify_blanks
belongs_to :wbs_line_item
belongs_to :task_category
has_many :positions_tasks
has_many :labor_hours, :through => :positions_tasks
has_many :positions, :through => :positions_tasks
has_many :clins, :through => :positions_tasks
has_many :proposals, :through => :positions_tasks
delegate :wbs, :to => :wbs_line_item
delegate :pws_line_items, :to => :wbs_line_item
delegate :pwss, :to => :wbs
accepts_nested_attributes_for :positions_tasks, allow_destroy: true
accepts_nested_attributes_for :labor_hours, allow_destroy: true
validates_associated :positions_tasks
end
Loading GET and SQL:
Started GET "/clins/11/edit" for 127.0.0.1 at 2015-07-20 17:48:49 -0400
Processing by ClinsController#edit as HTML
Parameters: {"id"=>"11"}
Clin Load (0.2ms) SELECT "clins".* FROM "clins" WHERE "clins"."id" = $1 LIMIT 1 [["id", 11]]
Proposal Load (0.2ms) SELECT "proposals".* FROM "proposals" WHERE "proposals"."id" IN (1)
PositionsTask Load (0.4ms) SELECT "positions_tasks".* FROM "positions_tasks" WHERE "positions_tasks"."clin_id" IN (11)
Task Load (0.6ms) SELECT "tasks".* FROM "tasks" WHERE "tasks"."id" IN (1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 20, 23, 24)
LaborHours Load (1.1ms) SELECT "labor_hours".* FROM "labor_hours" WHERE "labor_hours"."positions_task_id" IN (1, 2, 3, 6, 7, 8, 9, 10, 12, 13, 14, 18, 19, 20, 21, 23, 24, 25, 26, 27, 30, 35, 36, 37)
WbsLineItem Load (0.5ms) SELECT "wbs_line_items".* FROM "wbs_line_items" WHERE "wbs_line_items"."id" IN (310, 312, 314, 316, 317, 318, 319, 413, 320, 321, 322, 324, 325, 326, 327, 328, 330, 333, 334)
Wbs Load (0.4ms) SELECT "wbss".* FROM "wbss" WHERE "wbss"."id" IN (1)
Clin Load (0.2ms) SELECT "clins".* FROM "clins"
Rendered tasks/_task_header.html.erb (0.0ms)
LaborHours Load (0.4ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 1]]
Rendered tasks/_task_row.erb (2.6ms)
LaborHours Load (0.3ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 2]]
Rendered tasks/_task_row.erb (1.7ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 3]]
Rendered tasks/_task_row.erb (1.4ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 5]]
Rendered tasks/_task_row.erb (1.3ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 6]]
Rendered tasks/_task_row.erb (1.4ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 7]]
Rendered tasks/_task_row.erb (1.5ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 8]]
Rendered tasks/_task_row.erb (1.3ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 9]]
Rendered tasks/_task_row.erb (1.3ms)
LaborHours Load (0.4ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 10]]
Rendered tasks/_task_row.erb (1.9ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 11]]
Rendered tasks/_task_row.erb (1.5ms)
LaborHours Load (0.4ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 12]]
Rendered tasks/_task_row.erb (2.2ms)
LaborHours Load (0.5ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 14]]
Rendered tasks/_task_row.erb (2.6ms)
LaborHours Load (0.4ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 15]]
Rendered tasks/_task_row.erb (2.2ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 16]]
Rendered tasks/_task_row.erb (1.5ms)
LaborHours Load (0.3ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 17]]
Rendered tasks/_task_row.erb (1.9ms)
LaborHours Load (0.3ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 18]]
Rendered tasks/_task_row.erb (1.6ms)
LaborHours Load (0.3ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 20]]
Rendered tasks/_task_row.erb (1.9ms)
LaborHours Load (0.2ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 23]]
Rendered tasks/_task_row.erb (1.6ms)
LaborHours Load (0.3ms) SELECT "labor_hours".* FROM "labor_hours" INNER JOIN "positions_tasks" ON "labor_hours"."positions_task_id" = "positions_tasks"."id" WHERE "positions_tasks"."task_id" = $1 [["task_id", 24]]
Rendered tasks/_task_row.erb (1.9ms)
Rendered clins/_form.html.erb (47.6ms)
Rendered clins/_errors.html.erb (0.0ms)
Rendered clins/edit.html.erb within layouts/application (48.6ms)
Rendered layouts/_header.html.erb (60.5ms)
Rendered layouts/_sidenav.html.erb (0.4ms)
Rendered layouts/_footer.html.erb (0.0ms)
Completed 200 OK in 140ms (Views: 106.4ms | ActiveRecord: 8.8ms)
I think what is happening is that the loaded load is lost in distinct.each
and fields_for
in the _form.html.erb because it passes task objects instead of the clinic object and / or that the call to total_hours is causing the load on each callable, but I'm not sure how to determine if what they are and how to resolve them.
How to provide summarized task.labor_hours.total_hours
for each task in a table without N + 1 labor_hours load?
source to share
I'm not sure, but I have a theory. You have this (I removed the calls includes
that are not our concern now:
@clin = Clin.includes(:tasks).includes(:labor_hours).find(params[:id])
What you do here is that you download the Tasks associated with each wedge and the LaborHours associated with each client, so far so good, but in your opinion, you are doing this (more or less):
@clin.tasks.distinct.each do |task|
# inside the partial...
task.labor_hours...
end
This is where you don't get access to LaborHours associated with every blade, and that's what you eagerly download - you get access to LaborHours associated with every Task associated with every Clinic. To access LaborHours associated with each client, you will need to do this:
@clin.labor_hours.each do |labor_hour|
# ...
end
But since you're doing Tasks (not just LaborHours), I don't think you want to. Instead, you need to tell Rails that you want to eagerly load the second-order association - i.e. LaborHours associated with Tasks, not LaborHours associated with Clins, passing the hash to includes
:
@clin = Clin.includes(:tasks => :labor_hours).find(params[:id])
PS There are some additional improvements you could make - for example, it looks like you are not actually using any of the attributes from LaborHours, you are actually just using the column sum total_hours
. But calculating a sum in Ruby is a waste when you can just let the database do it. However, that is beyond the scope of this answer.
source to share