title: Stored Proc /Table Index Improvement
body: <p>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.</p>
  
  <p>We are running this on an Azure DB.</p>
  
  <p>The schema for for the table is the following:</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>And here is the SP:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I did add two nonclustered Indexes after using the display execution plan a few times.</p>
  
  <pre><code>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]);
  </code></pre>
  
  <p>This table will get in the million rows.  At the moment it is at around 120k.</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But thought that less writes and a more complicated read (in the SP) would be a better approach.</p>