I wonder, is StackExchange hiring SQL programmers? I know how to get Unique values from a table!
Yes, we are!...but that's beside the point.
The issue here isn't solved by a
Distinct() or any other simple means, because it's a high speed transactional problem. The load on a single SQL server is kept down via dirty reads using a
READ UNCOMMITTED isolation level. The issue comes into play when thinking about how the leagues are generated.
Once a day we hit an API on the requested site that returns a massive list of users for that site along with their rep, ranks, and deltas for that period. Think about what this means on Stack Overflow: Summing up every reputation event of 96,566 users (200+ rep) and doing these ranks. But that aggregate query is done on our RepHistory table which contains records for all 723,181 users with reputation changes totaling 34,941,129 rows as of about 5 seconds ago. None of those values are what I consider large, but they are significant and rapidly changing.
We can't do summations like this with 100% accuracy without locking the table, that's why READ UNCOMMITTED is used in our high transaction environment.
That's the why. What do we plan to do about it? I'll probably add a RepHistoryDaily table or some such that already sums up the day since that has multiple uses besides making these queries simpler. That's the off-the-top-of-my-head plan. But, we're coming up on some deadlines for moving our primary datacenter, so this will have to wait a while since that takes top priority and there's a lot of work left to get it done.