
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) May 28th, 2009 @ 07:19 PM
- State changed from unconfirmed to accepted
- Assigned user set to 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.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »