SQL: Correlated Subquery causing a massive performance hit

I need help replacing a correlated subquery on line 53. It causes the report to run in 6:22 min. With it commented out, the report runs in 2 seconds.
DECLARE @startDate datetime = '4/1/2012'        
DECLARE @endDate datetime = '12/31/2012' 
DECLARE @reportType VARCHAR(50) = 'Agent'
DECLARE @monthlyCostPerAccount MONEY = 25.00
DECLARE @costOfFunds MONEY = 5.00

SELECT DISTINCT 
	act.GlAccountTypeId, act.Name AS AccountType, at.GlAbstractTypeId, at.Name AS AbstractType, amt.GlAccountingMapTypeId, 
	amt.Name AS AccountingMapType, a.GlAccountId, a.AccountName, ammt.GlAccountingMapModifierTypeId, ammt.Name AS GlAccountingMapModifierType
INTO 
	#tmpMap
FROM  
	(
		SELECT DISTINCT 
			GlAccountingMapTypeId, GlAbstractTypeId, GlAccountId, GlAccountingMapModifierTypeId
		FROM 
			GlAccountingMap
	) am
	JOIN GlAbstractType at ON am.GlAbstractTypeId = at.GlAbstractTypeId
	JOIN GlAccount a ON am.GlAccountId = a.GlAccountId
	JOIN GlAccountType act ON a.GlAccountTypeId = act.GlAccountTypeId
	JOIN GlAccountingMapType amt ON am.GlAccountingMapTypeId = amt.GlAccountingMapTypeId
	JOIN GlAccountingMapModifierType ammt ON am.GlAccountingMapModifierTypeId = ammt.GlAccountingMapModifierTypeId

ORDER BY amt.GlAccountingMapTypeId, at.GlAbstractTypeId, act.GlAccountTypeId, a.GlAccountId, ammt.GlAccountingMapModifierTypeId



;WITH
cteTally10 AS (
		SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
		SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
		SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
		FROM cteTally10 c1
		CROSS JOIN cteTally10 c2
),
cteEntities AS (
		SELECT DISTINCT PartyId
		FROM Entity
)

SELECT 
	RT.*, 
	CONVERT(VARCHAR,DATEPART(MONTH,RT.PeriodDate))+'/'+CONVERT(VARCHAR,DATEPART(YEAR,RT.PeriodDate)) as Period,
	DT.*,
	RF.ReferralAmount,
	dtGL.InterestIncome, dtGL.FeeIncome, dtGLWO.NETWriteOffs ,
--	dtAL.NumberAccounts, dtAl.acctnum
	(
	 (
		(SELECT
			SUM(ent.Amount) as SumBeforeStartPeriod
		 FROM Entity en
		 left join CustomerAccount CuAc on en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
															  WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
														 END)
		 left join GlTransaction t on t.PartyId = CuAc.PartyId
		 left join GlEntry ent on ent.GlTransactionId = t.GlTransactionId
		 left join GlAccount a on a.GlAccountId = ent.GlAccountId
		 WHERE 
			en.PartyId = RT.PartyId
			AND t.WhenBooked < RT.PeriodDate 
			AND a.AccountName = 'Accounts Receivable - Premiums'
		) +
		
		(SELECT
			SUM(ent.Amount) as SumBeforeEndPeriod
		 FROM Entity en
		 left join CustomerAccount CuAc on en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
															  WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
														 END)
		 left join GlTransaction t on t.PartyId = CuAc.PartyId
		 left join GlEntry ent on ent.GlTransactionId = t.GlTransactionId
		 left join GlAccount a on a.GlAccountId = ent.GlAccountId
		 WHERE 
			en.PartyId = RT.PartyId
			AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
			AND a.AccountName = 'Accounts Receivable - Premiums'
		)
	 ) / 2 
	) * -1 as AvgDailyBalance,
	
	@costOfFunds / 100 as CostofFunds,
	
	
	
	
	
	
FROM
(
	SELECT 
		c.PartyId, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally, 0) as PeriodDate
	FROM 
		cteTally100 t, cteEntities c
	WHERE 
		t.tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
) RT

--LEFT JOIN CustomerAccount CustAccount ON RT.PartyId = CustAccount.PartyId

LEFT JOIN 
( ..
...
) DT ON DT.PartyId = RT.PartyId AND 
  DT.createMonth >=  RT.PeriodDate AND
  DT.createMonth < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
...
...
DROP TABLE #tmpMap 

