tsql - Stored Proc /Table Index Improvement -

i'm looking comments on stored proc using in our application. gets called lot , think there room improvement. i'm looking see if adding index team , opp sp.

we running on azure db.

the schema for table 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 sp:

create procedure [dbo].[up_getteampercentagev2] @team nvarchar(100), @opp nvarchar(100)  begin set nocount on declare  @totalresult int, @teamresult int   --total matchups set  @totalresult = (select count(*)  teamhistorymatchups (team = @team or opp = @team) , (team = @opp or opp = @opp) , result = 1)  set  @teamresult = (select count(*) teamhistorymatchups team = @team , opp = @opp , result = 1)  select (@teamresult * 100 / @totalresult) percentage  exit_proc: end 

i should mention worried inserts before sp called insert made table , call made win % on matchup on time.

i did add 2 nonclustered indexes after using display execution plan 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 in million rows. @ moment @ around 120k.

i added 2 records teamhistorymatchups each team result being 0 or 1. trying keep simple above query be.

create procedure [dbo].[up_getteampercentage]     @team nvarchar(100),     @opp nvarchar(100) select      sum(sign(result)) * 100 / count(*)      percentage     teamhistorymatchups     team = @team , opp = @opp 

but thought less writes , more complicated read (in sp) better approach.

if not worried inserts slow go ahead , add index better performance of selects.

also index should filter results result 1.

create nonclustered index [ix_teamhistorymatchups_team_opp]  on [dbo].[teamhistorymatchups] ([team],[opp]) result=1