Temp table causing full scan on table unlike Table Variable in a left outer join queries

347 views Asked by At

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)

enter image description here

0

There are 0 answers