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