Open in new window


I tried using a LEFT JOIN on line 104 -- something similar to the below, but I cannot see RT.PartyId or RT.PeriodDate from inside the LEFT JOIN, so this approach doesn't seem to work either.

LEFT JOIN (
            SELECT
                  en.PartyId
                , SUM(CASE
                        WHEN t.WhenBooked < RT.PeriodDate THEN ent.Amount END) AS SumBeforeStartPeriod
                , SUM(ent.Amount)                                              AS SumBeforeEndPeriod
            FROM Entity en
                  LEFT JOIN CustomerAccount CuAc
                              ON en.PartyId = (CASE
                                          WHEN @reportType = 'Agent' THEN CuAc.AgentId
                                          WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById END)
                  INNER JOIN GlTransaction t
                              ON t.PartyId = CuAc.PartyId
                  INNER JOIN GlEntry ent
                              ON ent.GlTransactionId = t.GlTransactionId
                  INNER JOIN GlAccount a
                              ON a.GlAccountId = ent.GlAccountId
            
            WHERE a.AccountName = 'Accounts Receivable - Premiums'
                  AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
            
            GROUP BY
                  en.PartyId

         ) as data ON RT.PartyId = data.PartyId

Open in new window

LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
could you provide the complete existing query?

and,
why are you cross joining [cteTally100]  with [cteEntities] ?  (making a Cartesian product of those 2 CTEs)
0
PortletPaulfreelancerCommented:
Perhaps this will work for you. There are some notes in the sql, please read those.
/* dates should be specified in YYYYMMDD or next best, YYYY-MM-DD */

DECLARE @startDate datetime = '20120401'
DECLARE @endDate datetime = '20121231'
DECLARE @reportType varchar(50) = 'Agent'
DECLARE @monthlyCostPerAccount money = 25.00
DECLARE @costOfFunds money = 5.00

/* gathering account references, can the use of DISTINCT be improved ??  is it even needed ?? */

SELECT DISTINCT
      act.GlAccountTypeId
    , act.Name  AS AccountType
    , at.GlAbstractTypeId
    , at.Name   AS AbstractType
    , amt.GlAccountingMapTypeId
    , amt.Name  AS AccountingMapType
    , a.GlAccountId
    , a.AccountName
    , ammt.GlAccountingMapModifierTypeId
    , ammt.Name AS GlAccountingMapModifierType
INTO #tmpMap
FROM (
            SELECT DISTINCT
                  GlAccountingMapTypeId
                , GlAbstractTypeId
                , GlAccountId
                , GlAccountingMapModifierTypeId
            FROM GlAccountingMap
      ) am
      JOIN GlAbstractType at
                  ON am.GlAbstractTypeId = at.GlAbstractTypeId
      JOIN GlAccount a
                  ON am.GlAccountId = a.GlAccountId
      JOIN GlAccountType act
                  ON a.GlAccountTypeId = act.GlAccountTypeId
      JOIN GlAccountingMapType amt
                  ON am.GlAccountingMapTypeId = amt.GlAccountingMapTypeId
      JOIN GlAccountingMapModifierType ammt
                  ON am.GlAccountingMapModifierTypeId = ammt.GlAccountingMapModifierTypeId

/* why order this information here - seems wasteful */
ORDER BY
      amt.GlAccountingMapTypeId
      , at.GlAbstractTypeId
      , act.GlAccountTypeId
      , a.GlAccountId
      , ammt.GlAccountingMapModifierTypeId

;
WITH
      cteTally10
      AS (
            SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
            SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
            ),
      cteTally100
      AS (
                  /* smarter to restrict this to only the rows needed; before cross join */
                  SELECT
                        *
                  FROM (
                              SELECT
                                    ROW_NUMBER() OVER (ORDER BY c1.tally) - 1 AS tally
                              FROM cteTally10 c1
                                    CROSS JOIN cteTally10 c2
                        ) x
                  WHERE tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
            ),
      cteEntities
      AS (
                  SELECT DISTINCT
                        PartyId
                  FROM Entity
            )

SELECT
      RT.PartyId
    , RT.PeriodDate
    , SUM(CASE
            WHEN t.WhenBooked < RT.PeriodDate THEN trans.Amount END)    AS SumBeforeStartPeriod
    , SUM(CASE
            WHEN t.WhenBooked < RT.PeriodEndDate THEN trans.Amount END) AS SumBeforeEndPeriod
