Ugly left join

  • 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

  • I don't understand what you want? What sort of ideas are you looking for?

  • 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

  • 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.

     

     

     

  • 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