I'll think about it.

It would definitely be something done every X minutes. The problem is that for each player the query has to look back over all opponents in all games for the month for each player. I do these bulk stats every 10 minutes and account only for players who played in the last 10 minutes. This is usually hundreds of players. Take an end of month example, 300 players in ten minutes * 100 average games for the month * 7 players per game. Thats around 200k values for each of the three queries so 600k values total. And the values need to be ordered by in memory calculations ie no indexes.

I'm not saying its not possible, I just have to figure out how to do it in an efficient way.

As a java developer, i can't even believe I'm saying this, but maybe farm it out to a stored procedure and order it there.

All the stats are pure batch SQL. I'm talking about doing this stuff with pure SQL. The calculations aren't complicated enough to do a stored procedure. It's just a lot of rows, which means a lot of disk reads, and a lot of per row calculation and ordering, which is a lot of CPU.

I think the solution would have to do a cumulative total so you wouldn't need to look back the entire month. The downside to this is you have to maintain NxN rows of data to remember relationships between each player - which has a worst case of 62 trillion rows.

how about once per day, the first time a person plays in a given day. (and once at the beginning of the month for everyone).

Yeah, having thought about this tonight I think that doing the cumulative total sounds like it would be a lot less intense, but the search times might be quite ugly.

I will run this issue by some former coworkers, we had to deal with a similar issue matching up financial transactions. I can't remember what we settled on.

btw n^2 = 64 trillion? I don't think you have 8 million players.

And... and I'm *really* sorry about this, but the nerd in me won't let it go... it's really (N * N-1) because people wouldn't be paired with themsleves!

No, wait.

It's (N * N-1) / 2 because in this case A-C is the same as C-A.

Or is it? I'm not sure. Do people have a combined rating?

I am not a programmer, but if as Kdicefreak says, not many people look at profile pages, then why not only generate these stats when the profile is looked at?


That would cut down the daily calculations by several orders of magnitude. Or put these stats on a page of their own, linked to from the profile page, so that they are only generated for people who really want to see them.

If that page took several seconds to generate at a low nice rating, I think that would still be acceptable.

This would be useful as a test of the new stats as not everyone will know about or want to check them straightaway, and you can tweak the code without committing yourself to calculating the stats for all players.