Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

SQL: Convert correlated subquery to JOIN

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,

Open in new window

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

Open in new window

0
pzozulka
Asked:
pzozulka
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Scott PletcherSenior DBACommented:
Should be something like this; it may or may not perform better:

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 ,
      ActiveLoans.countAcitve,
      ...
FROM ...
LEFT OUTER JOIN
      (
            SELECT en.PartyId,
                DATEADD(MONTH, DATEDIFF(MONTH, 0, CuAc.AccountCreateDate), 0) AS AccountCreateDate,
                DATEADD(MONTH, DATEDIFF(MONTH, 0, CuAc.ArchiveDate), 0) AS ArchiveDate,                
                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                  
                  ((@reportType = 'Agent' AND en.EntityTypeId = 3 AND en.Code NOT LIKE '%T%') OR (@reportType = 'Submitted By'))
                  AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
                  AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select * from @PortfolioRegionList))
            GROUP BY en.PartyId,
                DATEADD(MONTH, DATEDIFF(MONTH, 0, CuAc.AccountCreateDate), 0),
                DATEADD(MONTH, DATEDIFF(MONTH, 0, CuAc.ArchiveDate), 0)            
      ) as ActiveLoans ON ActiveLoans.PartyId = RT.PartyId AND
          ActiveLoans.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0) AND
          ActiveLoans.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0))
...
0
 
DcpKingCommented:
One fun way to get wretched performance from the query engine is to give it huge complicated queries - it runs an algorithm for searching for the best solution that's very good, but that also has a "give up" clause if it's taking too long. You may well have hit that here.

My suggestion is to split your one huge query into 6 smaller ones - one for each segment that you have linked by left outer joins, and put each into a temp table (not a temp var). make sure that the temp tables are indexed nicely to assist with the joins, and then run the thing that you have, where it'll look very small and simple because you'll have pre-processed most of the data into the temp tables.

You should find that doing the joins between well-indexed temp tables ends up far faster then the subqueries you're using right now. It'll probably take you about an hour to recode, but it should be well worth the time.

hth

Mike

(PS don't forget to comment what each temp table is for, and why you're doing it!)
0
 
pzozulkaAuthor Commented:
DcpKing: what's a temp var?

Also, when you say are "indexed nicely", is it enough to create a primary key for the temp table? Like:
Create table #tmpTbl
(
RefID int identity(1,1) primary key,
PartyID int not null,
Etc.
)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only watching for your correlated query, I would say immediately to get rid of the '*'
	(
		SELECT COUNT(1) 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 PartyID_ColumnNameHere from @AgentsList))
			AND (@pPortfolioRegionList IS NULL OR en.PortfolioRegionId IN (select PortfolioRegionId_ColumnNameHere from @PortfolioRegionList))
	) as ActiveLoans,

Open in new window


en.Code NOT LIKE '%T%' --> What are the possible values for en.Code?
0
 
pzozulkaAuthor Commented:
I'm doing that to improve performance to eliminate unwanted to hopefully shrink rows returned. E.code could be two things: T### or A###. Example : T001, T174, A037, etc.
0
 
pzozulkaAuthor Commented:
Vitor, can I replace it with: CHARINDEX('T', column) = 0
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, then you can increase the performance to telling to SQL Server engine to search only code not beginning with 'T' instead of having a 'T' in any position.

Like this ---> en.Code NOT LIKE 'T%'
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Avoid the use of functions on table columns. NOT LIKE will be a better option.
0
 
DcpKingCommented:
pzozulka:

"temp var" ... sorry - typo! I meant "table variable" but obviously my fingers were having trouble keeping up!

"Indexed nicely" meaning index the temp tables on the fields that they'll end up getting joined on. If you have an order table, for example, you can give it a primary key of OrderNumber but that won't help when you're joining it most of the time using the CustomerNumber field! Put a non-unique index there too!

Never use functions if where clauses if you can possibly help it - your function will be called once for every single row in the table you're looking at! Also, depending on the type of function (esp if it's a UDF), the query analyser may not evaluate it properly, so may not make a good plan for it ......resulting in even worse performance!

hth

Mike
0
 
Scott PletcherSenior DBACommented:
>> If you have an order table, for example, you can give it a primary key of OrderNumber but that won't help when you're joining it most of the time using the CustomerNumber field! Put a non-unique index there too! <<

That's not best either.

In those types of situations, the CustomerNumber should be the clustered index, and the PK should be nonclus.
0
 
pzozulkaAuthor Commented:
ScottPletcher:

I tried implementing your approach, but am getting duplication of records:

Results of my correlated subquery: Correlated Subquery ResultsLeft Join (Derived Table) Results:
Disregard formatting on one of the PartyIDs. I accidently chose ShortDate formatting in Excel Left Join (Derived Table) Results
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now