Ranking-Page - where to calculate big things (database or code)?

I have a ranking page for players of a new game with a huge database:

• We have one World.
• Each World have X Shards (~200).
• Each Shard have 2 Factions.
• Each Shard have X Guilds.
• A Guild have 1 shard, 1 faction and of course one world.
• A Guild will be ranked by a Kill of a Boss.

I will calculate the Rank of the Guild in World, in shard and in Faction. I only save the points of the BossKills in the database.

So I have a very very huge calculation each time a site wil开发者_运维问答l be opened in which a rank is showed up. How to make this calculation as fast as possible?

My idea is to either make a View, let the View do the calculation and only get back the information or get back all information I need and calculate it in code.

I would definitely do the calculating in your Database. The information is there. Return only the data that needs to be displayed to decrease data transfer and processor time for your code.

Maybe to help you to a different point of view:

If you have data that needs to be shown quite frequently, and has a big calculation cost, consider storing that information in a database column, and create a trigger that updates, or inserts a record in that column every time ( in your case ) a boss gets killed.

tblRank

UserId | Score_in_guild( integer) | ...

SQL Server has all sorts of smarts built into it for data and query caching (also an older article), whereas your app code doesn't. Databases are also generally hosted on servers with a reasonable spec, so if possible do your calculation there. You could also look to use the Ranking functions already built into SQL Server.

How large is the data you'd need to transfer and how computation intensive is the calculation?

The data being large favors calculating database sided since you avoid transferring it all. If you just sum some points and then order by that do it in the db.

The calculation being expensive or complicated favors doing it in your application. Since scaling an application is much easier than scaling the db. And of course it's nicer to write complicated algorithms in code with full debugger and IDE support.

And if it's expensive why are you recalculating whenever a ranking page is shown? Either cache the information for a minute, or just update it whenever a guild gains points.