Stored Proc /Table Index Improvement

Question

I'm looking for some comments on a stored proc that we are using in our application. It gets called a lot and I think there is room for improvement. I'm also looking to see if adding an Index to Team and Opp would help with the SP.

We are running this on an Azure DB.

The schema for for the table is the following:

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)
);

And here is the 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

I should mention that I am worried about inserts as just before this sp is called an insert is made into the table and then a call is made to get the win % on this matchup over time.

I did add two nonclustered Indexes after using the display execution plan a few times.

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]);

This table will get in the million rows. At the moment it is at around 120k.

I was added 2 records into the TeamHistoryMatchUps for each team with the result being 0 or 1. I was trying to keep it very simple so that the above query could be.

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

But thought that less writes and a more complicated read (in the SP) would be a better approach.

Answer 1

If you are not worried about the inserts to be slow i would say go ahead and add the index for better performance of selects.

also the index should filter the results where result is 1.

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