FROM (
            /* The cross join places every month against every partyid, to facilitate a subsequent left join */
            SELECT
                  c.PartyId
                , DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally, 0)     AS PeriodDate
                , DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally + 1, 0) AS PeriodEndDate
            FROM cteTally100 t
                  CROSS JOIN cteEntities c
      /* moved this where clause into CTE */
      -- WHERE t.tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
      ) AS RT
      LEFT JOIN (
                  SELECT
                        en.PartyId
                      , t.WhenBooked
                      , ent.Amount
                  FROM Entity en
                        LEFT JOIN CustomerAccount CuAc
                                    ON en.PartyId = (CASE
                                                WHEN @reportType = 'Agent' THEN CuAc.AgentId
                                                WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById END)
                        INNER JOIN GlTransaction t
                                    ON t.PartyId = CuAc.PartyId
                        INNER JOIN GlEntry ent
                                    ON ent.GlTransactionId = t.GlTransactionId
                        INNER JOIN GlAccount a
                                    ON a.GlAccountId = ent.GlAccountId

                  WHERE a.AccountName = 'Accounts Receivable - Premiums'
                        AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
            ) AS trans
                  ON RT.PartyId = trans.PartyId
/* 
include what you need to here 
*/
GROUP BY
      RT.PartyId
    , RT.PeriodDate
/* 
include what you need to here 
*/
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
line 81 & 83 change alias t to trans

WHEN t.WhenBooked

WHEN trans.WhenBooked

after line 115, add:

AND trans.WhenBooked  < RT.PeriodEndDate
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pzozulkaAuthor Commented:
I don't know if this is going to cause any problems, but the GROUP BY on line 120 does not include any of the DT.* fields from line 47 in my original post above.

>> why are you cross joining [cteTally100]  with [cteEntities] ?
What line are you referring to?


As requested, the full query is below.

IF EXISTS(SELECT null FROM tempdb..sysobjects WHERE NAME like '%tmpMap%')         
DROP TABLE #tmpMap        

DECLARE @startDate datetime = '4/1/2012'        
DECLARE @endDate datetime = '12/31/2012' 
DECLARE @reportType VARCHAR(50) = 'Agent'
DECLARE @monthlyCostPerAccount MONEY = 25.00
DECLARE @costOfFunds MONEY = 5.00

SELECT DISTINCT 
	act.GlAccountTypeId, act.Name AS AccountType, at.GlAbstractTypeId, at.Name AS AbstractType, amt.GlAccountingMapTypeId, 
	amt.Name AS AccountingMapType, a.GlAccountId, a.AccountName, ammt.GlAccountingMapModifierTypeId, ammt.Name AS GlAccountingMapModifierType
INTO 
	#tmpMap
FROM  
	(
		SELECT DISTINCT 
			GlAccountingMapTypeId, GlAbstractTypeId, GlAccountId, GlAccountingMapModifierTypeId
		FROM 
			GlAccountingMap
	) am
	JOIN GlAbstractType at ON am.GlAbstractTypeId = at.GlAbstractTypeId
	JOIN GlAccount a ON am.GlAccountId = a.GlAccountId
	JOIN GlAccountType act ON a.GlAccountTypeId = act.GlAccountTypeId
	JOIN GlAccountingMapType amt ON am.GlAccountingMapTypeId = amt.GlAccountingMapTypeId
	JOIN GlAccountingMapModifierType ammt ON am.GlAccountingMapModifierTypeId = ammt.GlAccountingMapModifierTypeId

ORDER BY amt.GlAccountingMapTypeId, at.GlAbstractTypeId, act.GlAccountTypeId, a.GlAccountId, ammt.GlAccountingMapModifierTypeId



;WITH
cteTally10 AS (
		SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
		SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
		SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
		FROM cteTally10 c1
		CROSS JOIN cteTally10 c2
),
cteEntities AS (
		SELECT DISTINCT PartyId
		FROM Entity
)

SELECT 
	RT.*, 
	CONVERT(VARCHAR,DATEPART(MONTH,RT.PeriodDate))+'/'+CONVERT(VARCHAR,DATEPART(YEAR,RT.PeriodDate)) as Period,
	DT.*,
	RF.ReferralAmount,
	dtGL.InterestIncome, dtGL.FeeIncome, 
	dtGLWO.NETWriteOffs ,
--	dtAL.NumberAccounts, dtAl.acctnum

	(
		SELECT COUNT(*) as countAcitve
		FROM Entity en
		left join CustomerAccount CuAc on en.PartyId = CuAc.AgentId
		WHERE
			en.PartyId = RT.PartyId
			and CuAc.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
			and (CuAc.ArchiveDate IS NULL OR CuAc.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0))
	) as ActiveLoans,
	
	(
		(SELECT
			SUM(ent.Amount) as SumBeforeStartPeriod
		 FROM Entity en
		 join CustomerAccount CuAc on en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
														 WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
													END)
		 join GlTransaction t on t.PartyId = CuAc.PartyId
		 join GlEntry ent on ent.GlTransactionId = t.GlTransactionId
		 join GlAccount a on a.GlAccountId = ent.GlAccountId
		 WHERE 
			en.PartyId = RT.PartyId
			AND t.WhenBooked < RT.PeriodDate 
			AND a.AccountName = 'Accounts Receivable - Premiums'
		) +
		
		(SELECT
			SUM(ent.Amount) as SumBeforeEndPeriod
		 FROM Entity en
		 join CustomerAccount CuAc on en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
														 WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
													END)
		 join GlTransaction t on t.PartyId = CuAc.PartyId
		 join GlEntry ent on ent.GlTransactionId = t.GlTransactionId
		 join GlAccount a on a.GlAccountId = ent.GlAccountId
		 WHERE 
			en.PartyId = RT.PartyId
			AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
			AND a.AccountName = 'Accounts Receivable - Premiums'
		)
	 ) / 2  as AvgDailyBalance,

	@costOfFunds / 100 as CostofFunds
	
FROM
(
	SELECT 
		c.PartyId, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally, 0) as PeriodDate
	FROM 
		cteTally100 t, cteEntities c
	WHERE 
		t.tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
) RT

LEFT JOIN 
(
	SELECT 
		e.PartyId																	as PartyId,
		MAX(CA.AccountNumber)														as AcctNumber,
		DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0)						as createMonth,
		DATEADD(day, -1, DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn) + 1 ,0))	as endMonth,
		SUM(CASE WHEN PFL.IsOriginal = 1 THEN 1 ELSE 0 END)							as OriginalLoan,
		SUM(CASE WHEN PFL.IsOriginal = 0 THEN 1 ELSE 0 END)							as APLoan,
		COUNT(PFL.PremiumFinanceLoanId)												as AllLoansThisMonth,
		COUNT(DISTINCT CA.PartyId)													as AllAccounts,
		SUM(CASE WHEN PFL.IsOriginal = 1 THEN PFL.AmountFinanced ELSE 0 END)		as OriginalLoanAmountFinanced,
		SUM(CASE WHEN PFL.IsOriginal = 0 THEN PFL.AmountFinanced ELSE 0 END)		as APLoanAmountFinanced,
		SUM(PFL.AmountFinanced)														as AllLoansAmountFinanced,
		ROUND(AVG(CONVERT(FLOAT,(PFL.[DownPayment]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])) /
		    (PFL.[TotalPremium]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])),4)	as AVGDownPaymentPercent,
		AVG(PFL.APR)																as AvgAPR,
		ROUND(SUM(CASE WHEN CA.PrimaryAccountStatusId = 2 THEN 1 ELSE 0 END) /
			CONVERT(FLOAT , COUNT(DISTINCT CA.PartyId)),4)							as CancelRatio
	    
	FROM 
		Entity e
		JOIN CustomerAccount CA ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CA.AgentId
													 WHEN @reportType = 'Submitted By' THEN CA.SubmittedById
												END)
		JOIN PremiumFinanceLoan PFL ON PFL.CustomerAccountId = CA.PartyId
		LEFT JOIN       
		(
			SELECT InPo.PremiumFinanceLoanId,SUM(InPo.EarnedTaxesFees) as TotalEarnedTaxFees
			FROM InsurancePolicy InPo
			GROUP BY InPo.PremiumFinanceLoanId
		) IP ON PFL.PremiumFinanceLoanId = IP.PremiumFinanceLoanId
		
	WHERE
		PFL.CreatedOn >= @startDate AND
		PFL.CreatedOn < DATEADD(DAY, 1, @endDate)
	    
	GROUP BY 
		e.PartyId,
		DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0),
		DATEADD(day, -1, DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn) + 1 ,0))
		
		
) DT ON DT.PartyId = RT.PartyId AND 
  DT.createMonth >=  RT.PeriodDate AND
  DT.createMonth < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
  
LEFT JOIN
(
	SELECT
		e.PartyId, SUM(DISTINCT ReFe.Amount) as ReferralAmount, DATEADD(MONTH, DATEDIFF(MONTH, 0, ReFe.DatePaid), 0) as DatePaid
		
	FROM
		Entity e
		JOIN CustomerAccount CuAc ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
													   WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
												  END)
		JOIN ReferralFee ReFe ON CuAc.PartyId = ReFe.CustomerAccountId
		
	WHERE 
		--RF.ReferralFeeTypeId NOT IN (5,6) AND  -- Probably needed, but is not specified in the requirements
		ReFe.DatePaid >= @startDate AND
		ReFe.DatePaid < DATEADD(DAY, 1, @endDate)
		
	GROUP BY
		e.PartyId, DATEADD(MONTH, DATEDIFF(MONTH, 0, ReFe.DatePaid), 0)
		
) RF ON RF.PartyId = RT.PartyId AND
  RF.DatePaid >= RT.PeriodDate AND
  RF.DatePaid < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0) 
  
LEFT JOIN
(
	SELECT  
		e.PartyId, 	
		
-- Interest Income	
		(					
			SUM(CASE WHEN m.GlAccountingMapTypeId = 1								-- Customer Accounts
						  AND GLe.GlAbstractAccountId = 1							-- Accrued Interest
						  AND GLe.GlAccountingEventId = 11							-- New Loan
					 THEN GLe.Amount ELSE 0 
				END)  +
			
			SUM(CASE WHEN m.GlAccountingMapTypeId = 1								-- Customer Accounts
						  AND GLe.GlAbstractAccountId = 4							-- Non-Refundable Fee
					 THEN GLe.Amount ELSE 0 
				END) +	
				
			SUM(CASE WHEN m.GlAccountingMapTypeId = 8								-- Interest Accrual / Refund
						  AND GLe.GlAbstractAccountId = 1							-- Accrued Interest
					 THEN GLe.Amount ELSE 0 
				END)  +
			
			SUM(CASE WHEN GLe.GlAbstractAccountId = 1000							-- Manual
						  AND GLe.GlAccountingEventId = 38							-- Manual Transaction
						  AND a.GlAccountTypeId = 4									-- Income
						  AND (a.AccountName ='Finance Charge Income' OR a.AccountName ='Interest Income')
					 THEN GLe.Amount ELSE 0 
				END)			
		) * -1 
		as InterestIncome,

-- Fee Income
		(	
			SUM(CASE WHEN GLe.GlAccountingEventId IN (9,10)							-- Late Fee Assessed / Late Fee Waived
						  AND m.GlAccountingMapTypeId = 4							-- Late Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +
				
			SUM(CASE WHEN GLe.GlAccountingEventId IN (2,3)							-- Cancellation Fee Assessed / Cancellation Fee Waived
						  AND m.GlAccountingMapTypeId = 5							-- Cancellation Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +

			SUM(CASE WHEN GLe.GlAccountingEventId IN (36,37)						-- Misc. Fee Assessed / Misc. Fee Waived
						  AND m.GlAccountingMapTypeId = 15							-- Misc. Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +
				
			SUM(CASE WHEN GLe.GlAccountingEventId IN (16,17)						-- Reinstatement Fee Assessed / Reinstatement Fee Waived
						  AND m.GlAccountingMapTypeId = 6							-- Reinstatement Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +
				
			SUM(CASE WHEN GLe.GlAccountingEventId IN (19,20)						-- Return Fee Assessed / Return Fee Waived
						  AND m.GlAccountingMapTypeId = 7							-- Return Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) 
		) * -1
		as FeeIncome,
			
		DATEADD(month, DATEDIFF(month,0,GLt.WhenBooked),0) as WhenBooked
													
	FROM 
		Entity e
		JOIN CustomerAccount CuAc ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
													   WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
												  END)
		JOIN GlTransaction GLt ON GLt.PartyId = CuAc.PartyId
		LEFT JOIN GlEntry GLe ON GLe.GlTransactionId = GLt.GlTransactionId
		LEFT JOIN #tmpMap m ON GLe.GlAbstractAccountId = m.GlAbstractTypeId
		LEFT JOIN GlAccount a on a.GlAccountId = GLe.GlAccountId
		
	WHERE 
		GLt.WhenBooked >= @startDate AND
		GLt.WhenBooked < DATEADD(DAY, 1, @endDate)
		
	GROUP BY 
		e.PartyId, DATEADD(month, DATEDIFF(month,0,GLt.WhenBooked),0)
	
) dtGL ON dtGL.PartyId = RT.PartyId AND
		  dtGL.WhenBooked >= RT.PeriodDate AND
		  dtGL.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0) 
		  
LEFT JOIN
(
	SELECT  
		ent.PartyId, DATEADD(month, DATEDIFF(month,0,GLtr.WhenBooked),0) as WhenBooked,
-- NetWriteOffs
		(
			SUM(CASE WHEN (GLen.GlAccountingEventId = 15				-- Negative Values
						   AND GLen.GlAbstractAccountId = 8)
					 THEN GLen.Amount ELSE 0 
			END) + 
			
			SUM(CASE WHEN (GLen.GlAccountingEventId = 23				-- Positive Values
						   AND GLen.GlAbstractAccountId = 8)
					 THEN GLen.Amount ELSE 0 
			END)
		)	
		as NetWriteOffs
		
	FROM 
		Entity ent
		JOIN CustomerAccount CusAcc ON ent.PartyId = (CASE WHEN @reportType = 'Agent' THEN CusAcc.AgentId
														   WHEN @reportType = 'Submitted By' THEN CusAcc.SubmittedById
													  END)
		JOIN GlTransaction GLtr ON GLtr.PartyId = CusAcc.PartyId
		left join GlEntry GLen ON GLen.GlTransactionId = GLtr.GlTransactionId
		
	WHERE 
		GLtr.WhenBooked >= @startDate AND
		GLtr.WhenBooked < DATEADD(DAY, 1, @endDate)

	GROUP BY 
		ent.PartyId, DATEADD(month, DATEDIFF(month,0,GLtr.WhenBooked),0)
		
) dtGLWO ON dtGLWO.PartyId = RT.PartyId AND
		  dtGLWO.WhenBooked >= RT.PeriodDate AND
		  dtGLWO.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0) 

ORDER BY RT.PartyId, RT.PeriodDate

Open in new window

0
pzozulkaAuthor Commented:
So what's the difference between your RT table and mine? It seems that they both accomplish the same exact thing. The result set below is the same for both versions of the RT derived table.

And to answer your question, this is needed to later do grouping by the PartyId column in Active Reports. If I didn't have that first PartyId column derived from the RT table, then I would have some rows as NULL from the actual Partyid from the retrieved from the Entity table.

Sample results
0
pzozulkaAuthor Commented:
Can you also help me replace the correlated subquery right above the original one?
	(
		SELECT COUNT(*) as countAcitve
		FROM Entity en
		left join CustomerAccount CuAc on en.PartyId = CuAc.AgentId
		WHERE
			en.PartyId = RT.PartyId
			and CuAc.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
			and (CuAc.ArchiveDate IS NULL OR CuAc.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0))
	) as ActiveLoans,

Open in new window

I tried replacing it with the following LEFT JOIN, but the query is running at this point for over 5 min.
SELECT ... , SUM(CASE WHEN dtAL.AccountCreateDate <= RT.PeriodEndDate AND (dtAL.ArchiveDate IS NULL OR dtAL.ArchiveDate > RT.PeriodEndDate) THEN 1 ELSE 0 END) AS ActiveLoans,
	
	( SUM(CASE WHEN dtADB.WhenBooked < RT.PeriodDate THEN dtADB.Amount END)
    +
      SUM(CASE WHEN dtADB.WhenBooked < RT.PeriodEndDate THEN dtADB.Amount END) 
	) / 2 AS AvgDailyBalance
FROM ...
...
LEFT JOIN
(
	SELECT
		en.PartyId,
		CuAc.AccountCreateDate,
		CuAc.ArchiveDate
		
	FROM
		Entity en
		JOIN CustomerAccount CuAc ON en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
														WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById 
												   END)
	WHERE
		CuAc.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
		AND (CuAc.ArchiveDate IS NULL OR CuAc.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1, 0))
		
) dtAL ON dtAL.PartyId = RT.PartyId AND
		  dtAL.AccountCreateDate < RT.PeriodEndDate

Open in new window

0
PortletPaulfreelancerCommented:
hi, I worked out what the cross join was for - sorry. You RT and my RT do exactly the same things, mine is just a tweak. I reduce the size of the tally CTE before cross joining it, and I include both a period start date and a period end date (which is actually day 1 of the next month). I then aggregate the detailed transactions using those date boundaries.

"The result set below is the same for both versions of the RT derived table." that's good!

I haven't yet looked at the other correlation, or the whole query. Back when I can be.
0
PortletPaulfreelancerCommented:
quick note:

I don't recall seeing that select count(*)... correlated subquery. Stripping down queries for us to handle is usually not a good thing IMHO.

This correlation, like its siblings, uses a left join when it does not need to. I implore you to be on guard for this. If you reference a left joined table in a where clause then it is likely you will disable that left join.
0
pzozulkaAuthor Commented:
In post ID: 40276921 I have posted the entire query. The count(*) is on line 57.

I have since went through the entire query and replaced all LEFT JOIN with JOIN wherever needed.
0
PortletPaulfreelancerCommented:
All the conditions necessary for that count exist in the revised query I provided earlier. However I'm not entirely sure what you expect from the count. I think what I have below will work for you but I cannot know for sure. It might be that you need to count(distinct ???something???) . Anyway, if you can get this count performed along with the existing calculations this would be good for performance.

in-here.pngThe code below is a revision of the above, changes from that are indicated, please merge carefully into your own query.
/* dates should be specified in YYYYMMDD or next best, YYYY-MM-DD */

DECLARE @startDate datetime = '20120401'
DECLARE @endDate datetime = '20121231'
DECLARE @reportType varchar(50) = 'Agent'
DECLARE @monthlyCostPerAccount money = 25.00
DECLARE @costOfFunds money = 5.00

/* gathering account references, can the use of DISTINCT be improved ??  is it even needed ?? */

SELECT DISTINCT
      act.GlAccountTypeId
    , act.Name  AS AccountType
    , at.GlAbstractTypeId
    , at.Name   AS AbstractType
    , amt.GlAccountingMapTypeId
    , amt.Name  AS AccountingMapType
    , a.GlAccountId
    , a.AccountName
    , ammt.GlAccountingMapModifierTypeId
    , ammt.Name AS GlAccountingMapModifierType
INTO #tmpMap
FROM (
            SELECT DISTINCT
                  GlAccountingMapTypeId
                , GlAbstractTypeId
                , GlAccountId
                , GlAccountingMapModifierTypeId
            FROM GlAccountingMap
      ) am
      JOIN GlAbstractType at
                  ON am.GlAbstractTypeId = at.GlAbstractTypeId
      JOIN GlAccount a
                  ON am.GlAccountId = a.GlAccountId
      JOIN GlAccountType act
                  ON a.GlAccountTypeId = act.GlAccountTypeId
      JOIN GlAccountingMapType amt
                  ON am.GlAccountingMapTypeId = amt.GlAccountingMapTypeId
      JOIN GlAccountingMapModifierType ammt
                  ON am.GlAccountingMapModifierTypeId = ammt.GlAccountingMapModifierTypeId

/* why order this information here - seems wasteful */
ORDER BY
      amt.GlAccountingMapTypeId
      , at.GlAbstractTypeId
      , act.GlAccountTypeId
      , a.GlAccountId
      , ammt.GlAccountingMapModifierTypeId

;
WITH
      cteTally10
      AS (
            SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
            SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
            ),
      cteTally100
      AS (
                  /* smarter to restrict this to only the rows needed; before cross join */
                  SELECT
                        *
                  FROM (
                              SELECT
                                    ROW_NUMBER() OVER (ORDER BY c1.tally) - 1 AS tally
                              FROM cteTally10 c1
                                    CROSS JOIN cteTally10 c2
                        ) x
                  WHERE tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
            ),
      cteEntities
      AS (
                  SELECT DISTINCT
                        PartyId
                  FROM Entity
            )

SELECT
      RT.PartyId
    , RT.PeriodDate
    , SUM(CASE
            WHEN trans.WhenBooked < RT.PeriodDate THEN trans.Amount END)                                  AS SumBeforeStartPeriod
    , SUM(CASE
            WHEN trans.WhenBooked < RT.PeriodEndDate THEN trans.Amount END)                               AS SumBeforeEndPeriod
-- changes                      
    , COUNT(CASE 
                WHEN trans.AccountCreateDate < RT.PeriodEndDate 
			     AND (trans.ArchiveDate IS NULL OR trans.ArchiveDate >= RT.PeriodEndDate) THEN 1 END)     AS ActiveLoans 
FROM (
            /* The cross join places every month against every partyid, to facilitate a subsequent left join */
            SELECT
                  c.PartyId
                , DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally, 0)     AS PeriodDate
                , DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally + 1, 0) AS PeriodEndDate
            FROM cteTally100 t
                  CROSS JOIN cteEntities c
      /* moved this where clause into CTE */
      -- WHERE t.tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
      ) AS RT
      LEFT JOIN (
                  SELECT
                        en.PartyId
                      , t.WhenBooked
                      , ent.Amount
-- changes                      
                      , CuAc.AccountCreateDate
                      , CuAc.ArchiveDate
-- changes                      
                  FROM Entity en
                        LEFT JOIN CustomerAccount CuAc
                                    ON en.PartyId = (CASE
                                                WHEN @reportType = 'Agent' THEN CuAc.AgentId
                                                WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById END)
                        INNER JOIN GlTransaction t
                                    ON t.PartyId = CuAc.PartyId
                        INNER JOIN GlEntry ent
                                    ON ent.GlTransactionId = t.GlTransactionId
                        INNER JOIN GlAccount a
                                    ON a.GlAccountId = ent.GlAccountId

                  WHERE a.AccountName = 'Accounts Receivable - Premiums'
                        AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
            ) AS trans
                  ON RT.PartyId = trans.PartyId
                  AND trans.WhenBooked  < RT.PeriodEndDate
/* 
include what you need to here 
*/
GROUP BY
      RT.PartyId
    , RT.PeriodDate
/* 
include what you need to here 
*/
;

Open in new window

0
PortletPaulfreelancerCommented:
Hi. Initially I asked for the whole query so I could assess what "came after" what you had provided just in case there was something I needed. On a quick scan there are 3 more left joins (and an order by which I'll ignore).

In my revision to RT I include RT.PeriodEndDate
"DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0) " can be replaced by that field in the joins

      Line 179:   RF.DatePaid < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
      Line 268:               dtGL.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
      Line 305:               dtGLWO.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)

I think that's about it.
0
pzozulkaAuthor Commented:
Thanks. I don't think combining everything inside the derived table (LEFT JOIN on line 99) will work because my two correlated subqueries required different criteria -- for example:

WHERE a.AccountName = 'Accounts Receivable - Premiums'
                        AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)

This does not apply to my correlated subquery called ActiveLoans.

My own attempt (post ID: 40277595) to replace the correlated subquery resulted in over 6 min execution time and a memory error message in SQL. I think I'll leave that subquery in place for now because even with it in place, the entire query runs in only 12 seconds. Not ideal, but not so bad.

Thanks for the recommendations on line 179, 268, 305.
0
PortletPaulfreelancerCommented:
OK, that sounds reasonable
admit I was having difficulty getting my head around it - I find it hard to visualize without access to data

with the 12 second query; have you looked at the execution plan?
0
pzozulkaAuthor Commented:
To be honest, execution plans don't make much sense to me as I don't see the correlation to my actual query.

Tomorrow, my query will be tested on a production database with millions of records. We will see if it is still a 12 second query. If it doesn't meet expectations, I will create a new post for further optimization and a deeper look at my remaining correlated subquery.

Thanks much for your help.
0
PortletPaulfreelancerCommented:
no problem. I can read the execution plan however (if you have an inclination upload a .sqlplan file) if not don't worry.

There is one thing I noticed when I looked last time and I'm not sure if I introduced it or notis this right?There isn't much pont to that left join and that case expression as the effective join is via en.partyid anyway, and in fact I don't think that table is used at all in that subquery
      LEFT JOIN (
                  SELECT
                        en.PartyId
                      , t.WhenBooked
                      , ent.Amount
                  FROM Entity en
                        --LEFT JOIN CustomerAccount CuAc
looks unused to me     --ON en.PartyId = (CASE
                                                --WHEN @reportType = 'Agent' THEN CuAc.AgentId
                                                --WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById END)
                        INNER JOIN GlTransaction t
                                    ON t.PartyId = CuAc.PartyId
                        INNER JOIN GlEntry ent
                                    ON ent.GlTransactionId = t.GlTransactionId
                        INNER JOIN GlAccount a
                                    ON a.GlAccountId = ent.GlAccountId

                  WHERE a.AccountName = 'Accounts Receivable - Premiums'
                        AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
            ) AS trans
                  ON RT.PartyId = trans.PartyId

Open in new window

0
PortletPaulfreelancerCommented:
goodness, I see it now, ignore the comment above please
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.