troubleshooting Question

SQL: Convert correlated subquery to JOIN

Avatar of pzozulka
pzozulka asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
11 Comments1 Solution1175 ViewsLast Modified:
Among other performance issues in my query, the correlated subquery below is causing a massive performance hit. I would like to try to re-write the subquery into a JOIN (derived table) to see if perhaps this improves the queries performance to satisfactory levels. I tried re-writing it into a JOIN, but my biggest issue is that inside the JOIN statements in the outer query, RT.PeriodDate and RT.PartyId are not visible.

Correlated Subquery
	(
		SELECT COUNT(*) as countAcitve
		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
			en.PartyId = RT.PartyId
			AND ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
			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))
			AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
			AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select * from @PortfolioRegionList))
	) as ActiveLoans,
Full Code:
-- STORED PROCEDURE pReport_AgentProfitability
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('pReport_AgentProfitability') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE pReport_AgentProfitability
GO

-- ************************************************************************************************************      
-- Case ####:
-- Description:       
-- 
-- ************************************************************************************************************ 
CREATE PROCEDURE [pReport_AgentProfitability]      
@pStartDate Datetime,      
@pEndDate Datetime,
@pReportType VARCHAR(50),     
@pPortfolioRegionList VARCHAR(8000),   
@pMonthlyCostPerAccount MONEY,
@pCostOfFunds MONEY,   
@pAgentList VARCHAR(8000),      
@pSortBy    VARCHAR(100) = ''      
AS      
BEGIN      
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED      
DECLARE @startDate DATETIME = @pStartDate       
DECLARE @endDate DATETIME = @pEndDate 
DECLARE @reportType VARCHAR(50) = @pReportType
DECLARE @monthlyCostPerAccount MONEY = @pMonthlyCostPerAccount
DECLARE @costOfFunds MONEY = @pCostOfFunds
IF (@pPortfolioRegionList ='') SET @pPortfolioRegionList = null  
IF (@pAgentList ='') SET @pAgentList = null
DECLARE @PortfolioRegionList TABLE
(
	portfolioId int not null primary key
)
INSERT INTO @PortfolioRegionList select * from dbo.createnumbertable(@pPortfolioRegionList)      
  --select * from @PortfolioRegionList 
       
DECLARE @AgentsList TABLE      
(      
	agentId int not null primary key      
)               
INSERT INTO @AgentsList select * from dbo.createnumbertable(@pAgentList)      
 --select * from @AgentsList      
    
IF OBJECT_ID('tempdb..#tmpMap') IS NOT NULL         
DROP TABLE #tmpMap            
    
-- Reference table containing GlAccountingMap     
CREATE TABLE #tmpMap        
(          
GlAccountTypeId INT NOT NULL,
AccountType VARCHAR(50),
GlAbstractTypeId INT,
AbstractType VARCHAR(50),
GlAccountingMapTypeId INT,
AccountingMapType VARCHAR(50),
GlAccountId INT,
AccountName VARCHAR(50),
GlAccountingMapModifierTypeId INT,
GlAccountingMapModifierType VARCHAR(50)    
)     


INSERT INTO #tmpMap
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
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 *
		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 e.PartyId, e.Name1, e.Code
		FROM Entity e
			 join CustomerAccount ca on e.PartyId = ca.AgentId
		WHERE ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
			  AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
			  AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
			  AND ca.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
			  AND (ca.ArchiveDate IS NULL OR ca.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0))
)

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 ,

	(
		SELECT COUNT(*) as countAcitve
		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
			en.PartyId = RT.PartyId
			AND ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
			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))
			AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
			AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select * from @PortfolioRegionList))
	) as ActiveLoans,
	
	@costOfFunds / 100 as CostofFunds,
	
	( 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 
(
	/* The cross join places every month against every partyid, to facilitate a subsequent left join */
    SELECT
		c.PartyId, c.Name1 as EntityName, c.Code,
		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
) AS RT

LEFT JOIN 
(
	SELECT
		en.PartyId,
		t.WhenBooked,
		ent.Amount
		
	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 a.AccountName = 'Accounts Receivable - Premiums'
		  AND ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
          AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
          AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
          AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select * from @PortfolioRegionList))
            
) AS dtADB ON RT.PartyId = dtADB.PartyId AND
	 dtADB.WhenBooked  < RT.PeriodEndDate

LEFT JOIN 
(
	SELECT 
		e.PartyId																	as PartyId,
		DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0)						as createMonth,
		DATEADD(day, -1, DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn) + 1 ,0))	as endMonth,
		COUNT(PFL.PremiumFinanceLoanId)												as AllLoansThisMonth,
		SUM(PFL.AmountFinanced)														as AllLoansAmountFinanced,
		ROUND(AVG(CONVERT(FLOAT,(PFL.[DownPayment]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])) /
		(PFL.[TotalPremium]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])) * 100,2)as AVGDownPaymentPercent,
		AVG(PFL.APR)																as AvgAPR,
		ROUND((SUM(CASE WHEN CA.PrimaryAccountStatusId = 2 THEN 1 ELSE 0 END) /
			CONVERT(FLOAT , COUNT(CA.PartyId))) * 100,2)							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)
		AND ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
		AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
		AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
	    
	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 < RT.PeriodEndDate
  
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)
		AND ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
		AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
		AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
		
	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 < RT.PeriodEndDate
  
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)
		AND ((@reportType = 'Agent' AND e.EntityTypeId = 3 AND e.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
		AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
		AND (@pPortfolioRegionList IS NULL OR e.PortfolioRegionId IN (select * from @PortfolioRegionList))
		
	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 < RT.PeriodEndDate
		  
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)
		AND ((@reportType = 'Agent' AND ent.EntityTypeId = 3 AND ent.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
		AND (@pAgentList IS NULL OR ent.PartyId IN (select * from @AgentsList))
		AND (@pPortfolioRegionList IS NULL OR ent.PortfolioRegionId IN (select * from @PortfolioRegionList))

	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 < RT.PeriodEndDate

GROUP BY RT.PartyId, RT.EntityName, RT.Code, RT.PeriodDate, RT.PeriodEndDate, 
		 DT.PartyId, DT.createMonth, DT.endMonth, DT.AllLoansThisMonth,
		 DT.AllLoansAmountFinanced, DT.AVGDownPaymentPercent, DT.AvgAPR, DT.CancelRatio,
		 RF.ReferralAmount,
		 dtGL.InterestIncome, dtGL.FeeIncome,
		 dtGLWO.NetWriteOffs

ORDER BY RT.EntityName, RT.PeriodDate

IF OBJECT_ID('tempdb..#tmpMap') IS NOT NULL        
DROP TABLE #tmpMap        

END
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros