I am trying to improve the execution plan to speed up the query. Currently the query won't finish after nine hours. Right now I am getting a lot of hash match and merge join. I have very limited knowledge of execution plans but from what I think I know the hash match is very slow and the merge join is not much better. Although I am not exactly sure what I am hoping to find as this part of the SQL is still really new to me.
Each table used except for ZZ_PropertyTable
has a Primary key on Casino_ID
, CasinoCode
, Gamingdate
, PlayerID
in that order. Along with a unique nonclustered index which is the same columns in the same order but with an include clause to cover the this query. The ZZ_PropertyTable
has a Primary key on Casino_ID
, CasinoCode
.
I did not think it mattered but just to be sure I put all the joins in the same order as the primary keys.
I am not sure if maybe I should write the code differently or if i need different indexes. Any help you can provide even if it just a link to something I can read to better understand this would be greatly appreciated.
I am using SQL Server 2012 below is the code as well as a link to the execution plan:
https://www.brentozar.com/pastetheplan/?id=rymXZWnHb
Select
PL.Casino_ID
,PL.CasinoCode
,PT.PropertyName
,PL.PlayerID
,PL.Gamingdate
,1 as TotalTrips
,isnull(TS.SlotTrips,0) as SlotTrips
,isnull(TT.TableTrips,0) as TableTrips
,isnull(GT.GamingTrips,0) as GamingTrips
,Isnull(TS.SlotCoinIn,0) as SlotCoinIn
,isnull(TT.TableDrop,0) as TableDrop
,isnull(TS.SlotTheoWin,0) as SlotTheoWin
,isnull(TT.TableTheoWin,0) as TableTheoWin
,isnull(TS.SlotActualWin,0) as SlotActualWin
,isnull(TT.TableActualWin,0) as TableActualWin
,isnull(TS.SlotTheoWin
+ TT.TableTheoWin,0) as TotalTheo
,isnull(TS.SlotActualWin
+ TT.TableActualWin,0) as TotalActual
,isnull(TS.SlotPointsEarned,0) as OasisSlotPointsEarned
,isnull(TT.TablePointsEarned,0) as OasisTablePointsEarned
,isnull(HP.SlotPointsEarned,0) as HaloSlotPointsEarned
,isnull(HP.TablePointsEarned,0) as HaloTablePointsEarned
,isnull(TS.SlotPointsEarned
+ TT.TablePointsEarned
+ HP.SlotPointsEarned
+ HP.TablePointsEarned
+ PA.PointAdjustment,0) as TotalPointsEarned
,isnull(TS.SlotCompsEarned,0) as OasisSlotCompEarned
,isnull(TT.TableCompsEarned,0) as OasisTableCompEarned
,isnull(HC.SlotCompsEarned,0) as HaloSlotCompEarned
,isnull(HC.TableCompsEarned,0) as HaloTableCompEarned
,isnull(TS.SlotCompsEarned
+ TT.TableCompsEarned
+ HC.SlotCompsEarned
+ HC.TableCompsEarned
+ CA.CompAdjustment,0) as TotalCompEarned
,isnull(TS.SlotMinutesPlayed,0) as SlotMinutesPlayed
,isnull(TT.TableMinutesPlayed,0)as TableMinutesPlayed
,isnull(TS.SlotMinutesPlayed
+ TT.TableMinutesPlayed,0) as TotalMinutesPlayed
,isnull(F.FreePlayRedeemed*-1,0)as FreePlayRedeemed
,isnull(F.PointstoFreePlay*-1,0)as PointsToFreePlay
,isnull(F.PromoFreePlay*-1,0) as PromoFreePlay
,isnull(C.CompRedeemed,0) as CompRedeemed
,isnull(TS.SlotJackpots,0) as SlotJackpots
,isnull(TT.TableJackpots,0) as TableJackpots
,isnull(TS.SlotJackpots
+ TT.TableJackpots,0) as TotalJackpots
,isnull(CA.CompAdjustment,0) as CompAdjustment
,isnull(PA.PointAdjustment,0) as PointAdjustment
,isnull(PR.RedeemedAmount,0) as Points_to_Purchase
,isnull(TT.TableTotalIn,0) as TableTotalIn
,isnull(A.Downloaded,0) as AwardsDownloaded
,isnull(PC.RedeemedAmount,0) as PromoChipRedeem
,Getdate() as LastUpdated
From Analytics.dbo.ZZ_PlayerIDList as PL
Left Join Analytics.dbo.ZZ_HaloComps as HC
on PL.Casino_ID = HC.Casino_ID
and PL.CasinoCode = HC.CasinoCode
and PL.GamingDate = HC.GamingDate
and PL.PlayerID = HC.PlayerID
Left Join Analytics.dbo.ZZ_HaloPoints as HP
on PL.Casino_ID = HP.Casino_ID
and PL.CasinoCode = HP.CasinoCode
and PL.GamingDate = HP.GamingDate
and PL.PlayerID = HP.PlayerID
Left Join Analytics.dbo.ZZ_FreePlay as F
on PL.Casino_ID = F.Casino_ID
and PL.CasinoCode = F.CasinoCode
and PL.GamingDate = F.GamingDate
and PL.PlayerID = F.PlayerID
Left Join Analytics.dbo.ZZ_Comp as C
on PL.Casino_ID = C.Casino_ID
and PL.CasinoCode = C.CasinoCode
and PL.GamingDate = C.GamingDate
and PL.PlayerID = C.PlayerID
Left Join Analytics.dbo.ZZ_PropertyTable as PT
on PL.Casino_ID = PT.Casino_ID
and PL.CasinoCode = PT.CasinoCode
Left Join Analytics.dbo.ZZ_TrackedSlot as TS
on PL.Casino_ID = TS.Casino_ID
and PL.CasinoCode = TS.CasinoCode
and PL.GamingDate = TS.GamingDate
and PL.PlayerID = TS.PlayerID
Left Join Analytics.dbo.ZZ_TrackedTable as TT
on PL.Casino_ID = TT.Casino_ID
and PL.CasinoCode = TT.CasinoCode
and PL.GamingDate = TT.GamingDate
and PL.PlayerID = TT.PlayerID
Left Join Analytics.dbo.ZZ_GamingTrips as GT
on PL.Casino_ID = GT.Casino_ID
and PL.CasinoCode = GT.CasinoCode
and PL.GamingDate = GT.GamingDate
and PL.PlayerID = GT.PlayerID
Left Join Analytics.dbo.ZZ_PointRedeem as PR
on PL.Casino_ID = PR.Casino_ID
and PL.CasinoCode = PR.CasinoCode
and PL.GamingDate = PR.GamingDate
and PL.PlayerID = PR.PlayerID
Left Join Analytics.dbo.ZZ_PointAdj as PA
on PL.Casino_ID = PA.Casino_ID
and PL.CasinoCode = PA.CasinoCode
and PL.GamingDate = PA.GamingDate
and PL.PlayerID = PA.PlayerID
Left Join Analytics.dbo.ZZ_CompAdj as CA
on PL.Casino_ID = CA.Casino_ID
and PL.CasinoCode = CA.CasinoCode
and PL.GamingDate = CA.GamingDate
and PL.PlayerID = CA.PlayerID
Left Join Analytics.dbo.ZZ_Award as A
on PL.Casino_ID = A.Casino_ID
and PL.CasinoCode = A.CasinoCode
and PL.GamingDate = A.GamingDate
and PL.PlayerID = A.PlayerID
Left Join Analytics.dbo.ZZ_PromoChip as PC
on PL.Casino_ID = PC.Casino_ID
and PL.CasinoCode = PC.CasinoCode
and PL.GamingDate = PC.GamingDate
and PL.PlayerID = PC.PlayerID
The outer joins are all needed (cannot be inner joins). ZZ_PlayerIDList
table has a list of every player that exist in any of the other tables. So while they will exist in one of the other ZZ_ Tables
they might only be in one of them.
Statistics are up to date. I made sure to update them with a fullscan
prior to getting the estimated plan.