Performance improvement - using temp table

1

...... INTO #CashierWithRowNumbers
FROM dbo.PlayersTransactions AS pt WITH(NOLOCK, INDEX=IX_PlayersTransactions_PlayerID_RequestDate_TransactionType_Status)
INNER JOIN ScratchPII.dbo.Operators AS o WITH(NOLOCK) ON pt.OperatorID = o.OperatorID
INNER JOIN dbo.Players AS p WITH(NOLOCK) ON pt.PlayerID = p.PlayerID
INNER JOIN dbo.PlayersWallets AS pw WITH(NOLOCK) ON p.PlayerID = pw.PlayerID
INNER JOIN dbo.Currencies AS c WITH(NOLOCK) ON pw.CurrencyCode = c.CurrencyCode
INNER JOIN @TransactionsStatusesTable AS tst ON pt.[Status] = tst.Column1 AND pt.TransactionType = tst.Column2
LEFT JOIN dbo.PlayersUCRs AS ucr WITH(NOLOCK) ON ucr.UCRID = pt.UCRID
LEFT JOIN dbo.PlayersTransactions AS aspt WITH (NOLOCK) ON aspt.TransactionID = pt.AssociatedTransactionID
LEFT JOIN dbo.PlayersBonuses AS pbon WITH (NOLOCK) ON pbon.BonusPolicyID = pt.BonusID AND pbon.PlayerBonusID = pt.PlayerBonusID
LEFT JOIN dbo.BonusPolicies AS bp WITH(NOLOCK) ON bp.BonusPolicyID = pt.BonusID AND bp.CasinoID IN (p.CasinoID, -1, 0)
LEFT JOIN dbo.ResourcesTrailer AS rtEng WITH(NOLOCK) ON rtEng.CasinoID = 0 AND bp.BonusTypeNameResourceID = rtEng.ResourceID AND rtEng.LanguageCode = 'ENG'
LEFT JOIN dbo.ResourcesTrailer AS rtLang WITH(NOLOCK) ON rtLang.CasinoID = 0 AND bp.BonusTypeNameResourceID = rtLang.ResourceID AND rtLang.LanguageCode = p.LanguageCode
LEFT JOIN dbo.ProcessingBanks AS pb WITH (NOLOCK) ON pt.ProcessingBank =pb.ProcessingBankCode
LEFT JOIN [ScratchPII].dbo.PlayersPaymentsMethods AS ppm ON pt.PlayerPaymentMethodID=ppm.PlayerPaymentMethodID
LEFT JOIN dbo.PaymentsMethods AS pm WITH(NOLOCK) ON pm.PaymentMethodID = ppm.PaymentMethodID AND pm.PaymentMethodID > 0
-- local PM name resource
LEFT JOIN dbo.ResourcesTrailer AS rtEngPM WITH(NOLOCK) ON rtEngPM.ResourceID = pm.NameResourceID AND rtEngPM.CasinoID = 0 AND rtEngPM.LanguageCode = 'ENG'
LEFT JOIN dbo.ResourcesTrailer AS rtLangPM WITH(NOLOCK) ON rtLangPM.ResourceID = pm.NameResourceID AND rtLangPM.CasinoID = 0 AND rtLangPM.LanguageCode = p.LanguageCode
LEFT JOIN dbo.ResourcesTrailer AS rtEngPMcsi WITH(NOLOCK) ON rtEngPMcsi.ResourceID = pm.NameResourceID AND rtEngPMcsi.CasinoID = p.CasinoID AND rtEngPMcsi.LanguageCode = 'ENG'
LEFT JOIN dbo.ResourcesTrailer AS rtLangPMcsi WITH(NOLOCK) ON rtLangPMcsi.ResourceID = pm.NameResourceID AND rtLangPMcsi.CasinoID = p.CasinoID AND rtLangPMcsi.LanguageCode = p.LanguageCode
--
LEFT JOIN dbo.PlayersLogins AS pl WITH (NOLOCK) ON p.PlayerID = pl.PlayerID AND pt.RequestDate BETWEEN pl.LoginDate AND ISNULL(pl.LogoutDate, '2050-01-01')
LEFT JOIN dbo.[Sessions] AS s WITH (NOLOCK) ON s.TransactionID = (CASE WHEN pt.AssociatedTransactionID = 0 THEN pt.TransactionID ELSE pt.AssociatedTransactionID END) AND s.SessionTypeID = 64
LEFT JOIN dbo.PlayersPendingPrizes AS ppp WITH(NOLOCK) ON ppp.PendingPrizeID = pt.PendingPrizeID AND pt.TransactionType = 'PTR'
LEFT JOIN dbo.[Parameters] AS prm0 WITH(NOLOCK) ON prm0.CasinoID = 0 AND prm0.ParamName = 'IsPaysafeAPIInsteadNETELLERAPIOnDepositEnabled'
LEFT JOIN dbo.[Parameters] AS prm WITH(NOLOCK) ON prm.CasinoID = p.CasinoID AND prm.ParamName = 'IsPaysafeAPIInsteadNETELLERAPIOnDepositEnabled'
LEFT JOIN dbo.PointsOfSale AS pos WITH(NOLOCK) ON pos.PointOfSaleID = pt.SubSystemID
WHERE pt.PlayerID = @PlayerID
AND (
pt.TransactionType IN ....

This is the part of the code of procedure that returns player's transactions in different scenarios/action modes.
Statistics parser displayed that there are more then 3000 scans on table BonusPolicies.
When i tried to transfer BonusPolicies data to temp table i got a lot of reads from Players table, how I can pass BonusPolicies relevant data to temp table and then use it such way to improve performance.
At BonusPolicies table one of indexes that we have and can be relevant to question is based on 2 columns - BonusPolicyID and CasinoID, and BonusPolicyID is PK, also CasinoID is PK , and we dont have their PlayerID column. And at Players table we have PK column PlayerID

2

When you specify a specific index:


FROM dbo.PlayersTransactions AS pt WITH(NOLOCK, 
INDEX=IX_PlayersTransactions_PlayerID_RequestDate_TransactionType_Status) --<<--

you might force SQL to do the other joins in the stated order. That could cause performance issues.

Other than that, without DDL and index stats for the indexes, there's no way we can help tune a query.

3

Thank you.
These are indexes on BonusPolicies table:

image
image1326Ă—406 84.5 KB

4

When i created temp table it takes much more time on execution:
CREATE TABLE #TempBonusPolicies(BonusPolicyID INT, CasinoID SMALLINT)
CREATE CLUSTERED INDEX IX_TempBonusPolicies_BonusPolicyID_CasinoID ON #TempBonusPolicies (BonusPolicyID,CasinoID)

INSERT #TempBonusPolicies (BonusPolicyID, CasinoID)
SELECT bp.BonusPolicyID, bp.CasinoID
FROM dbo.BonusPolicies AS bp WITH(NOLOCK)
INNER JOIN dbo.PlayersTransactions AS pt WITH(NOLOCK) ON bp.BonusPolicyID = pt.BonusID
INNER JOIN dbo.Players AS p WITH(NOLOCK) ON bp.CasinoID = p.CasinoID OR bp.CasinoID in (0, -1)

5

Without an execution plan it is guessing to the solution, is it possible to provide one?
Instructions - Brent Ozar Unlimited®

So just my guesses ;-):

