3

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.

  • 2
    It's difficult to suggest smth without seeing the actual plan: we need to see where the estimation goes wrong. But if yo have the actual plan and can identify where the error begins, I suggest you to split your query (you have too many joins), for example you see that after the 5th join estimation is wrong, so save the 1st part of the query to #tmp table and make other joins to that table – sepupic Jul 19 '17 at 7:19
  • To expand on @sepupic 's suggestion: start small, work your way up. Try running this with query just the first two tables, inserting the results into a temp table. Confirm the time it takes, and the number of records returned. If that seems reasonable, add in the next table. Repeat. Capture those execution plans. Watch for cases where the actual and estimated row counts (etc.) don't match.. Also - are you sure your statistics are up to date? – RDFozz Jul 19 '17 at 14:33
  • I will try the idea of doing a few tables at a time to see if anything jumps out. – Chad Portman Jul 19 '17 at 14:39
  • Are you doing this with any conditions? as that query stands you're asking the data for every user across all of time – Ste Bov Jul 20 '17 at 9:35
  • This code needs everything. It is going to be used to generate a table with all the data in one spot. To be the table we pull all the data from. Right now all the ZZ_tables are created from complex queries on already existing tables pulling the data into a smaller subset of tables. However it was requested of me to combine it further into one table so the analyst can pull the data from one table to help eliminate them writing bad joins. Or needing to use the same block of code over and over. – Chad Portman Jul 20 '17 at 12:49
1

First let's start where the problem likely isn't to be. Cardinality estimates look fine. You aren't doing any filtering and all of the joins are left joins on the primary keys of the tables. Table access methods look fine. You've defined the best possible indexes for the tables and SQL Server is doing index scans on those tables. Seems perfectly reasonable because you need all of the rows from the tables. Join order seems ok. None of the tables will decrease or increase the size of the result set (if ZZ_PlayerIDList is the starting table). A poor join order could lead to some unnecessary repartitioning but I don't see that here.

That means the join type. There are four parallel merge joins and the rest are parallel hash joins. There are some edge cases with parallel merge join that perform very poorly. I'm not a fan of it for this type of query in which you data is already sorted. The parallel aspect of it means you read the sorted data in parallel (which breaks the sorting) only to have to sort it again. There's a lot of repartitioning rows and sorting which would be avoided with MAXDOP 1 merge joins. There are also hints in the query plan that this query isn't getting as much memory as it wants.

I would try adding a MAXDOP 1 hint to the query and making changes so that you get all merge joins. For testing purposes it may be helpful to add OPTION (MERGE JOIN) as a hint. I would not expect that query to take more than 9 hours. If it does that sounds like some kind of hardware, configuration, or blocking issue.

From a data model point of view, you could improve performance of the query by combining together some of your tables. All of them have the same primary keys. For example, UX_ZZGamingTrips has 18260100 rows and UX_ZZTrackedSlot has 18259900 rows. Would combing those tables into one table really be a bad thing? What do you gain by separating them?

  • I will test out the maxdop1 and option merge join idea today. As for combing the tables that is the overall goal. This query is going to be building the final ZZ_table which is everything combined. In your example gaming trips and trackedslot the difference is that gaming trips is Boolean of if the player exists in tracked slot or tracked table. Which now that I write that out means I could probably just write that as a case statement and remove the gamingtrip table altogether.... I will have to look and see if there are any others i could do something like that with. – Chad Portman Jul 20 '17 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.