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.

This question actually belongs on https://codereview.stackexchange.com/ — Jens, Apr 14 '17 at 12:57

score 1 · Accepted Answer · answered Apr 14 '17 at 12:54

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

answered Apr 14 '17 at 12:54

Mahesh Sambu

349
2
15

I added more details to the Question. Yes I care about inserts as they happen just before this. – userStack Apr 14 '17 at 15:52
The indexes helped the most. Thanks for the help. – userStack Apr 15 '17 at 21:26
@userStack I am glad that i can help you. – Mahesh Sambu Apr 16 '17 at 00:10

score 0 · Answer 2 · answered Apr 14 '17 at 12:56

I think this should reduce accesses (in the mean time, I am trying to see if it's possibile to use just one SELECT). Index as suggested should help.

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

score 0 · Answer 3 · answered Apr 14 '17 at 14:22

0

The answer is that it depends on the number of records in the TeamHistoryMatchUps table, and on how many distinct values are in each of these columns. If there are not a large amount of records in the table, the query optimizer may still create an execution plan that involves an index scan (which reads every leaf record in the index looking for matches). This is not much faster than a full table scan.

If there are lots of records and the values you're searching for on the team and opp index will return approximately 15% of the rows or less, the query optimizer will likely elect to make use of the indexes through an index seek. In this case, there will be a performance improvement.

answered Apr 14 '17 at 14:22

Rob Reagan

7,313
3
20
49

There is about 120k records at the moment I expect that it will get to the millions in about a week. There are about 100 Team values that can be in either Team or Opp. – userStack Apr 14 '17 at 15:55
I'd say that adding the index would be a good idea. Also, Azure SQL has a Performance Recommendations screen that will look at your queries and suggest indexes that should be added. You do, however, have to use the database in order for it to gather information and make recommendations. – Rob Reagan Apr 14 '17 at 16:08

score 0 · Answer 4 · answered Apr 14 '17 at 18:50

Re-declare sproc params as local params. It handles param sniffing, and this also improves performance when WITH RECOMPILE doesn't help (Source: 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

***Never tried this on Azure DB

Stored Proc /Table Index Improvement

4 Answers4