Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query running slow

Posted on 2014-08-24
18
Medium Priority
?
428 Views
Last Modified: 2014-08-26
Attached is the SQL Execution Plan that shows a sort taking 80%. I don't really know how to interpret the execution plan, but decided to comment out the only two ORDER BYs in my code, and re-ran the query and execution plan, but it is still taking the same amount of time to run, and the sort still says it's taking 80%.
9080.sqlplan
0
Comment
Question by:pzozulka
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40282302
Hi. The sqlplan has truncated the SQL query - Could you provide the full query?

By the way, that 79.6% sort is probably from a 'group by' and not affected by the 'order by' clause.

It starts like this:
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  ....

Open in new window

0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40282861
Your code it's full of thiings that I wouldn't do it in SQL Server but I don't know the logic so I wouldn't focus on the rest of the code but only in the part of code that PortlePaul posted.

The order by it's almost on 77 millions of rows and 16GB of data.

How much time it takes for this query runs?
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40283519
Full stored procedure is below...

On one database with less records it took 12 seconds, on the production database it took 6 minutes. :)

-- 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:   
-- (Client id ##)      
-- ************************************************************************************************************ 
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
		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))
)

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 = CuAc.AgentId
		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
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.

 
LVL 4

Accepted Solution

by:
Randy Knight, MCM earned 1716 total points
ID: 40283711
On one database with less records it took 12 seconds, on the production database it took 6 minutes. :)

If I had a nickel for every time I heard this. :)  The important question is what is the expectation / SLA for this particular query.  Is this OLTP, user waiting on it, etc.?


In any case, the sort which is taking 80% of the cost is definitely caused by your group by.  For a query this complex, you might look into materializing some of the data into a temp table and indexing it.  The optimizer gets better and better at figuring this stuff out each release, but sometimes breaking the problem into smaller pieces is still the best solution.

The other thing I'd recommend is that you download and check out SQL Sentry Plan Explorer.  It's free and does a much better job of showing you what's going on in your plan.  For complex plans like this it is very helpful.

http://sqlsentry.com/products/plan-explorer/sql-server-query-view
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40283844
I just found a logical error on line 118.
join CustomerAccount CuAc on en.PartyId = CuAc.AgentId

Open in new window

This should be replaced with
join CustomerAccount CuAc on en.PartyId = (
CASE 
 WHEN @reportType = 'Agent' THEN CuAc.AgentId
 WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)

Open in new window


With that minor change, the query's running time on my test DB went from 12 seconds to 45 seconds. I have this type of CASE condition scattered in multiple locations, and might be what's slowing me down. Is there a more efficient way to rewrite that JOIN?
0
 
LVL 4

Assisted Solution

by:Randy Knight, MCM
Randy Knight, MCM earned 1716 total points
ID: 40283878
You're not going to use an index with that join the way it is.  Honestly, seeing the kinds of complexity here, this is a query that could benefit from parameterized dynamic SQL.  For the join above, you'd have a normal join once you decided which column to join it on and it would give you the opportunity to get an index seek.  

If you do this, make sure you use sp_executesql and use parameters.  Don't use EXEC().

Also, can you run SET STATISTICS IO ON and execute the query, then post the text?
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40283915
I'm not familiar with parameterized dynamic SQL. As such, if I wanted to convert the query to parameterized dynamic SQL, would I have to rewrite a lot of it. In other words, would this take a long time?

Not sure if this is what you were looking for in terms of the STATISTICS:

