Ответ 1
[Обновлено]
Основной ключ не является хорошим
У вас есть уникальная сущность, которая является [GameID] + [PlayerName]. И составной кластерный индекs > 120 байт с nvarchar. Найдите ответ @marc_s в соответствующем разделе SQL Server - Кластерный дизайн индекса для словаря
Схема вашей таблицы не соответствует вашим требованиям к периодам времени
Пример: я заработал 300 очков в среду, и этот счет был сохранен в таблице лидеров. На следующий день я заработал 250 баллов, но он не будет записываться в таблицу лидеров, и вы не получите результатов, если я запустил запрос во вторник в таблице лидеров.
Для получения полной информации вы можете получить из исторических игровых столов, но это может быть очень дорого
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)
Вот решения, чтобы ускорить процесс, связанный с агрегацией:
- Индексированное представление по исторической таблице (см. сообщение @Twinkles).
Вам нужно 3 индексированных представления для трех периодов времени. Потенциально огромный размер исторических таблиц и 3 индексированных представления. Не удалось удалить "старые" периоды таблицы. Проблемы с производительностью для сохранения оценки.
- Асинхронная таблица лидеров
Счета, сохраненные в исторической таблице. Задание SQL/ "Работник" (или несколько) в соответствии с расписанием (1 в минуту?) Сортирует историческую таблицу и заполняет таблицу лидеров (3 таблицы за 3 периода времени или одну таблицу с ключом периода времени) с предварительно рассчитанным рангом пользователя. Эта таблица также может быть денормализованной (иметь счет, дату и время, имя игрока и...). Плюсы: быстрое чтение (без сортировки), быстрое сохранение баллов, любые периоды времени, гибкая логика и гибкие графики. Минусы: пользователь закончил игру, но не сразу нашел себя в таблице лидеров.
- Преагрегированная таблица лидеров
Во время записи результаты сеанса игры проходят предварительную обработку. В вашем случае что-то вроде UPDATE [Leaderboard] SET score = @CurrentScore WHERE @CurrentScore > MAX (score) AND ... для идентификатора игрока/игры, но вы сделали это только для таблицы "Все время". Схема может выглядеть так:
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 150 1 1 2013-11-01 300 ... 1 2 2013-10-07 150 1 2 2013-11-18 300 ... 1 3 2013-11-19 300 1 3 2013-11-20 250 ...
Итак, вам нужно обновить все 3 балла за весь период времени. Также, как вы можете видеть, таблица лидеров будет содержать "старые" периоды, такие как ежемесячный октябрь. Возможно, вам нужно удалить его, если вам не нужна эта статистика. Плюсы: не нужна историческая таблица. Минусы: сложная процедура для хранения результата. Требуется обслуживание лидеров. Запрос требует сортировки и 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 Name
FROM 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 rows
INSERT 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 Created
FROM ( SELECT 1 as GameID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) as Game
CROSS JOIN Player
ORDER BY NEWID()
UPDATE [Leaderboard] SET [timePeriodFrom]='19000101' WHERE [timePeriod] = 0
GO
DECLARE @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, playerId
FROM [Leaderboard]
WHERE GameId = @GameId
AND [timePeriod] = @timePeriod
AND [timePeriodFrom] = @From)
SELECT lb.rnk,lb.Score, lb.createdDateTime, lb.playerId, Player.playerName
FROM Lb INNER JOIN Player ON lb.playerId = Player.id
ORDER 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.playerName
FROM [Leaderboard] INNER JOIN Player ON [Leaderboard].playerId = Player.id
where [Leaderboard].GameId = @GameId
AND [Leaderboard].[timePeriod] = @timePeriod
AND [Leaderboard].[timePeriodFrom] = @From
and Player.playerName = N'785DDBBB-3000-4730-B'
GO
Это только пример представления идей. Его можно оптимизировать. Например, объединение столбцов GameID, TimePeriod, TimePeriodDate в один столбец через таблицу словаря. Эффективность индекса будет выше.
P.S. Извините за мой английский. Не стесняйтесь исправить грамматические или орфографические ошибки.