Why don't you create the index after the INSERT statement? Index is slowing down INSERT statements as the index needs to be adjusted.

You can create 2 insert statements:

INSERT #TempBonusPolicies (BonusPolicyID, CasinoID)
SELECT bp.BonusPolicyID, bp.CasinoID
FROM dbo.BonusPolicies AS bp WITH(NOLOCK)
INNER JOIN dbo.PlayersTransactions AS pt WITH(NOLOCK) ON bp.BonusPolicyID = pt.BonusID
INNER JOIN dbo.Players AS p WITH(NOLOCK) ON bp.CasinoID = p.CasinoID
WHERE p.CasinoID > 0

INSERT #TempBonusPolicies (BonusPolicyID, CasinoID)
SELECT bp.BonusPolicyID, bp.CasinoID
FROM dbo.BonusPolicies AS bp WITH(NOLOCK)
INNER JOIN dbo.PlayersTransactions AS pt WITH(NOLOCK) ON bp.BonusPolicyID = pt.BonusID
INNER JOIN dbo.Players AS p WITH(NOLOCK) ON bp.CasinoID = p.CasinoID
WHERE p.CasinoID IN (0,1)

Please make sure that you understand WITH(NOLOCK) and don't use it as best practice every single time.

NOLOCK Is Bad And You Probably Shouldn't Use It. - Brent Ozar Unlimited®

1 Like