December 13, 2017 at 7:29 am
Folks,
I am (trying to) create an XML data file from SQL that has the following structure
<Letters>:N
<Letter>:N
<LetterType>:1
<AccountID>:1
<ContactName>:1
<Address>:1
<AddressLines>:N
<LetterDate>:1
<ReviewDate>:1
<TCOMDate>:1
<RPICompare>:0-1
<RPICompareMonth>:1
<RPIUpliftText>:1
<RPIRate>:1
<ChargeFrequency>:1
<NewTotalCharge>:1
<ChargeGroups>:1
<ChargeGroup>:N
<GroupDescription>:1
<OldGroupCharge>:1
<NewGroupCharge>:1
<Charges>:1
<Charge>:N
<ChargeDescription>:1
<ChargeOldValues>:1
<ChargeValueExVat>:1
<ChargeValueVAT>:1
<ChargeValueGross>:1
<ChargeNewValues>:1
<ChargeValueExVat>:1
<ChargeValueVAT>:1
<ChargeValueGross>:1
The code is built using lots of correlated sub-queries for the nested XML elements SELECT
/*<Letters><Letter>*/
AL.LetterCode AS 'LetterType'
,AL.Account_ID AS 'AccountID'
,ACCINF.Contact_Name AS 'ContactName'
,
( /*<Address>*/
SELECT
ADDR.Property_Number_or_Name
,ADDR.Property_or_Premises_Name
...
FROM
Addresses ADDR
WHERE
ADDR.Asset_ID = ACCINF.Asset_ID
FOR XML PATH ('Address'), type
)
, CONVERT(VARCHAR(100),@LetterDate,106) AS 'LetterDate'
, CONVERT(VARCHAR(100),@ReviewDate,106) AS 'ReviewDate'
,
(
SELECT CONVERT(NVARCHAR(100),MIN(TT.FirstDate), 106) FROM #tt_TCOMDATES TT WHERE TT.ChargeFrequency = AL.FreqCode AND TT.FirstDate >= AL.AccountStartDate
) AS 'TCOMDate'
,
(
SELECT
CASE
WHEN ADDR.Street = 'XXX' THEN 'June'
ELSE 'September'
END AS 'RPICompareMonth'
,CASE
WHEN ADDR.Street = 'XXX' THEN ''
ELSE ' plus 0.5% '
END AS 'RPIUpliftText'
,CASE
WHEN ADDR.Street = 'XXX' THEN @RPIJune
ELSE @RPISeptember
END AS 'RPIRate'
FROM
Addresses ADDR
WHERE
ADDR.Asset_ID = ACCINF.Asset_ID
FOR XML PATH ('RPICompare'), TYPE
)
,CASE
WHEN AL.FreqCode IN ('W','L') THEN 'week'
WHEN AL.FreqCode IN ('M','R') THEN 'month'
ELSE 'year'
END AS 'ChargeFrequency'
,
(
SELECT
SUM(COALESCE(NN.Curr_Value,0)+COALESCE(NN.Curr_vat,0))
FROM
CTE_NOW_NEXT NN
WHERE
NN.Account_ID = AL.Account_ID
) NewTotalCharge
,
(
SELECT
NN.ChargeGroupDescription AS GroupDescription
,SUM(COALESCE(NN.Prev_Value,0)+COALESCE(NN.Prev_vat,0)) AS OldGroupCharge
,SUM(COALESCE(NN.Curr_Value,0)+COALESCE(NN.Curr_vat,0)) AS NewGroupCharge
FROM
CTE_NOW_NEXT NN
WHERE
NN.Account_ID = AL.Account_ID
GROUP BY
NN.ChargeGroupDescription
FOR XML PATH('ChargeGroup'), TYPE
) ChargeGroups
,
( /*<--- This is what kills it ! --->*/
SELECT
NN.ChargeCodeDescription AS ChargeDescription
,(
SELECT
COALESCE(NN2.Prev_Value,0) AS 'ChargeValueExVat'
,COALESCE(NN2.Prev_Vat,0) AS 'ChargeValueVat'
,COALESCE(NN2.Prev_Value,0)+COALESCE(NN2.Prev_Vat,0) AS 'ChargeValueGross'
FROM
CTE_NOW_NEXT NN2
WHERE
NN2.ChargeCodeDescription = NN.ChargeCodeDescription
AND
NN2.Account_ID = NN.Account_ID
FOR XML PATH('ChargeOldValues'), TYPE
)
,
(
SELECT
COALESCE(NN2.Curr_Value,0) AS 'ChargeValueExVat'
,COALESCE(NN2.Curr_Vat,0) AS 'ChargeValueVat'
,COALESCE(NN2.Curr_Value,0)+COALESCE(NN2.Curr_Vat,0) AS 'ChargeValueGross'
FROM
CTE_NOW_NEXT NN2
WHERE
NN2.ChargeCodeDescription = NN.ChargeCodeDescription
AND
NN2.Account_ID = NN.Account_ID
FOR XML PATH('ChargeNewValues'), TYPE
)
FROM
CTE_NOW_NEXT NN
WHERE
NN.Account_Reference_TG_VC = AL.Account_ID
AND
NN.Account_Line_TG_VC = AL.Account_Line_TG_VC
FOR XML PATH('Charge'), TYPE
) Charges
,AL.AccountStartDate
FROM
#tt_AccountLetters AL
JOIN
Account_Details_V ACCINF ON ACCINF.Account_ID = AL.Account_ID
JOIN
Rent_Accounts RA ON CAST(RA.ID AS NVARCHAR(100)) = CAST(AL.Account_ID AS NVARCHAR(100))
The code works and returns me the correct data for a limited number of accounts (200) in about 6 minutes, but I need to run it for 25K accounts. The real killer is the double nested sub-select for the <Charges><Charge>
for reference the CTE_NOW_NEXT is a Common table expression which is taking a global temp table and finding the last record BEFORE and first record AFTER a specific date using a Full OUTER JOIN as either side could be NULL and then generating a charge reference with before and after valuesCTE_NOW_NEXT AS
(
SELECT
COALESCE(P.AccountID, C.AccountID) AS AccountID
,COALESCE(P.ChargeCode, C.ChargeCode) AS 'ChargeCode'
,COALESCE(PREVCHG.Description, CURRCHG.Description) AS 'ChargeCodeDescription'
,CG.Description_VC AS 'ChargeGroupDescription'
,P.PREVIOUS_RANK
,C.CURRENT_RANK
,PREVCHG.ChargeEffectiveDate AS 'Prev_From_Date'
,PREVCHG.ChargeEffectiveToDate AS 'Prev_To_Date'
,CURRCHG.ChargeEffectiveDate AS 'Curr_From_Date'
,CURRCHG.ChargeEffectiveToDate AS 'Curr_To_Date'
, PREVCHG.Value AS 'Prev_Value'
,PREVCHG.VAT AS 'Prev_VAT'
,CURRCHG.Value AS 'Curr_Value'
,CURRCHG.VAT AS 'Curr_VAT'
FROM
CTE_CURRENT_CHARGE_ID C
FULL OUTER JOIN
CTE_PREVIOUS_CHARGE_ID P ON P.AccountID = C.AccountID
AND P.ChargeCode = C.ChargeCode
LEFT JOIN
##tt_Tenancy_Charges CURRCHG ON CURRCHG.AccountID = C.AccountID
AND CURRCHG.ChargeCode = C.ChargeCode
AND CURRCHG.RANK = C.CURRENT_RANK
LEFT JOIN
##tt_Tenancy_Charges PREVCHG ON PREVCHG.AccountID = P.AccountID
AND PREVCHG.ChargeCode = P.ChargeCode
AND PREVCHG.RANK = P.PREVIOUS_RANK
LEFT JOIN
Rent_ChargeCodes CC ON CC.ID = COALESCE(P.ChargeCode, C.ChargeCode)
LEFT JOIN
CTE_ChargeGroups CG ON CG.Posting_Code = CC.ChargeGroup
)
The temp table has decent indexes on it, Query plan is not suggesting any missing indexes.
SELECT * FROM CTE_NOW_NEXT returns 61K rows in 45 seconds which is not too bad seeing as it is joining 940K records in the global temp table to themselves 4x and has to COALESCE the accounts and codes from both sides of the cross apply
Is there a better way to write the nested XML code so that we are not using hidden RBAR, should I materialize the CTE_NOW_NEXT and provide indexing on that as well as the current global temp table, or is XML just slow?
December 13, 2017 at 8:19 am
materializing the CTE_NOW_NEXT seems to have done the trick. 5000 accounts now takes 2m14s including the materialization of the CTE.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply