Optimizing find on join table

rubyonrails-talk
1

Hi,

I have a application where i want to rank players according to the goals they have scored. I can actually make the ranking work, but I have performance problems when I try to fetch the total opponent score (goals scored again an user in any match).

Basically what I do is call player in @players.find(:all) and apply the action oppenent_total_score in the players model like this:

class Player < ActiveRecord::Base

  has_many :participations, :class_name => "MatchParticipant"   has_many :matches, :through => :participations

  def opponent_total_score(player)     @opponent_score = 0     matches.each do |score|        player_side = score.participants.find(:last, :conditions => ["player_id = ?", player]).side        opponent_score = score.participants.find(:first, :limit => 10, :conditions => ["side != ?", player_side]).score        @opponent_score = @opponent_score + opponent_score     end     return @opponent_score

  end

end

The problem arises as soon as there are more than 250 matches, where it takes about 0,5 seconds to process the query.

With a database schema looking like this:

  create_table "match_participants", :force => true do |t|     t.integer "player_id", :null => false     t.integer "match_id", :null => false     t.string "side", :null => false     t.integer "score"     t.boolean "winner"   end

  create_table "matches", :force => true do |t|     t.datetime "created_at"     t.datetime "updated_at"   end

  create_table "players", :force => true do |t|     t.string "name"     t.string "login"     t.string "password"     t.string "email"     t.datetime "created_at"     t.datetime "updated_at"   end

end

Where match_participant is a join table of players and matches in has_many and belongs_to relations. One match can have either 2 or 4 participants.

How can i optimize this query to make it possible to search even more matches without performance issues? The problem, as I see it, is that I can't do a clean SQL query, since I have to determine the player's side before I know what score I will have to sum (the opponent's score).

Will I need to go for a different database structure in order to archieve better performance? As this is obviously an very important feature of a player ranking system I would like to make it as effective as possible.

Thanks, Rune

2

Hi,

I have a application where i want to rank players according to the
goals they have scored. I can actually make the ranking work, but I have performance problems when I try to fetch the total opponent score
(goals scored again an user in any match).

Basically what I do is call player in @players.find(:all) and apply
the action oppenent_total_score in the players model like this:

class Player < ActiveRecord::Base

has_many :participations, :class_name => "MatchParticipant" has_many :matches, :through => :participations

def opponent_total_score(player)    @opponent_score = 0    matches.each do |score|       player_side = score.participants.find(:last, :conditions => ["player_id = ?", player]).side       opponent_score = score.participants.find(:first, :limit => 10, :conditions => ["side != ?", player_side]).score       @opponent_score = @opponent_score + opponent_score    end    return @opponent_score

end

end

The problem arises as soon as there are more than 250 matches, where
it takes about 0,5 seconds to process the query.

One of the causes of your slowdown is that for each match you're
making 2 queries, so for 250 matches you make 500 queries. Just the
latency required for a single query (say you can do a query in 1ms)
adds up to 0.5s.

You could do something like

matches_with_participants = matches.find(:all, :include
=> :participants) matches_with_participants.each do |match|    player_side = match.participants.to_a.find {|p| p.player_id =
player.id}.side    opponent_score = match.participants.to_a.find {|p| p.side !=
side }.side    ... end

Note that the find here is a regular ruby array find, not an active
record find.

There is some repetition of the data: the score field is repeated on
all players, if it were more normalized you might have an easier job. For example if you had a sides model describing a side for a match, then

class Side    belongs_to :match    has_many :players, :through => :caps    has_many :opposing_sides, :class_name => 'Side', :conditions =>
'match_id = #{match_id} AND id != #{id}' #the single quotes here are
important end sides also has a score attribute and a winner attribute. In addition, Match has_many :sides.

Then your query could be rewritten as

SELECT SUM(opponents.score) from sides inner join caps on caps.player_id = #{player.id} inner join sides as opponents on opponents.match_id = sides.match_id where opponents.id != sides.id

which you can probably rewrite to use active record stuff if you feel
like it (untested) player.sides.sum(:all, :select => 'opponents.score', :joins => "inner
join sides as opponents on opponents.match_id = sides.match_id".                   :conditions => "opponents.id != sides.id"

which show be pretty nippy as long as you have indexes on the match_id
and player_id (This is just off the top of my head to get the ball rolling - I don't
understand the data you're modelling so there may be things that you
know about that would kill this idea)

Fred