Ruby on Rails SQL Optimizations

Question

Lets assume:

Network has_many Channels Network has_many Nicknames

Channel has_many Messages Channel belongs_to Network

Nickname has_many Messages Nickname belongs_to Network

Additionally:

Channel has_many Nicknames, :through => :messages Nicknamehas_many Channel, :through => :messages

I have this method here that logs messages. Very straightforward, it'll initialize it and link it to the needed associated models.

def log_msg(userinfo, target, message, type = nil, time = nil)
  # methods not shown yet obvious
  return unless chan = find_channel(target) 
  return unless nick = find_nickname(userinfo)

  msg = Message.new(:message => message)         

  msg.created_at = time unless time.nil?

  if !type.nil? && msg.respond_to?(type)
    msg.send("#{type}=", true)
  end

  msg.channel = chan
  msg.nickname = nick

  msg.save
end

Here are all the queries the above code generates.

  Channel Load (0.0ms) SELECT * FROM "channels" WHERE ("channels"."name" = '#main_chat') AND ("channels".network_id = 1) LIMIT 1←[0m

  Nickname Load (0.0ms) SELECT * FROM "nicknames" WHERE ("nicknames"."nickname" = 'bob_saget') LIMIT 1

  Channel Load (0.0ms) SELECT "channels".id FROM "channels" WHERE ("channels"."name" = '#main_chat' AND "channels".network_id = 1 AND "channels".id <> 1) LIMIT 1

  Network Load (0.0ms) SELECT * FROM "networks" WHERE ("networks"."id" = 1)

  Message Create (0.0ms) INSERT INTO "messages" ("message", "is_action", "channel_id", "is_part", "updated_at", "is_nick", "is_mode", "is_topic", "is_ban", "nickname_id", "is_init", "is_quit", "is_join", "is_kick", "created_at") VALUES(NULL, NULL, 1, NULL, '2009-09-01 20:21:47', NULL, NULL, NULL, NULL, 1, 't', NULL, NULL, NULL, '2009-09-01 20:21:47')

As you can see it is querying the Channel table twice. I am just curious why this is happening. When I first find the channel it is doing "SELECT *". It seems unnecessary to query again for just it's ID since it's already known. Likewise, why is it also querying for the Network table. That also seems unneeded since anything Network related isn't needed when creating new Message records.

Answer 1

ActiveRecord is selecting against channels twice because the two "Channels Load" queries are different. They're serving different purposes.

The first query is your find_channel method, and the second query is almost certainly from the following validations:

class Channel < ActiveRecord::Base
  validates_uniqueness_of :name, :scope => :network_id
  validates_associated :network
end

class Message < ActiveRecord::Base
  validates_associated :channel
end

Saving the Message triggers the Channel validations, and this query...

Channel Load (0.0ms) SELECT "channels".id FROM "channels" WHERE ("channels"."name" = '#main_chat' AND "channels".network_id = 1 AND "channels".id <> 1) LIMIT 1

...and then triggers validating the Network, and this query...

Network Load (0.0ms) SELECT * FROM "networks" WHERE ("networks"."id" = 1)

Seems to be working as intended. However, you can certainly cut out these extraneous queries by not relying on ActiveRecord to validate the presence of associated models.

Take advantage of foreign keys in your database to ensure that your Messages and Channels (and Nicknames) are associated with records that actually exist. Then, you can get rid of the queries by replacing your validates_associated with validates_presence_of :association_id.