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.