If I switch out the @GameIds table variable with the #GameIds temp table in the bottom query I get a full table scan on some tables, making a longer execution time, and large memory grant warning. I thought a temp table would give a better execution plan because it has statistics on it compared to a table variable. The table variable does a nice join between tables with a clustered index seek. Am I missing something with the temp table?
Stored Procedure
ALTER PROCEDURE [Test].[GetGameParticipants]
@GameIds [Test].[BulkIdType] READONLY
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #GameIds
(
Id INT
)
CREATE NONCLUSTERED INDEX IX_GameIds_Temp ON #GameIds (Id);
INSERT INTO #GameIds
SELECT Id FROM @GameIds
Select Statement
SELECT
...
FROM
Test.DivisionGameTeamResult divisionGameTeamResult LEFT OUTER JOIN
Test.DivisionBracketParticipant divisionBracketParticipant ON divisionBracketParticipant.DivisionGameTeamResultId = divisionGameTeamResult.Id LEFT OUTER JOIN
Test.DivisionBracketParticipantPart divisionBracketParticipantPart ON divisionBracketParticipantPart.Id = divisionBracketParticipant.DivisionBracketParticipantPartId LEFT OUTER JOIN
Test.DivisionBracketPart divisionBracketPart ON divisionBracketPart.Id = divisionBracketParticipantPart.Id LEFT OUTER JOIN
Test.DivisionBracket divisionBracket ON divisionBracket.Id = divisionBracketPart.DivisionBracketId LEFT OUTER JOIN
Test.DivisionBracketParticipantTeam divisionBracketParticipantTeam ON divisionBracketParticipantTeam.Id = divisionBracketParticipant.Id LEFT OUTER JOIN
Test.DivisionTeam divisionTeamBracket ON divisionTeamBracket.Id = divisionBracketParticipantTeam.DivisionTeamId LEFT OUTER JOIN
Test.Team teamBracket ON teamBracket.Id = divisionTeamBracket.Id LEFT OUTER JOIN
Test.DivisionBracketParticipantBracket divisionBracketParticipantBracket ON divisionBracketParticipantBracket.Id = divisionBracketParticipant.Id LEFT OUTER JOIN
Test.DivisionBracketParticipantPool divisionBracketParticipantPool ON divisionBracketParticipantPool.Id = divisionBracketParticipant.Id LEFT OUTER JOIN
Test.DivisionPool divisionPoolBracket ON divisionPoolBracket.Id = divisionBracketParticipantPool.DivisionPoolId LEFT OUTER JOIN
Test.Division poolDivision ON divisionPoolBracket.DivisionId = poolDivision.Id LEFT OUTER JOIN
Test.DivisionBracketParticipantTeamPool divisionBracketParticipantTeamPool ON divisionBracketParticipantTeamPool.Id = divisionBracketParticipant.Id LEFT OUTER JOIN
Test.DivisionTeamPool divisionTeamPoolBracket ON divisionTeamPoolBracket.Id = divisionBracketParticipantTeamPool.DivisionTeamPoolId LEFT OUTER JOIN
Test.DivisionPool divisionTeamPoolPoolBracket ON divisionTeamPoolPoolBracket.Id = divisionTeamPoolBracket.DivisionPoolId LEFT OUTER JOIN
Test.DivisionTeam divisionTeamPoolTeamBracket ON divisionTeamPoolTeamBracket.Id = divisionTeamPoolBracket.DivisionTeamId LEFT OUTER JOIN
Test.Team teamPoolTeamBracket ON teamPoolTeamBracket.Id = divisionTeamPoolTeamBracket.Id
WHERE
EXISTS (SELECT * FROM @GameIds WHERE Id = divisionGameTeamResult.GameId)
