Leaderboard design using SQL Server

[Updated]

Primary key is not good

You have a unique entity that is [GameID] + [PlayerName]. And composite clustered Index > 120 bytes with nvarchar. Look for the answer by @marc_s in the related topic SQL Server - Clustered index design for dictionary

Your table schema does not match of your requirements to time periods

Ex.: I earned 300 score on Wednesday and this score stored on leaderboard. Next day I earned 250 score, but it will not record on leaderboard and you don't get results if I run a query to Tuesday leaderboard

For complete information you can get from a historical table games played score but it can be very expensive

CREATE TABLE GameLog (  [id]                int NOT NULL IDENTITY                      CONSTRAINT [PK_GameLog] PRIMARY KEY CLUSTERED,  [gameId]            smallint NOT NULL,  [playerId]          int NOT NULL,  [score]             int NOT NULL,  [createdDateTime]   datetime2(3) NOT NULL)

Here are solutions to accelerate it related with the aggregation:

• Indexed view on historical table (see post by @Twinkles).

You need 3 indexed view for the 3 time periods. Potentially huge size of historical tables and 3 indexed view. Unable to remove the "old" periods of the table. Performance issue to save score.

• Asynchronous leaderboard

Scores saved in the historical table. SQL job/"Worker" (or several) according to schedule (1 per minute?) sorts historical table and populates the leaderboards table (3 tables for 3 time period or one table with time period key) with the precalculated rank of a user. This table also can be denormalized (have score, datetime, PlayerName and ...). Pros: Fast reading (without sorting), fast save score, any time periods, flexible logic and flexible schedules. Cons: The user has finished the game but did not found immediately himself on the leaderboard

• Preaggregated leaderboard

During recording the results of the game session do pre-treatment. In your case something like UPDATE [Leaderboard] SET score = @CurrentScore WHERE @CurrentScore > MAX (score) AND ... for the player / game id but you did it only for "All time" leaderboard. The scheme might look like this:

CREATE TABLE [Leaderboard] (    [id]                int NOT NULL IDENTITY                             CONSTRAINT [PK_Leaderboard] PRIMARY KEY CLUSTERED,    [gameId]            smallint NOT NULL,    [playerId]          int NOT NULL,    [timePeriod]        tinyint NOT NULL,   -- 0 -all time, 1-monthly, 2 -weekly, 3 -daily    [timePeriodFrom]    date NOT NULL,  -- '1900-01-01' for all time, '2013-11-01' for monthly, etc.    [score]             int NOT NULL,    [createdDateTime]   datetime2(3) NOT NULL    )

playerId    timePeriod  timePeriodFrom  Score----------------------------------------------1           0           1900-01-01      300  ...1           1           2013-10-01      1501           1           2013-11-01      300...1           2           2013-10-07      1501           2           2013-11-18      300...1           3           2013-11-19      3001           3           2013-11-20      250...

So, you have to update all 3 score for all time period. Also as you can see leaderboard will contain "old" periods, such as monthly of October. Maybe you have to delete it if you do not need this statistics. Pros: Does not need a historical table. Cons: Complicated procedure for storing the result. Need maintenance of leaderboard. Query requires sorting and JOIN

