Optimizing find on join table

Rails
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

On 11 Aug 2008, at 22:35, Rune Soerensen wrote:

Basically what I do is call player in @players.find(:all) and apply
matches.each do |score|
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