0

I have a UserActivities table

CREATE TABLE [dbo].[UserActivities](
    [UserId] [int] NOT NULL,
    [Points] [int] NOT NULL,

which could have 100k+ users, each user having 1k+ activities. I would like to be able to give query a user ID and it tells me the position.

Right now, do i have to get list all users using the below query, then filter out for my specific user? or is there a better way to get position for a specific user

SELECT
    UserId,
    SUM(Points) AS TotalPoints,
    RANK() OVER (ORDER BY SUM(Points) DESC) AS UserRank
FROM
    UserActivities
GROUP BY
    UserId
ORDER BY
    TotalPoints DESC;
2
  • What indexes do you currently have defined on your UserActivities table? What does the execution plan say about your current query?
    – J.D.
    Dec 31, 2023 at 1:40
  • I can put any index needed on there, there isnt any right now other than the PK cluster one. As for the plan, I was more asking if i have to retrieve all users and their points then look for the one I want or if there is a way to just get a single player and their position instead of get all users, then re-query for specific
    – Zoinky
    Dec 31, 2023 at 1:44

1 Answer 1

2

Working this out on the fly for an individual user is quite expensive.

If you have 100,000 users - each with 1,000 rows - then you are grouping 100 million rows each time to find a single user's rank.

Potentially you could use an indexed view to help with this

CREATE OR ALTER VIEW dbo.UserAggregatedPoints
WITH SCHEMABINDING AS
SELECT UserId,
       SUM(Points) AS TotalPoints,
       COUNT_BIG(*) AS Count
FROM [dbo].[UserActivities]
GROUP BY UserId

GO

CREATE UNIQUE CLUSTERED INDEX CUIX_UserId ON UserAggregatedPoints(UserId);

CREATE INDEX IX_Points ON UserAggregatedPoints(TotalPoints) INCLUDE (UserId);

This at least saves having to aggregate up the 1k+ activities for each user every time. And by pre-calculating the aggregates it can be indexed allowing the rank to be calculated by reading in index order.

Your query could then be

WITH Ranked AS
(
SELECT
    UserId,
    TotalPoints,
    RANK() OVER (ORDER BY TotalPoints DESC) AS UserRank
FROM
    dbo.UserAggregatedPoints WITH (NOEXPAND)
)
SELECT TOP 1 /*No need to read any additional rows after we have got to the one for the user*/ *
FROM Ranked
WHERE UserId = @UserId 
ORDER BY TotalPoints DESC

For 100,000 users then on average this should end up reading ~50,000 rows for a random user (if the TotalPoints is well distributed) but with no need to do any sorting on them.

The indexed view will be maintained automatically on inserts/updates/deletes to UserActivities - you will have to evaluate whether the benefit to this query outweighs the cost of this for those writes.

6
  • That would get really expensive really fast, I think I might just only get top 100 users and if that user is in that list great, otherwise dont show their position at this point Thank you for all info
    – Zoinky
    Dec 31, 2023 at 4:18
  • @Zoinky "That would get really expensive really fast" - How so?...what criteria are you using to come to that conclusion?
    – J.D.
    Dec 31, 2023 at 5:38
  • @J.D. I was referring to the comment about 100m groupings, and the indexed view would get slow as there would be be a lot of writes to that table. I might enable the exact player position when initially there isnt that many users as proposed by the indexed view, as more users/activities are added, i assume this query would get slower and slower even if its optimized
    – Zoinky
    Dec 31, 2023 at 6:32
  • 1
    @Zoinky "referring to the comment about 100m groupings" - That's in regards to what you're currently doing with your example query. It would be slow to do that every time on the fly. The Indexed View materializes the results (like a table) so it's not doing that every time. "as more users/activities are added, i assume this query would get slower" - Not really. You're paying a small amount of extra time to initially write the data to the table when new data is added so that whenever you read from the Indexed View, this query is relatively faster. You'd have to test to see if it's enough.
    – J.D.
    Dec 31, 2023 at 13:13
  • @Zoinky - you need to test - not assume Dec 31, 2023 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.