Data aggregation, client vs server side?

Hello

I have few typical models:

class Team < AR::Base
  has_many :games
end

class Game < AR::Base
  belongs_to :team
  has_many :events
end

etc..

When I list the team.games I want to be able to aggregate some of the
data (games won, lost etc). I could do this using scopes, but there are
so many different calculations it would result in a lot of queries.

As I am already fetching all the team games in one DB call, should I be
doing the calculations using just plain ruby on the fly, or even on the
client side using JSON/JS? Or is it better in fact to push everything
through SQL.

I'm not sure of the best plan of attack to do these calculations given
that they could well involve pulling data from the children of each game
as well (e.g events).

Many thanks in advance for any insight. I am pulling my hair out here!

Cameron

Cameron Caine wrote in post #965772:

Hello

I have few typical models:

class Team < AR::Base
  has_many :games
end

class Game < AR::Base
  belongs_to :team
  has_many :events
end

etc..

When I list the team.games I want to be able to aggregate some of the
data (games won, lost etc). I could do this using scopes, but there are
so many different calculations it would result in a lot of queries.

As I am already fetching all the team games in one DB call, should I be
doing the calculations using just plain ruby on the fly, or even on the
client side using JSON/JS? Or is it better in fact to push everything
through SQL.

Use SQL as it was meant to be used! The DB can do the aggregate
calculations faster than Rails could. You can even have the aggregates
returned in the same query with everything else (though I'm not sure I'd
advise that).

ActiveRecord::Calculations provides a nice interface to SQL aggregate
functions (at least in Rails 2 -- Arel may take care of this in Rails
3).

I'm not sure of the best plan of attack to do these calculations given
that they could well involve pulling data from the children of each game
as well (e.g events).

What are the calculations that you currently need?

Many thanks in advance for any insight. I am pulling my hair out here!

Cameron

Best,

Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?

Things I need so far as is results like:

Win Count
Loss Count
Draw Count
Win %
Home Wins %
Away Wins %
Scoring Average

The list goes on and on and into nested models in the game:

Thanks

Marnen Laibow-Koser wrote in post #965775:

Please avoid top-posting.

Cameron Caine wrote in post #965778:

Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?

I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.

Things I need so far as is results like:

Win Count
Loss Count
Draw Count
Win %
Home Wins %
Away Wins %
Scoring Average

Yes? And how are these calculated?

The list goes on and on and into nested models in the game:

Best,

cameron, maybe u can throw in a kinda of a team-statistics table which holds all the aggregated values? and update / insert them once u enter the results of a game…
not always 3rd-DB-Form, but often preferrable.
regards tom

Marnen Laibow-Koser wrote in post #965782:

Please avoid top-posting.

Cameron Caine wrote in post #965778:

Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?

I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.

Do you know of any resource I could read up on returning aggregates
along with the existing rows? I've never seen that before is all.

Things I need so far as is results like:

Win Count
Loss Count
Draw Count
Win %
Home Wins %
Away Wins %
Scoring Average

Yes? And how are these calculated?

So far the win/loss is calculated from the field of 'home_score' and
'away_score'
I could easily add an outcome integer column (0,1,2) to get an index on
it.

so it could be like

class Game < AR::Base
  scope :win, where(:outcome => 0)
  @team.games.win
end

Not sure if I could chain a sum/average onto that. Will have to look
into it.

Tom Tom wrote in post #965788:

cameron, maybe u can throw in a kinda of a team-statistics table which
holds
all the aggregated values? and update / insert them once u enter the
results
of a game...
not always 3rd-DB-Form, but often preferrable.
regards tom

The problem is that the team stats will be calculated based on different
date ranges and therefore I can't really cache a specific set. The
individual game stats might be more feasible.

thanks anyway
C

Cameron Caine wrote in post #965790:

Marnen Laibow-Koser wrote in post #965782:

Please avoid top-posting.

Cameron Caine wrote in post #965778:

Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?

I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.

Do you know of any resource I could read up on returning aggregates
along with the existing rows? I've never seen that before is all.

Any good SQL reference will show you how to do this. It will probably
be somewhat less efficient if you have a lot of rows, because it has to
put the aggregate value in each returned record, whereas if you did it
in a separate query, it wouldn't have to return all that redundant data.

Things I need so far as is results like:

Win Count
Loss Count
Draw Count
Win %
Home Wins %
Away Wins %
Scoring Average

Yes? And how are these calculated?

So far the win/loss is calculated from the field of 'home_score' and
'away_score'
I could easily add an outcome integer column (0,1,2) to get an index on
it.

That's a performance hack, and you probably don't need it: it should be
very fast to have the DB calculate the difference of the two values on
the fly.

so it could be like

class Game < AR::Base
  scope :win, where(:outcome => 0)
  @team.games.win
end

Not sure if I could chain a sum/average onto that. Will have to look
into it.

Chain it on? What do you mean?

Best,

I would advise against storing calculated values in the database at
the start. It adds complexity to the code and subtle bugs can be
introduced that may not be seen immediately. If performance becomes
an issue at some point in the future then that is the time to do it.
Though even then it may not be the best way to improve performance.
Bottlenecks in apps are rarely at the points that were imagined at the
design stage.

Colin

Marnen Laibow-Koser wrote in post #965796:

Cameron Caine wrote in post #965790:

Marnen Laibow-Koser wrote in post #965782:

Please avoid top-posting.

Cameron Caine wrote in post #965778:

Thanks this certainly helps. Any reason in particular why you don't
recommend getting aggregates in the same query?

I thought about that again after I posted it. It's less easy to use
ActiveRecord's abstraction layer, and you may be getting a lot of data
you don't need if you pack everything into the one query. But it's
probably not as inadvisable as I had originally implied.

Do you know of any resource I could read up on returning aggregates
along with the existing rows? I've never seen that before is all.

Any good SQL reference will show you how to do this. It will probably
be somewhat less efficient if you have a lot of rows, because it has to
put the aggregate value in each returned record, whereas if you did it
in a separate query, it wouldn't have to return all that redundant data.

I think this might come in handy in this case. Either way I am going to
go dive into some code.
Many thanks for your help. This has really helped my thinking on the
issue.