improve query performance

nicebuddyraghu 2016-04-06 01:13:11

DECLARE @InstanceName NVARCHAR(128)
SET NOCOUNT ON;
DECLARE @IsAppendCasinoId BIT, @IsPlayerClubId BIT, @IsAppendCustomerId BIT;
SET @IsAppendCustomerId =
(
SELECT IsAppendCasinoId
FROM Configuration..AppendCasinoIdInfo
WHERE TableName = N'dCustomer'
);
DECLARE @Rows INT= 0, @IsActive BIT= 1;
SELECT @Rows = [rows],
@IsActive = LF.IsActive
FROM SYS.PARTITIONS SP(NOLOCK)
INNER JOIN Configuration..LibraryInformation LF(NOLOCK) ON OBJECT_NAME(SP.OBJECT_ID) = LF.TableName
WHERE OBJECT_NAME(OBJECT_ID) = 'SFPC4'
AND InstanceName = @InstanceName;
WITH CompBalanceCTE
AS (SELECT CASE
WHEN @IsAppendCustomerId = 1
THEN CAST(CAST(ISNULL(S1.CASINOID, '') AS NVARCHAR)+CAST(S1.ACCTC4 AS NVARCHAR) AS NVARCHAR(50))
ELSE CAST(S1.ACCTC4 AS NVARCHAR(50))
END AS PLAYERACCOUNTNUMBER,
CAST(CASE S1.DHYRC4
WHEN 0
THEN '1900-01-01'
ELSE CONVERT( VARCHAR(16), DATEADD(DAY, S1.DHYRC4 – 1, '1/1/1900'), 111)
END AS DATETIME) AS GAMINGDATE,
MAX(S1.CMP$C4) COMPBALANCE,
CAST(S1.CPLOC4 + S1.PRLOC4 AS NVARCHAR(50)) AS CasinoId
FROM ISSFPC4 S1(NOLOCK)
INNER JOIN
(
SELECT ACCTC4,
DHYRC4,
MAX(RIGHT('000000'+CONVERT( VARCHAR, TIMEC4), 6)) AS TIMEC4
FROM ISSFPC4(NOLOCK)
WHERE ACCTC4 > 0
AND DHYRC4 > 0
GROUP BY ACCTC4,
DHYRC4
) S2 ON S1.ACCTC4 = S2.ACCTC4
AND S1.DHYRC4 = S2.DHYRC4
AND S1.TIMEC4 = S2.TIMEC4
INNER JOIN BallyBi.dbo.dCasino Config ON CAST(S1.CPLOC4 + S1.PRLOC4 AS NVARCHAR(50)) = Config.CasinoId
WHERE @Rows > 0
AND @IsActive = 1
GROUP BY CASE
WHEN @IsAppendCustomerId = 1
THEN CAST(CAST(ISNULL(S1.CASINOID, '
') AS NVARCHAR)+CAST(S1.ACCTC4 AS NVARCHAR) AS NVARCHAR(50))
ELSE CAST(S1.ACCTC4 AS NVARCHAR(50))
END,
S1.DHYRC4,
S1.TIMEC4,
CAST(S1.CPLOC4 + S1.PRLOC4 AS NVARCHAR(50))),
PointsBalanceCTE
AS (SELECT CASE
WHEN @IsAppendCustomerId = 1
THEN CAST(CAST(ISNULL(S1.CASINOID, '') AS NVARCHAR)+CAST(S1.ACCTBT AS NVARCHAR) AS NVARCHAR(50))
ELSE CAST(S1.ACCTBT AS NVARCHAR(50))
END AS PLAYERACCOUNTNUMBER,
CAST(CASE S1.DHYRBT
WHEN 0
THEN '1900-01-01'
ELSE CONVERT( VARCHAR(16), DATEADD(DAY, S1.DHYRBT – 1, 1 / 1 / 1900), 111)
END AS DATETIME) AS GAMINGDATE,
MAX(S1.CSAVBT) POINTSBALANCE,
CAST(CPTRBT + PRTRBT AS NVARCHAR(50)) AS CASINOID
FROM ISSFPBT S1(NOLOCK)
INNER JOIN
(
SELECT ACCTBT,
DHYRBT,
MAX(RIGHT('000000'+CONVERT( VARCHAR, TIMEBT), 6)) AS TIMEBT
FROM ISSFPBT(NOLOCK)
GROUP BY ACCTBT,
DHYRBT
) S2 ON S1.ACCTBT = S2.ACCTBT
AND S1.DHYRBT = S2.DHYRBT
AND S1.TIMEBT = S2.TIMEBT
INNER JOIN BallyBi.dbo.dCasino Config ON CAST(CPTRBT + PRTRBT AS NVARCHAR(50)) = Config.CasinoId
WHERE @Rows > 0
AND @IsActive = 1
GROUP BY CASE
WHEN @IsAppendCustomerId = 1
THEN CAST(CAST(ISNULL(S1.CASINOID, '
') AS NVARCHAR)+CAST(S1.ACCTBT AS NVARCHAR) AS NVARCHAR(50))
ELSE CAST(S1.ACCTBT AS NVARCHAR(50))
END,
S1.DHYRBT,
S1.TIMEBT,
CAST(CPTRBT + PRTRBT AS NVARCHAR(50)))
SELECT CASE
WHEN @IsAppendCustomerId = 1
THEN CAST(CAST(ISNULL(CFPCN.CasinoId, '') AS NVARCHAR)+CAST(CFPCN.ACCTCN AS NVARCHAR) AS NVARCHAR(101))
ELSE CAST(CFPCN.ACCTCN AS NVARCHAR(101))
END AS CustomerId,
CAST('
' AS NVARCHAR) AS InActive,
CASE
WHEN DOBICN < '1900-01-01' THEN '1900-01-01' ELSE CAST(DOBICN AS DATETIME) END AS BirthDate, CASE WHEN @IsAppendCustomerId = 1 THEN CAST(CAST(ISNULL(CFPCN.CasinoId, '') AS NVARCHAR)+CAST(CFPCN.ACCTCN AS NVARCHAR) AS NVARCHAR(50))
ELSE CAST(CFPCN.ACCTCN AS NVARCHAR(50))
END AS PlayerAccountNumber,
CAST('
' AS NVARCHAR) AS Login,
MIN(CAST(CASE
WHEN CAST(CFPPAS.SVALPAS AS INT) = 0
THEN '1900-01-01'
ELSE CONVERT(VARCHAR(16), DATEADD(day, (CAST(CFPPAS.SVALPAS AS INT)) – 1, 1 / 1 / 1900), 111)
END AS DATETIME)) AS TripStart,
MAX(CAST(CASE
WHEN CAST(CFPPAS.SVALPAS AS INT) = 0
THEN '1900-01-01'
WHEN CFPPAS.DCNTPAS <= 0 THEN CONVERT(VARCHAR(16), DATEADD(day, (CAST(CFPPAS.SVALPAS AS INT)) - 1, 1 / 1 / 1900), 111) ELSE CONVERT(VARCHAR(16), DATEADD(day, (CAST(CFPPAS.SVALPAS AS INT) + CFPPAS.DCNTPAS) - 1, 1 / 1 / 1900), 111) END AS DATETIME)) AS TripEnd, PointsBalanceCTE.PointsBalance, CAST(0 AS INT) AS AdjCompCr, CAST(0 AS INT) AS AdjCompDr, CAST(0 AS INT) AS CompUsed, CompBalanceCTE.COMPBALANCE, CAST(0 AS INT) AS ExpireComp, C.Casinoid AS CasinoId, C.Casinoid AS CasinoCode, CAST('' AS NVARCHAR) ClubLevelId,
CAST('
' AS NVARCHAR) [ClubState],
CAST('' AS NVARCHAR) ClubLevelCode,
MAX(CASE
WHEN @IsAppendCasinoId = 1
AND CFPLR.RPCDLR IS NOT NULL
AND LEN(CFPLR.RPCDLR) <> 0
THEN CAST(CAST(ISNULL(CFPLR.CPIDLR + CFPLR.PRIDLR, '
') AS NVARCHAR)+'-'+ISNULL(CAST(CFPLR.RPCDLR AS NVARCHAR), '') AS NVARCHAR(101))
ELSE CAST(ISNULL(CAST(CFPLR.RPCDLR AS NVARCHAR), '
') AS NVARCHAR(101))
END) AS HostEmployeeId,
ISNULL(Ballybi.dbo.ufnCreateIntKey(C.CasinoKey, CAST(CFPCN.ACCTCN AS INT)), -1) AS CustomerKey,
CASE
WHEN DOBICN < '1900-01-01' THEN-1 ELSE DATEDIFF(yyyy, CAST(DOBICN AS DATETIME), GETDATE()) END AS Age, CAST(-1 AS INT) AS AgeRangeKey, CAST('' AS NVARCHAR(101)) AS AreaCode,
CAST(0 AS DECIMAL(11, 2)) AS AverageBet,
CAST(-1 AS INT) AS AverageBetRangeKey,
CAST(-1 AS INT) AS CompBalanceRangeKey,
CAST('Not Entered' AS NVARCHAR(1000)) AS CustomerAddress,
CAST(-1 AS INT) AS CustomerAddressKey,
CAST(0 AS DECIMAL(18, 9)) AS Distance,
CAST(-1 AS INT) AS DistanceRangeKey,
CAST('Not Entered' AS NVARCHAR(255)) AS EmailAddress,
CAST(0 AS INT) AS Frequency,
CAST(-1 AS INT) AS FrequencyRangeKey,
CAST(-1 AS INT) AS HostEmployeeKey,
CAST('No' AS NVARCHAR(10)) AS IsNoEmail,
CAST('No' AS NVARCHAR(10)) AS IsNoPhone,
CAST('
' AS NVARCHAR(50)) AS Market,
CAST(0 AS INT) AS Monetary,
CAST(-1 AS INT) AS MonetaryRangeKey,
CAST('' AS NVARCHAR(50)) AS MostVisitedCasinoId,
CAST(-1 AS INT) AS PlayerClubKey,
CAST('Not Entered' AS NVARCHAR(64)) AS PlayerClubName,
CAST(-1 AS INT) AS PointBalanceRangeKey,
CAST(0 AS INT) AS PointsEarnedOnFirstTrip,
CAST(0 AS INT) AS Recency,
CAST(-1 AS INT) AS RecencyRangeKey,
CAST(0 AS INT) AS TripInterval,
CAST(-1 AS INT) AS TripIntervalRangeKey,
CAST('
' AS NVARCHAR(50)) AS PhoneNumber,
GETDATE() AS CreatedDtm,
GETDATE() AS ModifiedDtm,
CAST('Unknown' AS NVARCHAR(8)) AS SourceRecordStatus,
CAST('1900-01-01' AS DATETIME) SourceRecordTime,
CAST('' AS NVARCHAR(50)) AS LoyaltyProgramId,
CAST(-1 AS INT) AS PostalCodeKey,
CAST('No' AS NVARCHAR(10)) AS IsCall,
CAST('No' AS NVARCHAR(10)) AS IsSMS,
CAST('No' AS NVARCHAR(10)) AS IsEmailSend,
CAST('
' AS NVARCHAR(50)) AS PhoneNumberCall,
CAST('' AS NVARCHAR(50)) AS PhoneNumberSMS,
CAST('
' AS NVARCHAR(50)) AS HomeSite
FROM ISCFPCN CFPCN
INNER JOIN Configuration..Casino C ON C.CasinoId = CFPCN.CasinoId
LEFT JOIN ISCFPPAS CFPPAS ON CFPPAS.ACCTPAS = CFPCN.ACCTCN
LEFT JOIN ISCFPLR CFPLR ON CFPLR.ACCTLR = CFPPAS.ACCTPAS
LEFT JOIN PointsBalanceCTE ON PointsBalanceCTE.PLAYERACCOUNTNUMBER = CFPPAS.ACCTPAS
LEFT JOIN CompBalanceCTE ON CompBalanceCTE.PLAYERACCOUNTNUMBER = CFPPAS.ACCTPAS
GROUP BY CFPCN.ACCTCN,
CFPCN.CasinoId,
C.CasinoId,
C.CasinoKey,
C.CasinoName,
CFPCN.DOBICN,
PointsBalanceCTE.PointsBalance,
CompBalanceCTE.COMPBALANCE;

SQLkiwi 2016-04-06 02:34:03
Are you able to provide a post-execution (actual) execution plan? Preferably one collected by running the query directly in Plan Explorer so we get extra performance information?
SQLkiwi 2016-04-06 02:55:43
Obvious things on the basis of the estimated execution plan alone:

  1. The query is trying to do too much in one statement. The chances of getting good estimates, and consequently a good plan are diminished by this.
  2. There is a missing index report on the plan that you should evaluate.
  3. The query uses a scalar user-defined function.
  4. The nested loop join with a table spool underneath may not perform well if row estimates are wrong.

I would start by running each CTE separately, to assess its performance and the plan generated. This may identify missing indexes or other scope for optimizations.

Once the CTEs run reasonably well, try materializing their results in separate temporary tables. Rewrite the query to remove the CTEs and use the temporary tables instead. Create useful indexes on the temporary tables as necessary, including a clustered index. You may find the the automatically-created statistics on the temporary tables, row count information, and new indexes, result in good performance.

Rewrite the scalar UDF as an in-line UDF for improved performance. The effect my be dramatic if the current execution plans ends up invoking the scalar function many times.

Even if the final query must be a single block for some reason, rewriting using temporary tables can give you important insights into the target plan shape needed, and the steps you may need to take to get there.

Also try the final query with OPTION (RECOMPILE). This will allow the values of the various variables to be embedded in the plan at runtime, which may improve performance.

Please provide an actual execution plan if you can.