sql - 临时表导致对表进行完全扫描，这与左外连接查询中的表变量不同

Question

@GameIds table variable如果我用底部查询中的切换出去，#GameIds temp table我会在某些表上进行全表扫描，从而延长执行时间，并发出大内存授予警告。我认为临时表会提供更好的执行计划，因为与表变量相比，它具有统计信息。table 变量在具有聚集索引查找的表之间进行了很好的连接。我错过了临时表的一些东西吗？

存储过程

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

标签： sqlsql-servertemp-tablestable-valued-parameterstable-variable