January 13, 2020 at 3:37 pm
not looking for solution just maybe some ideas. This is from 3rd party vendor (i helped some with indexes and no i did not write it ) mostly goes against a factsummary table. Has several left outer joins with the subqueries (ugh), table is over 150 Million rows. Gets runs weekly so kind of a moving target. Vendor probably wont go for column store so need to be creative, best I can do is give them some ideas but it is their etl code.
--declare @p1 int
--set @p1=1
--exec sp_prepare @p1 output,NULL,N'-- Provider Summarization
---- H. Dillon 12/4/2017
DECLARE @ReportingDate date, @ReportingPeriodDate date, @ReportingPeriodDateKey int, @8MonthTrendDateKey int, @360DateKey int
, @Day31 int, @Day61 int, @Day91 int, @Day121 int, @Day151 int
SET @ReportingDate = '11/30/2019'
SET @ReportingPeriodDate = EOMONTH(@ReportingDate)
SET @ReportingPeriodDateKey = CAST(CONVERT(varchar(8),@ReportingPeriodDate,112) as int)
SET @8MonthTrendDateKey = CAST(CONVERT(varchar(8),EOMONTH(DATEADD(month,-8,@ReportingDate)),112) as int)
SET @360DateKey = CAST(CONVERT(varchar(8),DATEADD(day,-360,@ReportingPeriodDate),112) as int)
SET @Day31 = CAST(CONVERT(varchar(8),DATEADD(day,-30,@ReportingPeriodDate),112) as int)
SET @Day61 = CAST(CONVERT(varchar(8),DATEADD(day,-60,@ReportingPeriodDate),112) as int)
SET @Day91 = CAST(CONVERT(varchar(8),DATEADD(day,-90,@ReportingPeriodDate),112) as int)
SET @Day121 = CAST(CONVERT(varchar(8),DATEADD(day,-120,@ReportingPeriodDate),112) as int)
SET @Day151 = CAST(CONVERT(varchar(8),DATEADD(day,-150,@ReportingPeriodDate),112) as int)
SELECT SUM(act.Charges) Charges
, SUM(act.Payments) Payments
, SUM(act.BadDebtAdjustments) BadDebtAdjustments
, SUM(act.ContractualAdjustments) ContractualAdjustments
, SUM(act.ChargeAdjustments) ChargeAdjustments
, SUM(act.ExpenseAdjustments) ExpenseAdjustments
, SUM(act.RevenueDeductionAdjustments) RevenueDeductionAdjustments
, SUM(act.UnknownAdjustments) UnknownAdjustments
, SUM(act.Refunds) Refunds
, SUM(act.ARBalance) ARBalance
, SUM(act.ClosedClaimCount) ClosedClaimCount
, SUM(act.Pre360Balance) Pre360Balance
, SUM(act.Post360Balance) Post360Balance
, SUM(act.Pre360DebitsOnly) Pre360DebitsOnly
, SUM(act.Post360DebitsOnly) Post360DebitsOnly
, SUM(act.Day31Balance) Day31Balance
, SUM(act.Day61Balance) Day61Balance
, SUM(act.Day91Balance) Day91Balance
, SUM(act.Day121Balance) Day121Balance
, SUM(act.Day151Balance) Day151Balance
, SUM(act.Day151PlusBalance) Day151PlusBalance
, SUM(act.Day31DebitsOnly) Day31DebitsOnly
, SUM(act.Day61DebitsOnly) Day61DebitsOnly
, SUM(act.Day91DebitsOnly) Day91DebitsOnly
, SUM(act.Day121DebitsOnly) Day121DebitsOnly
, SUM(act.Day151DebitsOnly) Day151DebitsOnly
, SUM(act.Day151PlusDebitsOnly) Day151PlusDebitsOnly
, act.COIDMappingKey
, act.FinancialClassKey
, act.ReportingPeriodDateKey
, act.ContextID
, act.SourceSystemKey
FROM
(
SELECT SUM(Charges) Charges
, SUM(Payments) Payments
, SUM(BadDebtAdjustments) BadDebtAdjustments
, SUM(ChargeAdjustments) ChargeAdjustments
, SUM(ContractualAdjustments) ContractualAdjustments
, SUM(ExpenseAdjustments) ExpenseAdjustments
, SUM(RevenueDeductionAdjustments) RevenueDeductionAdjustments
, SUM(UnknownAdjustments) UnknownAdjustments
, SUM(Refunds) Refunds
, 0 as ARBalance
, 0 as ClosedClaimCount
, 0 as Pre360Balance
, 0 as Post360Balance
, 0 as Pre360DebitsOnly
, 0 as Post360DebitsOnly
, 0 as Day31Balance
, 0 as Day61Balance
, 0 as Day91Balance
, 0 as Day121Balance
, 0 as Day151Balance
, 0 as Day151PlusBalance
, 0 as Day31DebitsOnly
, 0 as Day61DebitsOnly
, 0 as Day91DebitsOnly
, 0 as Day121DebitsOnly
, 0 as Day151DebitsOnly
, 0 as Day151PlusDebitsOnly
, COALESCE(fma.COIDMappingKey,-1) COIDMappingKey
, COALESCE(fc.FinancialClassKey, -1) FinancialClassKey
, fma.ReportingPeriodDateKey
, fma.ContextID
, fma.SourceSystemKey
FROM dbo.FactMonthlyActivity fma
LEFT JOIN dbo.DimInsurance i ON i.InsuranceKey = fma.PrimaryInsuranceKey
LEFT JOIN UAT_PSG_MERGED.dbo.Lookup_FinancialClass lfc ON i.InsuranceGroup = lfc.InsuranceGroupName
LEFT JOIN dbo.DimFinancialClass fc ON fc.FinancialClassName = lfc.FinancialClassName
WHERE fma.ReportingPeriodDateKey = @ReportingPeriodDateKey AND fma.DeleteFlag = 0
GROUP BY fma.COIDMappingKey, fc.FinancialClassKey, fma.ReportingPeriodDateKey, fma.ContextID, fma.SourceSystemKey
UNION ALL
SELECT 0 as Charges
, 0 as Payments
, 0 as BadDebtAdjustments
, 0 as ChargeAdjustments
, 0 as ContractualAdjustments
, 0 as ExpenseAdjustments
, 0 as RevenueDeductionAdjustments
, 0 as UnknownAdjustments
, 0 as Refunds
, SUM(Balance) as ARBalance
, SUM(cc.ClosedClaim) ClosedClaimCount
, SUM(Pre360.Pre360Balance) Pre360Balance
, SUM(Post360.Post360Balance) Post360Balance
, SUM(Pre360Debit.Pre360Balance) Pre360DebitsOnly
, SUM(Post360Debit.Post360Balance) Post360DebitsOnly
, SUM(Day31.Day31Balance) Day31Balance
, SUM(Day61.Day61Balance) Day61Balance
, SUM(Day91.Day91Balance) Day91Balance
, SUM(Day121.Day121Balance) Day121Balance
, SUM(Day151.Day151Balance) Day151Balance
, SUM(Day151Plus.Day151PlusBalance) Day151PlusBalance
, SUM(Day31Debit.Day31DebitsOnly) Day31DebitsOnly
, SUM(Day61Debit.Day61DebitsOnly) Day61DebitsOnly
, SUM(Day91Debit.Day91DebitsOnly) Day91DebitsOnly
, SUM(Day121Debit.Day121DebitsOnly) Day121DebitsOnly
, SUM(Day151Debit.Day151DebitsOnly) Day151DebitsOnly
, SUM(Day151PlusDebit.Day151PlusDebitsOnly) Day151PlusDebitsOnly
, COALESCE(fcs.COIDMappingKey,-1) COIDMappingKey
, COALESCE(fc.FinancialClassKey, -1) FinancialClassKey
, @ReportingPeriodDateKey as ReportingPeriodDateKey
, fcs.ContextID
, fcs.SourceSystemKey
FROM (
SELECT SUM(Balance) Balance, COIDMappingKey, PrimaryInsuranceKey, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary WHERE DeleteFlag = 0 AND VoidFlag = 0
GROUP BY COIDMappingKey, PrimaryInsuranceKey, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) fcs
INNER JOIN
(
SELECT DISTINCT ClaimNumber, ContextID, SourceSystemKey, MAX(SequenceNumber) SequenceNumber
FROM dbo.FactClaimSummary
WHERE RecordInsertedDateKey <= @ReportingPeriodDateKey
GROUP BY ClaimNumber, ContextID, SourceSystemKey
) lc ON lc.ClaimNumber = fcs.ClaimNumber AND lc.ContextID = fcs.ContextID AND lc.SourceSystemKey = fcs.SourceSystemKey AND lc.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Pre360Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey >= @360DateKey
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Pre360 ON Pre360.ClaimNumber = fcs.ClaimNumber AND Pre360.ContextID = fcs.ContextID AND Pre360.SourceSystemKey = fcs.SourceSystemKey AND Pre360.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Post360Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey < @360DateKey
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Post360 ON Post360.ClaimNumber = fcs.ClaimNumber AND Post360.ContextID = fcs.ContextID AND Post360.SourceSystemKey = fcs.SourceSystemKey AND Post360.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Pre360Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey >= @360DateKey AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Pre360Debit ON Pre360Debit.ClaimNumber = fcs.ClaimNumber AND Pre360Debit.ContextID = fcs.ContextID AND Pre360Debit.SourceSystemKey = fcs.SourceSystemKey AND Pre360Debit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Post360Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey < @360DateKey AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Post360Debit ON Post360Debit.ClaimNumber = fcs.ClaimNumber AND Post360Debit.ContextID = fcs.ContextID AND Post360Debit.SourceSystemKey = fcs.SourceSystemKey AND Post360Debit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day31Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day31
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Day31 ON Day31.ClaimNumber = fcs.ClaimNumber AND Day31.ContextID = fcs.ContextID AND Day31.SourceSystemKey = fcs.SourceSystemKey AND Day31.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day61Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day61 AND ServiceDateKey <= @Day31
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Day61 ON Day61.ClaimNumber = fcs.ClaimNumber AND Day61.ContextID = fcs.ContextID AND Day61.SourceSystemKey = fcs.SourceSystemKey AND Day61.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day91Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day91 AND ServiceDateKey <= @Day61
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Day91 ON Day91.ClaimNumber = fcs.ClaimNumber AND Day91.ContextID = fcs.ContextID AND Day91.SourceSystemKey = fcs.SourceSystemKey AND Day91.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day121Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day121 AND ServiceDateKey <= @Day91
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Day121 ON Day121.ClaimNumber = fcs.ClaimNumber AND Day121.ContextID = fcs.ContextID AND Day121.SourceSystemKey = fcs.SourceSystemKey AND Day121.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day151Balance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day151 AND ServiceDateKey <= @Day121
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Day151 ON Day151.ClaimNumber = fcs.ClaimNumber AND Day151.ContextID = fcs.ContextID AND Day151.SourceSystemKey = fcs.SourceSystemKey AND Day151.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day151PlusBalance, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey <= @Day151
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) Day151Plus ON Day151Plus.ClaimNumber = fcs.ClaimNumber AND Day151Plus.ContextID = fcs.ContextID AND Day151Plus.SourceSystemKey = fcs.SourceSystemKey AND Day151Plus.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day31DebitsOnly, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day31 AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Day31Debit ON Day31Debit.ClaimNumber = fcs.ClaimNumber AND Day31Debit.ContextID = fcs.ContextID AND Day31Debit.SourceSystemKey = fcs.SourceSystemKey AND Day31Debit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day61DebitsOnly, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day61 AND ServiceDateKey <= @Day31 AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Day61Debit ON Day61Debit.ClaimNumber = fcs.ClaimNumber AND Day61Debit.ContextID = fcs.ContextID AND Day61Debit.SourceSystemKey = fcs.SourceSystemKey AND Day61Debit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day91DebitsOnly, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day91 AND ServiceDateKey <= @Day61 AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Day91Debit ON Day91Debit.ClaimNumber = fcs.ClaimNumber AND Day91Debit.ContextID = fcs.ContextID AND Day91Debit.SourceSystemKey = fcs.SourceSystemKey AND Day91Debit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day121DebitsOnly, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day121 AND ServiceDateKey <= @Day91 AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Day121Debit ON Day121Debit.ClaimNumber = fcs.ClaimNumber AND Day121Debit.ContextID = fcs.ContextID AND Day121Debit.SourceSystemKey = fcs.SourceSystemKey AND Day121Debit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day151DebitsOnly, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey > @Day151 AND ServiceDateKey <= @Day121 AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Day151Debit ON Day151Debit.ClaimNumber = fcs.ClaimNumber AND Day151Debit.ContextID = fcs.ContextID AND Day151Debit.SourceSystemKey = fcs.SourceSystemKey AND Day151Debit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT SUM(Balance) Day151PlusDebitsOnly, ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
WHERE ServiceDateKey <= @Day151 AND VoidFlag = 0
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
HAVING SUM(Balance) >= 0
) Day151PlusDebit ON Day151PlusDebit.ClaimNumber = fcs.ClaimNumber AND Day151PlusDebit.ContextID = fcs.ContextID AND Day151PlusDebit.SourceSystemKey = fcs.SourceSystemKey AND Day151PlusDebit.SequenceNumber = fcs.SequenceNumber
LEFT JOIN
(
SELECT MIN(CAST(ClosedClaim as int)) ClosedClaim, fcs.ClaimNumber, fcs.ContextID, fcs.SourceSystemKey, claims.SeqNo
FROM dbo.FactClaimSummary fcs
INNER JOIN
(
SELECT ClaimNumber, ContextID, SourceSystemKey, MAX(SequenceNumber) SeqNo FROM dbo.FactClaimSummary
WHERE ModifiedDateKey <= @ReportingPeriodDateKey AND ModifiedDateKey > @8MonthTrendDateKey
GROUP BY ClaimNumber, ContextID, SourceSystemKey
) claims ON claims.ClaimNumber = fcs.ClaimNumber AND claims.ContextID = fcs.ContextID AND claims.SourceSystemKey = fcs.SourceSystemKey AND claims.SeqNo = fcs.SequenceNumber
GROUP BY fcs.ClaimNumber, fcs.ContextID, fcs.SourceSystemKey, claims.SeqNo
) cc ON cc.ClaimNumber = fcs.ClaimNumber AND cc.ContextID = fcs.ContextID AND cc.SourceSystemKey = fcs.SourceSystemKey AND cc.SeqNo = fcs.SequenceNumber
LEFT JOIN dbo.DimInsurance i ON i.InsuranceKey = fcs.PrimaryInsuranceKey
LEFT JOIN UAT_PSG_MERGED.dbo.Lookup_FinancialClass lfc ON lfc.InsuranceGroupName = i.InsuranceGroup
LEFT JOIN dbo.DimFinancialClass fc ON fc.FinancialClassName = lfc.FinancialClassName
GROUP BY fcs.COIDMappingKey, fc.FinancialClassKey, fcs.ContextID, fcs.SourceSystemKey
) act
GROUP BY COIDMappingKey, FinancialClassKey, ReportingPeriodDateKey, ContextID, SourceSystemKey
January 13, 2020 at 4:02 pm
I don't understand what you want? What sort of ideas are you looking for?
January 13, 2020 at 4:12 pm
trying to figure out a way to deal with left joins and the sub queries, with this amount of data in the sub query leanings towards temp tables with some indexes added. Looking for more ideas/approaches problem is I need the vendor to change the sql for their ETL. Issue is this table growing about 5 million rows a month, this is not going to get better. Already takes almost an hour to run with a 4 proc 128 gig of ram, hate to just throw hardware at bad code
January 13, 2020 at 4:50 pm
On thing I've noticed from a very quick look as that the left joins seem to be very similar.
I would look at combining the LEFT JOINS into one query on dbo.FactClaimSummary by using CASE statements,
I've only listed three here but I think you could get them all into one statement with this type of logic:
LEFT JOIN (SELECT SUM(CASE WHEN ServiceDateKey < @360DateKey THEN Balance ELSE NULL END) AS Post360Balance,
SUM(CASE WHEN ServiceDateKey >= @360DateKey AND VoidFlag = 0 THEN Balance ELSE NULL END) AS Pre360Balance,
SUM(CASE WHEN ServiceDateKey >= @Day31 AND VoidFlag = 0 THEN Balance ELSE NULL END) AS Day31Balance,
ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
FROM dbo.FactClaimSummary
GROUP BY ContextID, SourceSystemKey, ClaimNumber, SequenceNumber
) x
Temporary table with index added post-population would probably also help with the performance, but you could still use this method of summing case statements to get better performance on the creation of the temporary tables.
January 13, 2020 at 6:28 pm
duh should have thought of case will test and see what optimizer thinks
Thx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply