[Nitro] OG vs Active Record

[Bill Kelly]

From: "Robert Mela" <rob at robmela.com>
>
> If you want speed you need to be very careful about how you use ORMs 
> with large datasets.
> 
> I recently found an ActiveRecord retrieval that made 8 thousand 
> individual database calls totally 4 megabytes of generated SQL.   All 
> calls were to the same, self-referential table.

When I've run into issues like this with Og, I've just written a
custom SQL query.

This particular example doesn't exactly parallel your problem, but
it does show using refers_to to keep the data normalized; and shows
how to ask Og for particular table names and such needed to construct
the custom query:


class IPHost
  property :ip, String, :unique => true
  property :hostname, String
end

class Playername
  property :playername, String, :unique => true
end

class Servername
  property :servername, String, :unique => true
end

class SuicidesAllTime
  property :method, String      # ex: mg, cg, trap, phalanx, lava, squished, cratered, drowned
  property :date, Date
  property :count, Integer
  refers_to :servername, Servername
  refers_to :victim, Playername

  def self.top_suicides_list(victim_name_str, method_str, servername_str, date=Date.today, limit=10)
    date = get_insert_date(date)
    __total__ = STATS_TOTAL_NAME
    
    @playername_total ||= Playername.find_or_create_by_playername(__total__)
    @servername_total ||= Servername.find_or_create_by_servername(__total__)
    date_str = "#{date.year}-#{date.month}-#{date.mday}"

    if victim_name_str.nil?
      victim = nil
    elsif victim_name_str == __total__
      victim = @playername_total
    else
      victim = PlayerSeen.find_closest_playername(victim_name_str)
      return [] unless victim
    end
    
    if servername_str.nil?
      servername = nil
    elsif servername_str == __total__
      servername = @servername_total
    else
      servername = Servername.find_by_servername(servername_str)
      return [] unless servername
    end

    ptotal = @playername_total
    stotal = @servername_total

    sql =
      "SELECT P1.playername AS victim, " +
             "FRAG.method, SV.servername AS server, FRAG.count " +
        "FROM #{self.table} FRAG, #{Playername.table} P1, " +
             "#{Servername.table} SV " +
       "WHERE " +
         (victim ? "FRAG.victim_oid = #{victim.oid}" : "FRAG.victim_oid <> #{ptotal.oid}") +
        " AND " + (servername ? "FRAG.servername_oid = #{servername.oid}" : "FRAG.servername_oid <> #{stotal.oid}") +
        " AND " + (method_str ? "FRAG.method = '#{method_str}'" : "FRAG.method <> '#{__total__}'") +
        " AND FRAG.date = '#{date_str}' " +
         "AND P1.oid = FRAG.victim_oid " +
         "AND SV.oid = FRAG.servername_oid " +
         "ORDER BY FRAG.count DESC"
    sql << " LIMIT #{limit}" if limit
    res = ogstore.query(sql)
    rows = []
    res.each_row {|row,dummy| rows << row}
    rows
  end

  # ...

end


Dunno if this is useful to anyone; but so far it's been possible for 
me to use Og to do most of the work, and just write custom queries as
I need them.


Regards,

Bill