#831 ✓resolved
nitsujw

has 1, N+1 problem

Reported by nitsujw | April 11th, 2009 @ 12:14 PM

If you have an association setup such as

class Action ... has 1, :action_user ... end

class ActionUser ... belongs_to :action ... end

In the controller @actions = Action.all

In the view - @actions.each do |a| = a.action_user

You get the following

~ (0.000196) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (4, 17, 1, 10, 23, 16, 13, 22, 6, 19, 3, 12, 25, 9, 18, 2, 24, 8, 21, 5, 14, 11, 20)) ORDER BY id

~ (0.000061) SELECT body, id FROM actions WHERE (restaurant_id = 18) AND (id IN (25, 24, 23, 22, 21, 20, 19, 18, 17, 16, 14, 13, 12, 11, 10, 9, 8, 6, 5, 4, 3, 2, 1)) ORDER BY created_at DESC LIMIT 25

~ (0.000074) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (24)) ORDER BY id LIMIT 1 ~ (0.000072) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 23) ORDER BY id LIMIT 1 ~ (0.000106) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 22) ORDER BY id LIMIT 1 ~ (0.000064) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (4, 17, 1, 10, 23, 16, 13, 22, 6, 19, 3, 12, 25, 9, 18, 2, 24, 8, 21, 5, 14, 11, 20)) ORDER BY id ~ (0.000099) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (21)) ORDER BY id LIMIT 1 ~ (0.000094) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 20) ORDER BY id LIMIT 1 ~ (0.000103) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 19) ORDER BY id LIMIT 1 ~ (0.000060) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (4, 17, 1, 10, 23, 16, 13, 22, 6, 19, 3, 12, 25, 9, 18, 2, 24, 8, 21, 5, 14, 11, 20)) ORDER BY id ~ (0.000049) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (18)) ORDER BY id LIMIT 1 ~ (0.000096) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (4, 17, 1, 10, 23, 16, 13, 22, 6, 19, 3, 12, 25, 9, 18, 2, 24, 8, 21, 5, 14, 11, 20)) ORDER BY id ~ (0.000069) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (17)) ORDER BY id LIMIT 1 ~ (0.000224) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (4, 17, 1, 10, 23, 16, 13, 22, 6, 19, 3, 12, 25, 9, 18, 2, 24, 8, 21, 5, 14, 11, 20)) ORDER BY id ~ (0.000259) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (16)) ORDER BY id LIMIT 1 ~ (0.000091) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (4, 17, 1, 10, 23, 16, 13, 22, 6, 19, 3, 12, 25, 9, 18, 2, 24, 8, 21, 5, 14, 11, 20)) ORDER BY id ~ (0.000082) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id IN (14)) ORDER BY id LIMIT 1 ~ (0.000080) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 13) ORDER BY id LIMIT 1 ~ (0.000089) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 12) ORDER BY id LIMIT 1 ~ (0.000228) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 11) ORDER BY id LIMIT 1 ~ (0.000161) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 10) ORDER BY id LIMIT 1 ~ (0.000099) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 9) ORDER BY id LIMIT 1 ~ (0.000071) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 8) ORDER BY id LIMIT 1 ~ (0.000088) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 6) ORDER BY id LIMIT 1 ~ (0.000058) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 5) ORDER BY id LIMIT 1 ~ (0.000098) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 4) ORDER BY id LIMIT 1 ~ (0.000081) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 3) ORDER BY id LIMIT 1 ~ (0.000114) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 2) ORDER BY id LIMIT 1 ~ (0.000058) SELECT id, name, state, city, address, action_id, tip FROM action_users WHERE (action_id = 1) ORDER BY id LIMIT 1

If you change has 1, :action_user to has n, :action_users then call action.action_user.first, you get the same result with many single calls to the DB. However if you do action.action_users.each do |au| (Thanks to Derek from the mailing list for pointing out a temp fix) it acts correct and all those single DB hits are gone.

Comments and changes to this ticket

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) May 28th, 2009 @ 07:19 PM

    • State changed from “unconfirmed” to “accepted”
    • Assigned user set to “Dan Kubb (dkubb)”
  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) June 7th, 2009 @ 02:03 AM

    • State changed from “accepted” to “resolved”

    I've completely rewritten strategic eager loading in dm-core/next and specced has 1 specifically so it should be working. Marking this ticket as resolved.

    I would suggest trying out the dm-core/next branch and please add a comment if the problem persists -- I will reopen the ticket right away.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Pages