CREATE TABLE [Player] (    [id]    int NOT NULL IDENTITY CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED,    [playerName]        nvarchar(50) NOT NULL CONSTRAINT [UQ_Player_playerName] UNIQUE NONCLUSTERED)CREATE TABLE [Leaderboard] (    [id]                int NOT NULL IDENTITY CONSTRAINT [PK_Leaderboard] PRIMARY KEY CLUSTERED,    [gameId]            smallint NOT NULL,    [playerId]          int NOT NULL,    [timePeriod]        tinyint NOT NULL,   -- 0 -all time, 1-monthly, 2 -weekly, 3 -daily    [timePeriodFrom]    date NOT NULL,  -- '1900-01-01' for all time, '2013-11-01' for monthly, etc.    [score]             int NOT NULL,    [createdDateTime]   datetime2(3) )CREATE UNIQUE NONCLUSTERED INDEX [UQ_Leaderboard_gameId_playerId_timePeriod_timePeriodFrom] ON [Leaderboard] ([gameId] ASC, [playerId] ASC, [timePeriod]  ASC,  [timePeriodFrom] ASC)CREATE NONCLUSTERED INDEX [IX_Leaderboard_gameId_timePeriod_timePeriodFrom_Score] ON [Leaderboard] ([gameId] ASC, [timePeriod]  ASC,  [timePeriodFrom] ASC, [score] ASC)GO-- Generate test data-- Generate 500K unique players;WITH digits (d) AS (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION   SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0)INSERT INTO Player (playerName)SELECT TOP (500000) LEFT(CAST(NEWID() as nvarchar(50)), 20 + (ABS(CHECKSUM(NEWID())) & 15)) as NameFROM   digits CROSS JOIN digits ii CROSS  JOIN digits iii CROSS  JOIN digits iv CROSS  JOIN digits v CROSS  JOIN digits vi-- Random score 500K players * 4 games = 2M rowsINSERT INTO [Leaderboard] (    [gameId],[playerId],[timePeriod],[timePeriodFrom],[score],[createdDateTime])SELECT  GameID, Player.id,ABS(CHECKSUM(NEWID())) & 3 as [timePeriod], DATEADD(MILLISECOND, CHECKSUM(NEWID()),GETDATE()) as Updated, ABS(CHECKSUM(NEWID())) & 65535 as score    , DATEADD(MILLISECOND, CHECKSUM(NEWID()),GETDATE()) as CreatedFROM (  SELECT 1 as GameID  UNION ALL SELECT 2  UNION ALL SELECT 3  UNION ALL SELECT 4) as Game    CROSS JOIN PlayerORDER BY NEWID()UPDATE [Leaderboard] SET [timePeriodFrom]='19000101' WHERE [timePeriod] = 0GODECLARE @From date = '19000101'--'20131108'    ,@GameID int = 3    ,@timePeriod tinyint = 0-- Get paginated ranking ;With Lb as (SELECT     DENSE_RANK() OVER (ORDER BY Score DESC) as Rnk    ,Score, createdDateTime, playerIdFROM [Leaderboard]WHERE GameId = @GameId  AND [timePeriod] = @timePeriod  AND [timePeriodFrom] = @From)SELECT lb.rnk,lb.Score, lb.createdDateTime, lb.playerId, Player.playerNameFROM Lb INNER JOIN Player ON lb.playerId = Player.idORDER BY rnk OFFSET 75 ROWS FETCH NEXT 25 ROWS ONLY;-- Get rank of a player for a given game SELECT (SELECT COUNT(DISTINCT rnk.score)         FROM [Leaderboard] as rnk         WHERE rnk.GameId = @GameId             AND rnk.[timePeriod] = @timePeriod            AND rnk.[timePeriodFrom] = @From            AND rnk.score >= [Leaderboard].score) as rnk    ,[Leaderboard].Score, [Leaderboard].createdDateTime, [Leaderboard].playerId, Player.playerNameFROM [Leaderboard]  INNER JOIN Player ON [Leaderboard].playerId = Player.idwhere [Leaderboard].GameId = @GameId    AND [Leaderboard].[timePeriod] = @timePeriod    AND [Leaderboard].[timePeriodFrom] = @From    and Player.playerName = N'785DDBBB-3000-4730-B'GO

This is only an example for the presentation of ideas. It can be optimized. For example, combining columns GameID, TimePeriod, TimePeriodDate to one column through the dictionary table. The effectiveness of the index will be higher.

P.S. Sorry for my English. Feel free to fix grammatical or spelling errors

You could look into indexed views to create scoreboards for common time ranges (today, this week/month/year, all-time).

to get the rank of a player for a given game across multiple timeframes, you will select the game and rank (i.e. sort) by score over a multiple timeframes. for this, your nonclustered index could be changed like this since this is the way your select seems to query.

CREATE NONCLUSTERED INDEX [Score_Idx] ON score ([gameId] ASC, [updatedDateTime] ASC, [score] DESC) INCLUDE ([playerName])

for the paginated ranking:

for the 24h-top score i guess you will want all the top scores of a single user across all games within the last 24h. for this you will be querying [playername], [updateddatetime] with [gameid].

for the players between rank 25-50, i assume you are talking about a single game and have a long ranking that you can page through. the query will then be based upon [gameid], [score] and a little on [updateddatetime] for the ties.

the single-user ranks, probably for each game, is a little more difficult. you will need to query the leaderboards for all games in order to get the player's rank in them and then filter on the player. you will need [gameid], [score], [updateddatetime] and then filter by player.

concluding all this, i propose you keep your nonclustered index and change the primary key to:

PRIMARY KEY CLUSTERED ([gameId] ASC, [score] DESC, [updatedDateTime] ASC)

for the 24h-top score i think this might help:

CREATE NONCLUSTERED INDEX [player_Idx] ON score ([playerName] ASC) INCLUDE ([gameId], [score])

the dense_rank query sorts because it selects [gameId], [updatedDateTime], [score]. see my comment on the nonclustered index above.

i would also think twice about including the [updatedDateTime] in your queries and subsequently in your indexes. maybe sometmes two players get the same rank, why not? [updatedDateTime] will let your index swell up significantly.

also you might think about partitioning tables by [gameid].