Table '#6465405F'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#3573B09E'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)
Table '#6835D143'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#3573B09E'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)
Table '#tmpMap_____________________________________________________________________________________________________________000000001A69'. Scan count 0, logical reads 61, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlAccountingMapType'. Scan count 0, logical reads 122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlAccountingMapModifierType'. Scan count 0, logical reads 122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlAbstractType'. Scan count 0, logical reads 122, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlAccountingMap'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlAccountType'. Scan count 0, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlAccount'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(61 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

(7980 row(s) affected)
Table 'Worktable'. Scan count 7981, logical reads 592298, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CustomerAccount'. Scan count 6, logical reads 15863, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Entity'. Scan count 1, logical reads 146885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InsurancePolicy'. Scan count 1, logical reads 675, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PremiumFinanceLoan'. Scan count 1, logical reads 555, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ReferralFee'. Scan count 1, logical reads 144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlEntry'. Scan count 3, logical reads 5190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlTransaction'. Scan count 3, logical reads 2436, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GlAccount'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tmpMap_____________________________________________________________________________________________________________000000001A69'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Open in new window

0
 
LVL 4

Assisted Solution

by:Randy Knight, MCM
Randy Knight, MCM earned 1716 total points
ID: 40283934
It may take a long time but I think you're headed down that road anyway.  Here is the BOL on sp_executesql which should get you pointed in the right direction.

http://msdn.microsoft.com/en-us/library/ms188001.aspx

On the STATISTICS IO output above, you can see that the worktable caused by your sort is almost 600K logical reads.  So this is clearly where your biggest problem is.  I would reiterate trying an indexed temp table to eliminate that sort.  

You're also doing a lot of IO on the Entity table so you might look at an index for that as well.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40284133
Not sure how to eliminate the group by and replace it with an indexed temp table. Here's why from a logical point of view.

The report needs to return rows of clientIDs and all months in a user specified date range.
Example: User running the report selects 1/1/2012 to 3/31/2012

ClientId     Month      a.col1    b.col2    c.col3    d.col4      ...
1                1/2012       5             6             25       34
1                 2/2012      null        null         null     null
1                 3/2012      3             7             11       19
2                 1/2012      4             8             7          6
2                 2/2012      11           20            5         7
2                 3/2012      null         null         3         null

The point is I first gather all ClientIDs and Months, and then use LEFT JOINs (derived tables) to calculate col1, col2, col3, col4, and so on. I JOIN these derived tables on the ClientID and on the MONTH. The point is -- this report has columns that use different criteria. I understand that what's probably causing the slowness is because of the many JOINS on the group data inside each derived table, However, since each derived table is acting like a separate query to bring together all the pieces, I'm not sure how to convert my approach into an indexed temp table approach.
0
 
LVL 4

Assisted Solution

by:Randy Knight, MCM
Randy Knight, MCM earned 1716 total points
ID: 40284159
I was thinking you could create a temp table with the data you are grouping, then index by the group by order.  The temp table would still have the detail, but if you create a clustered index on in the same order of the group by columns, you may be able to eliminate the sort.  That's really what the optimizer is doing with the work table, but you'd be doing in a bit smarter way.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 284 total points
ID: 40284491
regarding:
join CustomerAccount CuAc on en.PartyId = (
CASE
 WHEN @reportType = 'Agent' THEN CuAc.AgentId
 WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
END)

Instead of dynamic sql you could branch inside the SP for that part of it that requires the different joins. This would result in repeated code, but you avoid dynamic sql.

IF @reportType = 'Agent'
       select ... join CustomerAccount CuAc on en.PartyId = CuAc.AgentId

ELSE
      select ... join CustomerAccount CuAc on en.PartyId = CuAc.SubmittedById
0
 
LVL 4

Assisted Solution

by:Randy Knight, MCM
Randy Knight, MCM earned 1716 total points
ID: 40284496
PortletPaul is right, but if you do this, you are probably going to want to create your stored procedure WITH RECOMPILE so that a new plan gets generated each time it is called.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40284558
PortletPaul: Thanks, I will look into this idea:

Randy Knight:  going back to your comment
I was thinking you could create a temp table with the data you are grouping, then index by the group by order.
I would like to confirm if I understood you correctly. I changed my stored procedure by replacing the derived table called RT with a temp table as follows, let me know if this is what you meant:
IF OBJECT_ID('tempdb..#tmpRef') IS NOT NULL         
DROP TABLE #tmpRef 

-- Reference table containing all Entity PartyIDs and months the report is running for
CREATE TABLE #tmpRef
(
RefId INT IDENTITY(1,1) primary key,
PartyId INT NOT NULL,
EntityName VARCHAR(50),
Code VARCHAR(50),
PeriodDate DATETIME,
PeriodEndDate DATETIME
)

;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
		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))
)

INSERT INTO #tmpRef
( PartyId, EntityName, Code, PeriodDate, PeriodEndDate )
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
ORDER BY c.PartyId, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally, 0)

SELECT RT.*, ...
FROM #tmpRef RT
LEFT JOIN 
(
...

Open in new window

0
 
LVL 4

Assisted Solution

by:Randy Knight, MCM
Randy Knight, MCM earned 1716 total points
ID: 40285938
Yes but I would make PartyID the clustered index since you are grouping on that several times.  Right now your clustered index is on RefID which is your surrogate key for the temp table that you aren't even really using.  good on you for always having a primary key but in this case I don't think you really need it.  It's not even enforcing uniqueness.  

If you cluster on PartyID and then group by PartyID, the optimizer should be smart enough to know the data is already in order and not to the sort as part of the group by.  The same thing with the last group by that uses all the columns in your new temp table.

Try it with your new temp table defined like this.
IF OBJECT_ID('tempdb..#tmpRef') IS NOT NULL         
DROP TABLE #tmpRef 

-- Reference table containing all Entity PartyIDs and months the report is running for
CREATE TABLE #tmpRef
(
--RefId INT IDENTITY(1,1) primary key nonclustered
PartyId INT NOT NULL,
EntityName VARCHAR(50),
Code VARCHAR(50),
PeriodDate DATETIME,
PeriodEndDate DATETIME
)

CREATE CLUSTERED INDEX cx ON #tmpRef(PartyID, EntityName, Code, PeriodDate, PeriodEndDate)

Open in new window

0
 
LVL 8

Author Comment

by:pzozulka
ID: 40286038
:)

After changing my RT derived table into a temp table with the clustered key above, it ran from a 12 second query on my machine to 2 minutes. The Execution Plan is complaining about "Index Spool (Lazy Spool)" with cost of 97%.

Seek Predicate:
Seek Keys[1]: Prefix: #tmpRef.PartyId, #tmpRef.PeriodDate, ConstExpr1168, ConstExpr1169, ConstExpr1170, ConstExpr1171 = Scalar Operator(#tmpRef.[PartyId] as [RT].[PartyId]), Scalar Operator(#tmpRef.[PeriodDate] as [RT].[PeriodDate]), Scalar Operator([@reportType]='Agent'), Scalar Operator([@reportType]='Submitted By'), Scalar Operator([@pAgentList] IS NULL), Scalar Operator([@pPortfolioRegionList] IS NULL)

Open in new window


I will try to convert all my derived tables (LEFT JOIN subqueries) into temp tables with indexes, and try this again, but for now, no luck.
 Execution Plan with using temp table
0
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 40286092
Okay it was worth a try.  I think you're on the right track though.  Breaking this query down into multiple steps should get you where you need to go.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40286193
Holy crap. Thanks to all your help, I converted all my LEFT JOIN (derived tables) into temp tables with clustered indexes on all columns, and now the query on a production database with millions of records is running 66% better. Use to run in 6 minutes 40 seconds, down to 1 minute and 30 seconds. Major improvement.
0
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 40286202
In addition to duration, make sure you are looking at IO.  The fewer logical reads the better.  SET STATISTICS IO ON will give you the logical reads per table when you execute the query so you can find where most of the work is.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 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