CODEWITHSUNDEEP
sqlruby-on-railsactiverecordruby-on-rails-3.2query-optimization
user664833
user664833

Reputation: 19525

Why are individual SELECT queries running when an all-encompassing SELECT already ran? (Rails/ActiveRecord)

I have the following code (note the includes and the .each):

subscribers = []
mailgroup.mailgroup_members.opted_to_receive_email.includes(:roster_contact, :roster_info).each { |m|
  subscribers << { :EmailAddress => m.roster_contact.member_email,
                   :Name => m.roster_contact.member_name,
                   :CustomFields => [ { :Key => 'gender', 
                                        :Value => m.roster_info.gender.present? ? m.roster_info.gender : 'X' 
                                    } ] 
                  } if m.roster_contact.member_email.present?
}
subscribers

Correspondingly, I see the following in my logs (i.e. select * from ROSTER_INFO ... IN (...)):

SELECT `ROSTER_INFO`.* FROM `ROSTER_INFO` WHERE `ROSTER_INFO`.`ID` IN ('1450', '1000', '1111')

Yet immediately after that there are select * from ROSTER_INFO for each ID already specified in the IN list of the previous query:

RosterInfo Load (84.8ms)  SELECT `ROSTER_INFO`.* FROM `ROSTER_INFO` WHERE `ROSTER_INFO`.`ID` = '1450' LIMIT 1
RosterInfo Load (59.2ms)  SELECT `ROSTER_INFO`.* FROM `ROSTER_INFO` WHERE `ROSTER_INFO`.`ID` = '1000' LIMIT 1
RosterInfo Load (56.8ms)  SELECT `ROSTER_INFO`.* FROM `ROSTER_INFO` WHERE `ROSTER_INFO`.`ID` = '1111' LIMIT 1

If select * had already been done on ROSTER_INFO on all IDs of interest (IN (...)), why is another select * being done again for each of the same IDs? Doesn't ActiveRecord already know all the ROSTER_INFO columns for each ID?

(Meanwhile, there are no individual queries for ROSTER_CONTACT, yet if I remove :roster_contact from the includes method, then ROSTER_INFO is not queried again, but ROSTER_CONTACT is.)

RosterInfo model (abridged)

class RosterInfo < ActiveRecord::Base
  self.primary_key = 'ID'
end

RosterContact model (abridged)

class RosterContact < ActiveRecord::Base
  self.primary_key = 'ID'

  has_many :mailgroup_members, foreign_key: 'rosterID'
  has_many :mailgroups, through: :mailgroup_members

  has_one :roster_info, foreign_key: 'ID'     # can use this line
  #belongs_to :roster_info, foreign_key: 'ID' # or this with no difference

  def member_name                             # I added this method to this
    roster_info.member_name                   # question only *after* having
  end                                         # figured out the problem.
end

RosterWeb model (abridged)

class RosterWeb < ActiveRecord::Base
  self.primary_key = 'ID'
end

Mailgroup model (abridged)

class Mailgroup < ActiveRecord::Base
  self.primary_key = 'ID'

  has_many :mailgroup_members, foreign_key: 'mailCatID'

  has_one :mailing_list, foreign_key: :legacy_id
end

MailgroupMember model (abridged)

class MailgroupMember < ActiveRecord::Base
  self.primary_key = 'ID'

  belongs_to :mailgroup, foreign_key: 'mailCatID'
  belongs_to :roster_contact, foreign_key: 'rosterID'
  belongs_to :roster_info, foreign_key: 'rosterID'
  belongs_to :roster_web, foreign_key: 'rosterID'

  scope :opted_to_receive_email, joins(:roster_web).where('ROSTER_WEB.receiveEmail=?', 1)
end

Upvotes: 8

Views: 266

Answers (3)

user664833
user664833

Reputation: 19525

The issue turned out to be related to m.roster_contact.member_name -- unfortunately I made member_name a method of roster_contact that itself (indirectly) queried roster_info.member_name. I resolved this by changing the line

:Name => m.roster_contact.member_name,

to directly query roster_info as follows

:Name => m.roster_info.member_name,

I am sorry for the trouble!

Upvotes: 1

prusswan
prusswan

Reputation: 7101

class RosterInfo < ActiveRecord::Base
  has_one :roster_contact, foreign_key: 'ID'  
end

class RosterContact < ActiveRecord::Base
  has_one :roster_info, foreign_key: 'ID' 
end

I don't know what is the premise for having bi-directional has_one, but I suspect it will turn out badly. Probably change one of them to belongs_to. Do the same for the other bi-directional has_one associations.

Another thing is that you are using 'ID' for the foreign_key column, where the usual practice is roster_contact_id or whichever class you are referencing.

Edit:

On closer examination, RosterInfo, RosterContact, RosterWeb look like separate tables for what should be a single record since they are all having the same set of mutual has_one associations. This is something that should be addressed on the schema level, but right now you should be able to drop the has_one associations from one of the three models to solve your immediate problem.

enter image description here

Upvotes: 0

MikeAinOz
MikeAinOz

Reputation: 128

I'm going to stick my neck out and say that this is probably an in-flight optimization by your query engine. The 'IN' is typically used to compare large sets of keys, the most efficient way of resolving three keys (assuming ID is the key) would be to retrieve each row by key, as has happened.

Upvotes: 0

Related Questions