Including certain columns creates long query time

Question

Including certain columns creates long query time

Right there with Babe

Points: 731

April 7, 2021 at 4:04 pm

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 3 years, 7 months ago by jmetape.

April 8, 2021 at 6:55 am

This was removed by the editor as SPAM

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

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

Need to see the DDL including the index definitions for all tables in the query.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Thom A SSC Guru Points: 99130 More actions · Answer 2

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

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 3

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.

jmetape Right there with Babe Points: 731 More actions · Answer 4

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jmetape Right there with Babe Points: 731 More actions · Answer 6

jmetape

Right there with Babe

Points: 731

April 7, 2021 at 8:44 pm

#3866542

That did it! THank you so much!!!

Thom A SSC Guru Points: 99130 More actions · Answer 7

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