Including certain columns creates long query time

  • Hi, I have a query that takes a long time to run if I add  certain column(s) to it. I realize this is highly situational and tough to determine the issue without a lot of details, so I appreciate any help. I'm hoping someone has a general idea of why this is happening.

    Without the "gbp.[boxscore.players.stats.batting.hits]" column, query takes 5 seconds, 181,343 rows. with the column (gbp.[boxscore.players.stats.batting.hits]) in the select list, it just grinds away and I stop the query after 10 minutes or so.

    ;WITH cteBatter AS (SELECT batterID FROM MLBdw_rNd.bts_batter bIN WHERE bIN.analysis_date_int = 20210407 GROUP BY batterID) 


    SELECT
    b.batterID
    , g.[gameData.venue.id] venueID,gbp.[gamePk],gbp.[liveData.boxscore.teams.team.id] batterTeamID,g.schedule_date_int AS schedule_date_int,
    g.[gameData.datetime.dayNight] dayNight, g.[gameData.game.season] AS season, g.[gameData.venue.fieldInfo.turfType] AS turfType, g.[gameData.venue.fieldInfo.roofType] AS roofType
    ,CASE WHEN gbp.[liveData.boxscore.teams.team.id] = g.[gameData.teams.home.id] THEN g.[gameData.teams.away.id] ELSE g.[gameData.teams.home.id] END AS vsTeamID, CASE WHEN gbp.[liveData.boxscore.teams.team.id] = g.[gameData.teams.home.id] THEN 'home' ELSE 'away' end
    AS teamAwayOrHome
    /*This is the column that causes the long running query*/, gbp.[boxscore.players.stats.batting.hits] AS [batting_hits]



    FROM mlb_json.game_boxscore_players gbp
    INNER JOIN mlb_json.game g ON g.[gameData.game.pk] = gbp.gamePk
    INNER JOIN cteBatter b ON b.batterID = gbp.[boxscore.players.person.id]
    WHERE g.[gameData.game.type] = 'R' AND g.[gameData.status.codedGameState] = 'F'

    Execution plan for the quick query:

    https://www.brentozar.com/pastetheplan/?id=S14PcOjSd

    Estimated execution plan for the query with the column:

    https://www.brentozar.com/pastetheplan/?id=Bk8pq_or_

    DDL for the  tables....

    /****** Object:  Table [mlb_json].[game_boxscore_players]    Script Date: 4/7/2021 12:59:13 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [mlb_json].[game_boxscore_players](
    [gamePk] [INT] NOT NULL,
    [liveData.boxscore.teams.team.id] [INT] NOT NULL,
    [liveData.boxscore.teams.team.name] [VARCHAR](200) NULL,
    [boxscore.players.person.fullName] [VARCHAR](200) NULL,
    [boxscore.players.person.id] [INT] NOT NULL,
    [boxscore.players.person.link] [VARCHAR](200) NULL,
    [boxscore.players.seasonStats.batting.atBats] [INT] NULL,
    [boxscore.players.seasonStats.batting.atBatsPerHomeRun] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.batting.avg] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.batting.babip] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.batting.baseOnBalls] [INT] NULL,
    [boxscore.players.seasonStats.batting.catchersInterference] [INT] NULL,
    [boxscore.players.seasonStats.batting.caughtStealing] [INT] NULL,
    [boxscore.players.seasonStats.batting.doubles] [INT] NULL,
    [boxscore.players.seasonStats.batting.flyOuts] [INT] NULL,
    [boxscore.players.seasonStats.batting.gamesPlayed] [INT] NULL,
    [boxscore.players.seasonStats.batting.groundIntoDoublePlay] [INT] NULL,
    [boxscore.players.seasonStats.batting.groundIntoTriplePlay] [INT] NULL,
    [boxscore.players.seasonStats.batting.groundOuts] [INT] NULL,
    [boxscore.players.seasonStats.batting.hitByPitch] [INT] NULL,
    [boxscore.players.seasonStats.batting.hits] [INT] NULL,
    [boxscore.players.seasonStats.batting.homeRuns] [INT] NULL,
    [boxscore.players.seasonStats.batting.intentionalWalks] [INT] NULL,
    [boxscore.players.seasonStats.batting.leftOnBase] [INT] NULL,
    [boxscore.players.seasonStats.batting.obp] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.batting.ops] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.batting.pickoffs] [INT] NULL,
    [boxscore.players.seasonStats.batting.plateAppearances] [INT] NULL,
    [boxscore.players.seasonStats.batting.rbi] [INT] NULL,
    [boxscore.players.seasonStats.batting.runs] [INT] NULL,
    [boxscore.players.seasonStats.batting.sacBunts] [INT] NULL,
    [boxscore.players.seasonStats.batting.sacFlies] [INT] NULL,
    [boxscore.players.seasonStats.batting.slg] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.batting.stolenBasePercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.batting.stolenBases] [INT] NULL,
    [boxscore.players.seasonStats.batting.strikeOuts] [INT] NULL,
    [boxscore.players.seasonStats.batting.totalBases] [INT] NULL,
    [boxscore.players.seasonStats.batting.triples] [INT] NULL,
    [boxscore.players.seasonStats.pitching.airOuts] [INT] NULL,
    [boxscore.players.seasonStats.pitching.atBats] [INT] NULL,
    [boxscore.players.seasonStats.pitching.balks] [INT] NULL,
    [boxscore.players.seasonStats.pitching.baseOnBalls] [INT] NULL,
    [boxscore.players.seasonStats.pitching.blownSaves] [INT] NULL,
    [boxscore.players.seasonStats.pitching.catchersInterference] [INT] NULL,
    [boxscore.players.seasonStats.pitching.caughtStealing] [INT] NULL,
    [boxscore.players.seasonStats.pitching.completeGames] [INT] NULL,
    [boxscore.players.seasonStats.pitching.doubles] [INT] NULL,
    [boxscore.players.seasonStats.pitching.earnedRuns] [INT] NULL,
    [boxscore.players.seasonStats.pitching.era] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.flyOuts] [INT] NULL,
    [boxscore.players.seasonStats.pitching.gamesFinished] [INT] NULL,
    [boxscore.players.seasonStats.pitching.gamesPitched] [INT] NULL,
    [boxscore.players.seasonStats.pitching.gamesPlayed] [INT] NULL,
    [boxscore.players.seasonStats.pitching.gamesStarted] [INT] NULL,
    [boxscore.players.seasonStats.pitching.groundOuts] [INT] NULL,
    [boxscore.players.seasonStats.pitching.groundOutsToAirouts] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.hitBatsmen] [INT] NULL,
    [boxscore.players.seasonStats.pitching.hitByPitch] [INT] NULL,
    [boxscore.players.seasonStats.pitching.hits] [INT] NULL,
    [boxscore.players.seasonStats.pitching.hitsPer9Inn] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.holds] [INT] NULL,
    [boxscore.players.seasonStats.pitching.homeRuns] [INT] NULL,
    [boxscore.players.seasonStats.pitching.homeRunsPer9] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.inheritedRunners] [INT] NULL,
    [boxscore.players.seasonStats.pitching.inheritedRunnersScored] [INT] NULL,
    [boxscore.players.seasonStats.pitching.inningsPitched] [DECIMAL](18, 1) NULL,
    [boxscore.players.seasonStats.pitching.intentionalWalks] [INT] NULL,
    [boxscore.players.seasonStats.pitching.losses] [INT] NULL,
    [boxscore.players.seasonStats.pitching.obp] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.outs] [INT] NULL,
    [boxscore.players.seasonStats.pitching.pickoffs] [INT] NULL,
    [boxscore.players.seasonStats.pitching.rbi] [INT] NULL,
    [boxscore.players.seasonStats.pitching.runs] [INT] NULL,
    [boxscore.players.seasonStats.pitching.runsScoredPer9] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.sacBunts] [INT] NULL,
    [boxscore.players.seasonStats.pitching.sacFlies] [INT] NULL,
    [boxscore.players.seasonStats.pitching.saveOpportunities] [INT] NULL,
    [boxscore.players.seasonStats.pitching.saves] [INT] NULL,
    [boxscore.players.seasonStats.pitching.shutouts] [INT] NULL,
    [boxscore.players.seasonStats.pitching.stolenBasePercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.stolenBases] [INT] NULL,
    [boxscore.players.seasonStats.pitching.strikeOuts] [INT] NULL,
    [boxscore.players.seasonStats.pitching.strikeoutWalkRatio] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.strikeoutsPer9Inn] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.triples] [INT] NULL,
    [boxscore.players.seasonStats.pitching.walksPer9Inn] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.whip] [DECIMAL](18, 2) NULL,
    [boxscore.players.seasonStats.pitching.wildPitches] [INT] NULL,
    [boxscore.players.seasonStats.pitching.winPercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.pitching.wins] [INT] NULL,
    [boxscore.players.seasonStats.fielding.assists] [INT] NULL,
    [boxscore.players.seasonStats.fielding.caughtStealing] [INT] NULL,
    [boxscore.players.seasonStats.fielding.chances] [INT] NULL,
    [boxscore.players.seasonStats.fielding.errors] [INT] NULL,
    [boxscore.players.seasonStats.fielding.fielding] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.fielding.passedBall] [INT] NULL,
    [boxscore.players.seasonStats.fielding.pickoffs] [INT] NULL,
    [boxscore.players.seasonStats.fielding.putOuts] [INT] NULL,
    [boxscore.players.seasonStats.fielding.stolenBasePercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.seasonStats.fielding.stolenBases] [INT] NULL,
    [boxscore.players.stats.batting.atBats] [INT] NULL,
    [boxscore.players.stats.batting.atBatsPerHomeRun] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.batting.baseOnBalls] [INT] NULL,
    [boxscore.players.stats.batting.catchersInterference] [INT] NULL,
    [boxscore.players.stats.batting.caughtStealing] [INT] NULL,
    [boxscore.players.stats.batting.doubles] [INT] NULL,
    [boxscore.players.stats.batting.flyOuts] [INT] NULL,
    [boxscore.players.stats.batting.gamesPlayed] [INT] NULL,
    [boxscore.players.stats.batting.groundIntoDoublePlay] [INT] NULL,
    [boxscore.players.stats.batting.groundIntoTriplePlay] [INT] NULL,
    [boxscore.players.stats.batting.groundOuts] [INT] NULL,
    [boxscore.players.stats.batting.hitByPitch] [INT] NULL,
    [boxscore.players.stats.batting.hits] [INT] NULL,
    [boxscore.players.stats.batting.homeRuns] [INT] NULL,
    [boxscore.players.stats.batting.intentionalWalks] [INT] NULL,
    [boxscore.players.stats.batting.leftOnBase] [INT] NULL,
    [boxscore.players.stats.batting.note] [VARCHAR](200) NULL,
    [boxscore.players.stats.batting.pickoffs] [INT] NULL,
    [boxscore.players.stats.batting.plateAppearances] [INT] NULL,
    [boxscore.players.stats.batting.rbi] [INT] NULL,
    [boxscore.players.stats.batting.runs] [INT] NULL,
    [boxscore.players.stats.batting.sacBunts] [INT] NULL,
    [boxscore.players.stats.batting.sacFlies] [INT] NULL,
    [boxscore.players.stats.batting.stolenBasePercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.batting.stolenBases] [INT] NULL,
    [boxscore.players.stats.batting.strikeOuts] [INT] NULL,
    [boxscore.players.stats.batting.totalBases] [INT] NULL,
    [boxscore.players.stats.batting.triples] [INT] NULL,
    [boxscore.players.stats.pitching.airOuts] [INT] NULL,
    [boxscore.players.stats.pitching.atBats] [INT] NULL,
    [boxscore.players.stats.pitching.balks] [INT] NULL,
    [boxscore.players.stats.pitching.balls] [INT] NULL,
    [boxscore.players.stats.pitching.baseOnBalls] [INT] NULL,
    [boxscore.players.stats.pitching.battersFaced] [INT] NULL,
    [boxscore.players.stats.pitching.blownSaves] [INT] NULL,
    [boxscore.players.stats.pitching.catchersInterference] [INT] NULL,
    [boxscore.players.stats.pitching.caughtStealing] [INT] NULL,
    [boxscore.players.stats.pitching.completeGames] [INT] NULL,
    [boxscore.players.stats.pitching.doubles] [INT] NULL,
    [boxscore.players.stats.pitching.earnedRuns] [INT] NULL,
    [boxscore.players.stats.pitching.flyOuts] [INT] NULL,
    [boxscore.players.stats.pitching.gamesFinished] [INT] NULL,
    [boxscore.players.stats.pitching.gamesPitched] [INT] NULL,
    [boxscore.players.stats.pitching.gamesPlayed] [INT] NULL,
    [boxscore.players.stats.pitching.gamesStarted] [INT] NULL,
    [boxscore.players.stats.pitching.groundOuts] [INT] NULL,
    [boxscore.players.stats.pitching.hitBatsmen] [INT] NULL,
    [boxscore.players.stats.pitching.hitByPitch] [INT] NULL,
    [boxscore.players.stats.pitching.hits] [INT] NULL,
    [boxscore.players.stats.pitching.holds] [INT] NULL,
    [boxscore.players.stats.pitching.homeRuns] [INT] NULL,
    [boxscore.players.stats.pitching.homeRunsPer9] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.pitching.inheritedRunners] [INT] NULL,
    [boxscore.players.stats.pitching.inheritedRunnersScored] [INT] NULL,
    [boxscore.players.stats.pitching.inningsPitched] [DECIMAL](18, 1) NULL,
    [boxscore.players.stats.pitching.intentionalWalks] [INT] NULL,
    [boxscore.players.stats.pitching.losses] [INT] NULL,
    [boxscore.players.stats.pitching.note] [VARCHAR](200) NULL,
    [boxscore.players.stats.pitching.numberOfPitches] [INT] NULL,
    [boxscore.players.stats.pitching.outs] [INT] NULL,
    [boxscore.players.stats.pitching.pickoffs] [INT] NULL,
    [boxscore.players.stats.pitching.pitchesThrown] [INT] NULL,
    [boxscore.players.stats.pitching.rbi] [INT] NULL,
    [boxscore.players.stats.pitching.runs] [INT] NULL,
    [boxscore.players.stats.pitching.runsScoredPer9] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.pitching.sacBunts] [INT] NULL,
    [boxscore.players.stats.pitching.sacFlies] [INT] NULL,
    [boxscore.players.stats.pitching.saveOpportunities] [INT] NULL,
    [boxscore.players.stats.pitching.saves] [INT] NULL,
    [boxscore.players.stats.pitching.shutouts] [INT] NULL,
    [boxscore.players.stats.pitching.stolenBasePercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.pitching.stolenBases] [INT] NULL,
    [boxscore.players.stats.pitching.strikeOuts] [INT] NULL,
    [boxscore.players.stats.pitching.strikePercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.pitching.strikes] [INT] NULL,
    [boxscore.players.stats.pitching.triples] [INT] NULL,
    [boxscore.players.stats.pitching.wildPitches] [INT] NULL,
    [boxscore.players.stats.pitching.wins] [INT] NULL,
    [boxscore.players.stats.fielding.assists] [INT] NULL,
    [boxscore.players.stats.fielding.caughtStealing] [INT] NULL,
    [boxscore.players.stats.fielding.chances] [INT] NULL,
    [boxscore.players.stats.fielding.errors] [INT] NULL,
    [boxscore.players.stats.fielding.fielding] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.fielding.passedBall] [INT] NULL,
    [boxscore.players.stats.fielding.pickoffs] [INT] NULL,
    [boxscore.players.stats.fielding.putOuts] [INT] NULL,
    [boxscore.players.stats.fielding.stolenBasePercentage] [DECIMAL](18, 3) NULL,
    [boxscore.players.stats.fielding.stolenBases] [INT] NULL,
    [boxscore.players.position.abbreviation] [VARCHAR](200) NULL,
    [boxscore.players.position.code] [VARCHAR](200) NULL,
    [boxscore.players.position.name] [VARCHAR](200) NULL,
    [boxscore.players.position.type] [VARCHAR](200) NULL,
    [boxscore.players.status.code] [VARCHAR](200) NULL,
    [boxscore.players.status.description] [VARCHAR](200) NULL,
    [jerseyNumber] [VARCHAR](200) NULL,
    [parentTeamId] [INT] NULL,
    [battingOrder] [INT] NULL,
    [createDate] [DATETIME] NOT NULL,
    CONSTRAINT [PK_game_boxscore_players_INTS] PRIMARY KEY CLUSTERED
    (
    [gamePk] ASC,
    [liveData.boxscore.teams.team.id] ASC,
    [boxscore.players.person.id] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    /****** Object: Index [IX_game_boxscore_players_personID] Script Date: 4/7/2021 12:59:13 PM ******/
    CREATE NONCLUSTERED INDEX [IX_game_boxscore_players_personID] ON [mlb_json].[game_boxscore_players]
    (
    [boxscore.players.person.id] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    GO


     

    /****** Object:  Table [mlb_json].[game]    Script Date: 4/7/2021 2:25:21 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [mlb_json].[game](
    [gameData.game.pk] [INT] NOT NULL,
    [gameData.game.type] [VARCHAR](200) NULL,
    [gameData.game.doubleHeader] [VARCHAR](200) NULL,
    [gameData.game.id] [VARCHAR](200) NULL,
    [gameData.game.gamedayType] [VARCHAR](200) NULL,
    [gameData.game.tiebreaker] [VARCHAR](200) NULL,
    [gameData.game.gameNumber] [VARCHAR](200) NULL,
    [gameData.game.calendarEventID] [VARCHAR](200) NULL,
    [gameData.game.season] [INT] NULL,
    [gameData.game.seasonDisplay] [VARCHAR](200) NULL,
    [gameData.datetime.dateTime] [VARCHAR](200) NULL,
    [gameData.datetime.originalDate] [VARCHAR](200) NULL,
    [gameData.datetime.dayNight] [VARCHAR](200) NULL,
    [gameData.datetime.time] [VARCHAR](200) NULL,
    [gameData.datetime.ampm] [VARCHAR](200) NULL,
    [gameData.status.abstractGameState] [VARCHAR](200) NULL,
    [gameData.status.codedGameState] [VARCHAR](200) NULL,
    [gameData.status.detailedState] [VARCHAR](200) NULL,
    [gameData.status.statusCode] [VARCHAR](200) NULL,
    [gameData.status.abstractGameCode] [VARCHAR](200) NULL,
    [gameData.teams.away.id] [INT] NULL,
    [gameData.teams.away.name] [VARCHAR](200) NULL,
    [gameData.teams.away.link] [VARCHAR](200) NULL,
    [gameData.teams.away.season] [INT] NULL,
    [gameData.teams.away.teamCode] [VARCHAR](200) NULL,
    [gameData.teams.away.fileCode] [VARCHAR](200) NULL,
    [gameData.teams.away.abbreviation] [VARCHAR](200) NULL,
    [gameData.teams.away.teamName] [VARCHAR](200) NULL,
    [gameData.teams.away.locationName] [VARCHAR](200) NULL,
    [gameData.teams.away.firstYearofPlay] [VARCHAR](200) NULL,
    [gameData.teams.home.id] [INT] NULL,
    [gameData.teams.home.name] [VARCHAR](200) NULL,
    [gameData.teams.home.link] [VARCHAR](200) NULL,
    [gameData.teams.home.season] [INT] NULL,
    [gameData.teams.home.teamCode] [VARCHAR](200) NULL,
    [gameData.teams.home.fileCode] [VARCHAR](200) NULL,
    [gameData.teams.home.abbreviation] [VARCHAR](200) NULL,
    [gameData.teams.home.teamName] [VARCHAR](200) NULL,
    [gameData.teams.home.locationName] [VARCHAR](200) NULL,
    [gameData.teams.home.firstYearofPlay] [VARCHAR](200) NULL,
    [gameData.venue.id] [INT] NULL,
    [gameData.venue.name] [VARCHAR](200) NULL,
    [gameData.venue.link] [VARCHAR](200) NULL,
    [gameData.venue.location.city] [VARCHAR](200) NULL,
    [gameData.venue.location.state] [VARCHAR](200) NULL,
    [gameData.venue.location.defaultCoordinates.latitude] [VARCHAR](200) NULL,
    [gameData.venue.location.defaultCoordinates.longitude] [VARCHAR](200) NULL,
    [gameData.venue.timeZone.id] [VARCHAR](200) NULL,
    [gameData.venue.location.timeZone.offset] [VARCHAR](200) NULL,
    [gameData.venue.timeZone.tz] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.capacity] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.turfType] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.roofType] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.center] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.leftLine] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.rightLine] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.leftCenter] [VARCHAR](200) NULL,
    [gameData.venue.fieldInfo.rightCenter] [VARCHAR](200) NULL,
    [condition] [VARCHAR](200) NULL,
    [temp] [VARCHAR](200) NULL,
    [wind] [VARCHAR](200) NULL,
    [gameData.gameInfo.attendance] [VARCHAR](200) NULL,
    [gameData.gameInfo.gameDurationMinutes] [VARCHAR](200) NULL,
    [gameData.flags.noHitter] [VARCHAR](200) NULL,
    [gameData.flags.perfectGame] [VARCHAR](200) NULL,
    [gameData.flags.awayTeamNoHitter] [VARCHAR](200) NULL,
    [gameData.flags.homeTeamNoHitter] [VARCHAR](200) NULL,
    [gameData.flags.homeTeamPerfectGame] [VARCHAR](200) NULL,
    [gameData.flags.awayTeamPerfectGame] [VARCHAR](200) NULL,
    [gameData.probablePitchers.away.id] [INT] NULL,
    [gameData.probablePitchers.away.fullName] [VARCHAR](200) NULL,
    [gameData.probablePitchers.away.link] [VARCHAR](200) NULL,
    [gameData.probablePitchers.home.id] [INT] NULL,
    [gameData.probablePitchers.home.fullName] [VARCHAR](200) NULL,
    [gameData.probablePitchers.home.link] [VARCHAR](200) NULL,
    [livedata.decisions.winner.id] [INT] NULL,
    [livedata.decisions.winner.fullName] [VARCHAR](200) NULL,
    [liveData.linescore.home.runs] [INT] NULL,
    [liveData.linescore.home.hits] [INT] NULL,
    [liveData.linescore.home.errors] [INT] NULL,
    [liveData.linescore.away.runs] [INT] NULL,
    [liveData.linescore.away.hits] [INT] NULL,
    [liveData.linescore.away.errors] [INT] NULL,
    [allPlays] [NVARCHAR](MAX) NULL,
    [boxscore] [NVARCHAR](MAX) NULL,
    [players] [NVARCHAR](MAX) NULL,
    [gameData] [NVARCHAR](MAX) NULL,
    [liveData] [NVARCHAR](MAX) NULL,
    [gameData.datetime.date] [DATE] NULL,
    [createDate] [DATETIME] NOT NULL,
    [schedule_date] [DATE] NULL,
    [schedule_date_int] [INT] NULL,
    CONSTRAINT [PK_game] PRIMARY KEY CLUSTERED
    (
    [gameData.game.pk] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    /****** Object: Index [IX_game_schedule_date_int] Script Date: 4/7/2021 2:25:22 PM ******/
    CREATE NONCLUSTERED INDEX [IX_game_schedule_date_int] ON [mlb_json].[game]
    (
    [schedule_date_int] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    GO

    /****** Object: Index [IX_game_season] Script Date: 4/7/2021 2:25:22 PM ******/
    CREATE NONCLUSTERED INDEX [IX_game_season] ON [mlb_json].[game]
    (
    [gameData.game.season] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    GO


     

     

    • This topic was modified 1 week, 5 days ago by  jmetape.
  • Need to see the DDL including the index definitions for all tables in the query.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • All those images show scans, no seeks, which really isn't going to be helping things here. That image, of your SQL, is very difficult to read as well. You'd be better off posting the SQL in a code block for us (click the {;}Insert/edit code sample button and paste the SQL into that). The DDL with the indexes of the table(s) involved would help along with a more consumable version of the plan; either attached to your question or by using Paste the Plan.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I for the index that's used in the seek ([IX_game_box...] full name unknown) you need to INCLUDE all the columns from table [game_boxscore_players] that are in the query.

    It then won't have to do a  key lookup at all.

    PS: If you get the estimated execution plan for the one that's taking a long time then it will probably recommend the correct index.

  • Thank you for your help. I've edited my post to be easier to read and included the ddl and links to the "Paste the plan" execution plans

  • For just that specific column, you need to rebuild one of the existing indexes:

    CREATE NONCLUSTERED INDEX [IX_game_boxscore_players_personID] 
    ON [mlb_json].[game_boxscore_players] ( [boxscore.players.person.id] )
    INCLUDE ( [boxscore.players.stats.batting.hits], [gamePk],
    [liveData.boxscore.teams.team.id] )
    WITH ( DROP_EXISTING = ON, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF ) ON [PRIMARY];

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • That did it! THank you so much!!!

  • This was removed by the editor as SPAM

  • Paxton wrote:

    Long running queries/statements/transactions are sometimes inevitable in a MySQL environment. In some occasions, a long running query could be a catalyst to a disastrous event.

    This is a SQL Server community though, not MySQL. 😉

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply