?
Solved

SQL: Correlated Subquery causing a massive performance hit

Posted on 2014-08-20
16
Medium Priority
?
161 Views
Last Modified: 2014-08-22
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

0
Comment
Question by:pzozulka
  • 10
  • 6
16 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40275241
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40275339
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40275349
line 81 & 83 change alias t to trans

WHEN t.WhenBooked

WHEN trans.WhenBooked

after line 115, add:

AND trans.WhenBooked  < RT.PeriodEndDate
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Author Comment

by:pzozulka
ID: 40276921
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40276966
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40277595
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40277621
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40277639
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40277679
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40277728
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40277747
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40277825
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40277856
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40277960
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40277996
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40278002
goodness, I see it now, ignore the comment above please
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question