i have reasonably complex application (over 30 tables) has persistent n+1 issues, , i'm presuming because not doing 'the rails way™'
i'm going give , example of 1 of more complicated ones. 1 includes 4 tables: clins, positions_tasks, tasks , labor_hours.
positions_tasks three-way many-to-many join between clins, positions(which isn't needed example), , tasks, , has_many :labor_hours. labor_hours table has integer field each month of year , other data. method, total_hours, sums of months total hours year. in clin view, displays clin information , table of associated tasks [with other related data], , sums hours each of tasks, has_many :labor_hours, :through => :positions_tasks. eager load of relevant tables, including labor_hours, , of n+1 problems went away except labor_hours.
code snippets follow.
the clins_controller's eager load:
@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 |t| %> <%= f.fields_for :task, t |builder| %> <%= render "tasks/task_row", f: builder %> <% end %> <% end %> </tbody> the _task_row partial:
<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> the clin 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 labor hours 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 the positionstask 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 the task 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 the , sql loads:
started "/clins/11/edit" 127.0.0.1 @ 2015-07-20 17:48:49 -0400 processing clinscontroller#edit html parameters: {"id"=>"11"} clin load (0.2ms) select "clins".* "clins" "clins"."id" = $1 limit 1 [["id", 11]] proposal load (0.2ms) select "proposals".* "proposals" "proposals"."id" in (1) positionstask load (0.4ms) select "positions_tasks".* "positions_tasks" "positions_tasks"."clin_id" in (11) task load (0.6ms) select "tasks".* "tasks" "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".* "labor_hours" "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".* "wbs_line_items" "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".* "wbss" "wbss"."id" in (1) clin load (0.2ms) select "clins".* "clins" rendered tasks/_task_header.html.erb (0.0ms) laborhours load (0.4ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 1]] rendered tasks/_task_row.erb (2.6ms) laborhours load (0.3ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 2]] rendered tasks/_task_row.erb (1.7ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 3]] rendered tasks/_task_row.erb (1.4ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 5]] rendered tasks/_task_row.erb (1.3ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 6]] rendered tasks/_task_row.erb (1.4ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 7]] rendered tasks/_task_row.erb (1.5ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 8]] rendered tasks/_task_row.erb (1.3ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 9]] rendered tasks/_task_row.erb (1.3ms) laborhours load (0.4ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 10]] rendered tasks/_task_row.erb (1.9ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 11]] rendered tasks/_task_row.erb (1.5ms) laborhours load (0.4ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 12]] rendered tasks/_task_row.erb (2.2ms) laborhours load (0.5ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 14]] rendered tasks/_task_row.erb (2.6ms) laborhours load (0.4ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 15]] rendered tasks/_task_row.erb (2.2ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 16]] rendered tasks/_task_row.erb (1.5ms) laborhours load (0.3ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 17]] rendered tasks/_task_row.erb (1.9ms) laborhours load (0.3ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 18]] rendered tasks/_task_row.erb (1.6ms) laborhours load (0.3ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 20]] rendered tasks/_task_row.erb (1.9ms) laborhours load (0.2ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "positions_tasks"."task_id" = $1 [["task_id", 23]] rendered tasks/_task_row.erb (1.6ms) laborhours load (0.3ms) select "labor_hours".* "labor_hours" inner join "positions_tasks" on "labor_hours"."positions_task_id" = "positions_tasks"."id" "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 happening eager loading getting lost in distinct.each , fields_for in _form.html.erb because passing task objects instead of clin object, and/or call total_hours causing load each object called on, i'm not sure how determine is, nor how resolve them.
how provide summed task.labor_hours.total_hours each task in table without n+1 loading of labor_hours?
i'm not certain, i've theory. have (i've removed includes calls don't concern now:
@clin = clin.includes(:tasks).includes(:labor_hours).find(params[:id]) what you're doing here eager-loading tasks associated each clin , laborhours associated each clin—so far, good, in view you're doing (more or less):
@clin.tasks.distinct.each |task| # inside partial... task.labor_hours... end here you're not accessing laborhours associated each clin—which eager-loaded—you're accessing laborhours associated each task associated each clin. access laborhours associated each clin, you'd have this:
@clin.labor_hours.each |labor_hour| # ... end but since rendering tasks (and not laborhours), don't think that's want. instead, need tell rails want eager-load second-order association—i.e. laborhours associated tasks, not laborhours associated clins—by passing hash includes:
@clin = clin.includes(:tasks => :labor_hours).find(params[:id]) p.s. there's additional improvement make—for example, looks you're not using of attributes laborhours, you're using sum of total_hours column. calculating sum in ruby waste when can let database it. however, that's beyond scope of answer.
Comments
Post a Comment