Улучшение индекса сохраненных процессов / таблиц

Question

Я ищу комментарии к хранимому процессу, который мы используем в нашем приложении. Это часто называют, и я думаю, что есть место для улучшения. Я также смотрю, поможет ли добавление индекса в Team и Opp с SP.

Мы выполняем это на базе данных Azure.

Схема для таблицы следующая:

CREATE TABLE [dbo].[TeamHistoryMatchUps] (
    [Id]              UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [Team]            NVARCHAR (100)   NOT NULL,
    [Opp]             NVARCHAR (100)   NOT NULL,
    [Result]          INT              NOT NULL,
    [MatchResultTime] DATETIME2 (7)    DEFAULT (getdate()) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

А вот и SP:

CREATE PROCEDURE [dbo].[up_GetTeamPercentagev2]
@Team NVARCHAR(100),
@Opp NVARCHAR(100) 
AS
begin
set nocount ON
declare

@TotalResult INT,
@TeamResult INT


--Total Matchups
Set  @TotalResult = (SELECT count(*)  FROM TeamHistoryMatchUps
WHERE (Team = @Team OR Opp = @Team) AND (Team = @Opp OR Opp = @Opp)
AND Result = 1)

Set  @TeamResult = (SELECT COUNT(*) FROM TeamHistoryMatchUps
WHERE Team = @Team and Opp = @Opp
AND Result = 1)

SELECT (@TeamResult * 100 / @TotalResult) AS Percentage

exit_proc:
end

Я должен упомянуть, что я беспокоюсь о вставках, так как незадолго до того, как этот sp вызывается, в таблицу вносится вставка, а затем делается вызов, чтобы получить процент выигрышей в этом матче с течением времени.

Я несколько раз добавил два некластеризованных индекса после использования плана выполнения отображения.

GO
CREATE NONCLUSTERED INDEX [[IX_MatchUps]
    ON [dbo].[TeamHistoryMatchUps]([Result] ASC)
    INCLUDE([Team], [Opp]);
GO
CREATE NONCLUSTERED INDEX [IX_MatchupsTeamOpp]
    ON [dbo].[TeamHistoryMatchUps]([Team] ASC, [Opp] ASC)
    INCLUDE([Result], [MatchResultTime], [MatchUpId]);

Эта таблица попадет в миллион строк. На данный момент это около 120к.

Я добавил 2 записи в TeamHistoryMatchUps для каждой команды с результатом 0 или 1. Я пытался сделать это очень просто, чтобы вышеупомянутый запрос мог быть.

CREATE PROCEDURE [dbo].[up_GetTeamPercentage]
    @Team NVARCHAR(100),
    @Opp NVARCHAR(100)
AS
SELECT 
    SUM(SIGN(result)) * 100 / COUNT(*) 
    AS Percentage
    FROM TeamHistoryMatchUps
    WHERE Team = @Team AND Opp = @Opp

Но думал, что меньше записей и более сложное чтение (в SP) будет лучшим подходом.

Answer 1

Если вы не беспокоитесь о медленной вставке, я бы сказал: добавьте индекс для лучшей производительности выбора.

также индекс должен фильтровать результаты, где результат равен 1.

CREATE NONCLUSTERED INDEX [IX_TeamHistoryMatchUps_team_opp] 
ON [dbo].[TeamHistoryMatchUps] ([Team],[Opp])
WHERE result=1

Answer 2

Ответ заключается в том, что это зависит от количества записей в таблице TeamHistoryMatchUps и от того, сколько различных значений содержится в каждом из этих столбцов. Если в таблице нет большого количества записей, оптимизатор запросов все равно может создать план выполнения, включающий сканирование индекса (который читает каждую конечную запись в индексе в поисках совпадений). Это не намного быстрее, чем полное сканирование таблицы.

Если имеется много записей и значения, которые вы ищете в команде и индексе opp, возвращают приблизительно 15% строк или меньше, оптимизатор запросов, скорее всего, выберет использование индексов при поиске по индексу. В этом случае произойдет улучшение производительности.

Answer 3

Повторно объявите sproc params как локальные params. Он обрабатывает анализ параметров, и это также улучшает производительность, когда WITH RECOMPILE не помогает (Источник: TBD)

CREATE PROCEDURE [dbo].[up_GetTeamPercentagev2] @Team NVARCHAR(100), @Opp NVARCHAR(100) AS begin set nocount ON declare

@localTeam NVARCHAR(100), @localOpp NVARCHAR(100), @TotalResult INT, @TeamResult INT

Set @Team = @localTeam SET @Opp = @localOpp

--Total Matchups Set @TotalResult = (SELECT count(*) FROM TeamHistoryMatchUps WHERE (Team = @localTeam OR Opp = @localTeam) AND (Team = @localOpp OR Opp = @localOpp) AND Result = 1)

Set @TeamResult = (SELECT COUNT(*) FROM TeamHistoryMatchUps WHERE Team = @localTeam and Opp = @localOpp AND Result = 1)

SELECT (@TeamResult * 100 / @TotalResult) AS Percentage

exit_proc: end

*** Никогда не пробовал это на Azure DB

Answer 4

Я думаю, что это должно уменьшить доступ (в то же время я пытаюсь увидеть, можно ли использовать только один SELECT). Индекс, как предложено, должен помочь.

--Total Matchups
SELECT @TeamResult = COUNT(*) FROM TeamHistoryMatchUps
WHERE  Result = 1
    AND Team = @Team and Opp = @Opp

SELECT @TotalResult = count(*)  
FROM TeamHistoryMatchUps
WHERE Opp = @Team AND Team = @Opp 
AND Result = 1

SET @TotalResult= @TotalResult+@TeamResult