sproc performance on first load

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sproc performance on first load

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-22 : 09:36:07

I run this stored procedure from a webpage.
When I first load the page, it takes about 10 seconds. Every subsequent request takes less than a seocnd if not faster. If I wait a few hours or 1 day and rerun it it takes about 10 secionds again.
All my other stored procedure son this page are similar and all pretty efficient. I have indexes on the tables as recommended by the Index tuning wizard. Any ideas on how I can determine what is wrong with this one stored proc?
I looked through the execution plan but no big resource uses I don't think...
The tuning wizard didn't recommend anything either...maybe some index hints?


CREATE PROCEDURE kpi_DisplayLegalEntities (

@Month int

)

AS

SELECT 		S.[Company Description] AS Description, 
		RIGHT('0000' + [Company Code], 4) AS Code, 
		ROUND(ISNULL(ZA.Months0To3,0),2) AS Months0To3, 
		ROUND(ISNULL(ZA.Months4To5,0),2) AS Months4To5, 
		ROUND(ISNULL(ZA.Months6To12,0),2) AS Months6To12, 
		ROUND(ISNULL(ZA.[Months12+],0),2) AS [Months12+], 
		ROUND(ISNULL(ZA.TotalDebt,0),2) AS TotalDebt, 
		ROUND(ISNULL(ZAMinus1.Months0To3,0),2) AS Months0To3_Minus1, 
		ROUND(ISNULL(ZAMinus1.Months4To5,0),2) AS Months4To5_Minus1, 
		ROUND(ISNULL(ZAMinus1.Months6To12,0),2) AS Months6To12_Minus1, 
		ROUND(ISNULL(ZAMinus1.[Months12+],0),2) AS [Months12+_Minus1], 
		ROUND(ISNULL(ZAMinus1.TotalDebt,0),2) AS TotalDebtM1, 
		ROUND(ISNULL(ZAMinus2.TotalDebt,0),2) AS TotalDebtM2, 
		ROUND(ISNULL(ZAMinus3.TotalDebt,0),2) AS TotalDebtM3, 
		ROUND(ISNULL(BD.[Bad Debt],0),2) AS BadDebt, 
		ROUND(ISNULL(BDMinus1.[Bad Debt],0),2) AS BadDebtMinus1, 
		ROUND(ISNULL(ZA.TotalDebt,0) - ISNULL(BD.[Bad Debt],0),2) AS NetTotal, 
		ROUND(ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(BDMinus1.[Bad Debt],0),2) AS NetTotalMinus1, 
		ROUND(ISNULL(ZI.InvoicesRaised,0),2) AS InvoicesRaised, 
		ROUND(ISNULL(ZIMinus1.InvoicesRaised,0),2) AS InvoicesRaisedMinus1, 
		ROUND(ISNULL(ZIMinus2.InvoicesRaised,0),2) AS InvoicesRaisedMinus2, 
		ROUND(ISNULL(ZIMinus3.InvoicesRaised,0),2) AS InvoicesRaisedMinus3, 
		ROUND(ISNULL(ZIMinus4.InvoicesRaised,0),2) AS InvoicesRaisedMinus4, 
		ROUND(ISNULL(ZIMinus5.InvoicesRaised,0),2) AS InvoicesRaisedMinus5, 
		ROUND(ISNULL(ZIMinus6.InvoicesRaised,0),2) AS InvoicesRaisedMinus6, 
		ROUND(ISNULL(ZIMinus7.InvoicesRaised,0),2) AS InvoicesRaisedMinus7, 
		ROUND(((ISNULL(ZA.Months4To5,0) + ISNULL(ZA.Months6To12,0) + ISNULL(ZA.[Months12+],0)) * 0.01),2) AS InterestCharge, 
		ROUND(((ISNULL(ZAMinus1.Months4To5,0) + ISNULL(ZAMinus1.Months6To12,0) + ISNULL(ZAMinus1.[Months12+],0)) * 0.01),2) AS InterestChargeMinus1, 
		ROUND(ISNULL(ZA.DebtAged5Months,0),2) AS DebtAged5Months, 
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		--Current debtor days 5 sets of 30 day calculations
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		CASE	
		WHEN ISNULL(ZI.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZA.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0)) > 0 
				THEN 30
           			WHEN ((ISNULL(ZA.TotalDebt,0) * 30) / ISNULL(ZI.InvoicesRaised,0)) > 0
            			THEN ((ISNULL(ZA.TotalDebt,0) * 30) / ISNULL(ZI.InvoicesRaised,0))
            			ELSE 0
       			 END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus1.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZA.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus1.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus1.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus2.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZA.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus2.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus2.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus3.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZA.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus4.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZA.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZA.TotalDebt,0) - ISNULL(ZI.InvoicesRaised,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0))
				ELSE 0
			END
		END AS DD,
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		--debtor days minus 1
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		CASE	
		WHEN ISNULL(ZIMinus1.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus1.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0)) > 0 
				THEN 30
           			WHEN ((ISNULL(ZAMinus1.TotalDebt,0) * 30) / ISNULL(ZIMinus1.InvoicesRaised,0)) > 0
            			THEN ((ISNULL(ZAMinus1.TotalDebt,0) * 30) / ISNULL(ZIMinus1.InvoicesRaised,0))
            			ELSE 0
       			 END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus2.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus1.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus2.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus2.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus3.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus1.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus4.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus1.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus5.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus1.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus5.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus1.TotalDebt,0) - ISNULL(ZIMinus1.InvoicesRaised,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus5.InvoicesRaised,0))
				ELSE 0
			END
		END AS [DD-1],
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		--debtor days minus 2
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		CASE	
		WHEN ISNULL(ZIMinus2.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus2.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) > 0 
				THEN 30
           			WHEN ((ISNULL(ZAMinus2.TotalDebt,0) * 30) / ISNULL(ZIMinus2.InvoicesRaised,0)) > 0
            			THEN ((ISNULL(ZAMinus2.TotalDebt,0) * 30) / ISNULL(ZIMinus2.InvoicesRaised,0))
            			ELSE 0
       			 END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus3.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus2.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus4.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus2.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus5.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus2.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus5.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus5.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus6.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus2.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0) - ISNULL(ZIMinus6.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus6.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus2.TotalDebt,0) - ISNULL(ZIMinus2.InvoicesRaised,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus6.InvoicesRaised,0))
				ELSE 0
			END
		END AS [DD-2],
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		--debtor days minus 3
		-------------------------------------------------------------------------------------------------------------------------------------------------------
		CASE	
		WHEN ISNULL(ZIMinus3.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus3.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) > 0 
				THEN 30
           			WHEN ((ISNULL(ZAMinus3.TotalDebt,0) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0)) > 0
            			THEN ((ISNULL(ZAMinus3.TotalDebt,0) * 30) / ISNULL(ZIMinus3.InvoicesRaised,0))
            			ELSE 0
       			 END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus4.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus3.TotalDebt,0) <> 0 
			THEN
			CASE 
				WHEN (ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus4.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus5.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus3.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus5.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus5.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus6.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus3.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0) - ISNULL(ZIMinus6.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus6.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus6.InvoicesRaised,0))
				ELSE 0
			END
		END
		+
		CASE	
		WHEN ISNULL(ZIMinus7.InvoicesRaised,0) <=0 
		THEN 0
		WHEN ISNULL(ZAMinus3.TotalDebt,0) <> 0 
			THEN 	
			CASE 
				WHEN (ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0) - ISNULL(ZIMinus6.InvoicesRaised,0) - ISNULL(ZIMinus7.InvoicesRaised,0)) > 0 
				THEN 30
				WHEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0) - ISNULL(ZIMinus6.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus7.InvoicesRaised,0)) > 0
				THEN (((ISNULL(ZAMinus3.TotalDebt,0) - ISNULL(ZIMinus3.InvoicesRaised,0) - ISNULL(ZIMinus4.InvoicesRaised,0) - ISNULL(ZIMinus5.InvoicesRaised,0) - ISNULL(ZIMinus6.InvoicesRaised,0)) * 30) / ISNULL(ZIMinus7.InvoicesRaised,0))
				ELSE 0
			END
		END AS [DD-3]

FROM ((((((((((((((SELECT		[Company Code], [Company Description]
			FROM		CCApp.dbo.Structure
			GROUP BY	[Company Code], [Company Description]
		) AS S LEFT JOIN (SELECT		CoCd,
					(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000 AS Months0To3,
					(Sum([121To150])+Sum([151To180])) /1000 AS Months4To5, 
					(Sum([181To365])) /1000 AS Months6To12, 
					(Sum([MoreThan366])) /1000 AS [Months12+], 
					((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt,
					(Sum([151To180])) / 1000 AS DebtAged5Months
			FROM		zarageddebt
			WHERE 		MonthData = @Month
			GROUP BY	CoCd
		) AS ZA ON ZA.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					(Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000 AS Months0To3,
					(Sum([121To150])+Sum([151To180])) /1000 AS Months4To5, 
					(Sum([181To365])) /1000 AS Months6To12, 
					(Sum([MoreThan366])) /1000 AS [Months12+], 
					((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt
			FROM		zarageddebt
			WHERE 		MonthData = @Month-1
			GROUP BY	CoCd
		) AS ZAMinus1 ON ZAMinus1.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt
			FROM		zarageddebt
			WHERE 		MonthData = @Month-2
			GROUP BY	CoCd
		) AS ZAMinus2 ON ZAMinus2.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					((Sum([1To90])+Sum([Not Due])+Sum([91To120]))+(Sum([121To150])+Sum([151To180]))+Sum([181To365])+Sum([MoreThan366])) /1000 AS TotalDebt
			FROM		zarageddebt
			WHERE 		MonthData = @Month-3
			GROUP BY	CoCd
		) AS ZAMinus3 ON ZAMinus3.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM(baddebt.[Bad Debt]) / 1000 AS [Bad Debt]
			FROM		baddebt
			WHERE		MonthData = @Month
			GROUP BY	CoCd
		) AS BD ON BD.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM(baddebt.[Bad Debt]) / 1000 AS [Bad Debt]
			FROM		baddebt
			WHERE		MonthData = @Month-1
			GROUP BY	CoCd
		) AS BDMinus1 ON BDMinus1.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month
			GROUP BY	CoCd
		) AS ZI ON ZI.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-1
			GROUP BY	CoCd
		) AS ZIMinus1 ON ZIMinus1.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-2
			GROUP BY	CoCd
		) AS ZIMinus2 ON ZIMinus2.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-3
			GROUP BY	CoCd
		) AS ZIMinus3 ON ZIMinus3.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-4
			GROUP BY	CoCd
		) AS ZIMinus4 ON ZIMinus4.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-5
			GROUP BY	CoCd
		) AS ZIMinus5 ON ZIMinus5.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-6
			GROUP BY	CoCd
		) AS ZIMinus6 ON ZIMinus6.CoCd = S.[Company Code]) LEFT JOIN (SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-7
			GROUP BY	CoCd
		) AS ZIMinus7 ON ZIMinus7.CoCd = S.[Company Code]
WHERE S.[Company Description] IS NOT NULL






GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 10:23:46

Read about PROCEDURE CACHE.
How long it stays, and what it does.

E 12°55'05.25"
N 56°04'39.16"

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-22 : 10:58:35

Yes, I assume it was the cache but it doesn't help with how slow the procedure is initiaily nor explain why all the other procedures run quickly. They use similar datasets but not all identical.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-07-22 : 12:35:14

Your tables could do with being "dbo." qualified.
"initial slowness....cache loading, subsequent speed => benefit from cache"

"They use similar datasets but not all identical"....similar <> same....so you may not get the same results/timings from "similar" procedures.

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-22 : 12:43:08

Why does dbo. qualifying make a difference.
Doesn't SQL server assume it is dbo. ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-07-23 : 03:12:32

SQL assumes tables are owner qualified first....and if it doesn't find them then searches for tables prefixed with dbo - incurring an extra time overhead. best practice is to put the dbo. there 12st. this enables code to avail of cache properly.

Have a look at Kristens FAQ....I think there are references in there to this.

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-23 : 07:02:44

dbo. made little or no difference to the execution time.
Must be something else in the way the data is retrieved, presumable the splitting of subqueries but this is the only way I can do it.
Any other thoughts on optimising this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 09:57:38

What about : Set Nocount on

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-07-23 : 11:19:10

STE NOCOUNT ON isn't going to take it from 16seconds down to 1s though is it! :)
I tried it, made little or no difference.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 11:40:48

Start with writing better code.
You can replace all these 8 almost identical LEFT JOINs

LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month
			GROUP BY	CoCd
		) AS ZI ON ZI.CoCd = S.[Company Code]
LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-1
			GROUP BY	CoCd
		) AS ZIMinus1 ON ZIMinus1.CoCd = S.[Company Code]
LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-2
			GROUP BY	CoCd
		) AS ZIMinus2 ON ZIMinus2.CoCd = S.[Company Code]
LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-3
			GROUP BY	CoCd
		) AS ZIMinus3 ON ZIMinus3.CoCd = S.[Company Code]
LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-4
			GROUP BY	CoCd
		) AS ZIMinus4 ON ZIMinus4.CoCd = S.[Company Code]
LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-5
			GROUP BY	CoCd
		) AS ZIMinus5 ON ZIMinus5.CoCd = S.[Company Code]
LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-6
			GROUP BY	CoCd
		) AS ZIMinus6 ON ZIMinus6.CoCd = S.[Company Code]
LEFT JOIN	(
			SELECT		CoCd,
					SUM([Amtin loccur]) / 1000 AS InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData = @Month-7
			GROUP BY	CoCd
		) AS ZIMinus7 ON ZIMinus7.CoCd = S.[Company Code]

with a single one, like this

LEFT JOIN	(
			SELECT		CoCd,
					SUM(CASE WHEN MonthData = @Month THEN [Amtin loccur] / 1000.0 AS InvoicesRaised,
					SUM(CASE WHEN MonthData = @Month - 1 THEN [Amtin loccur] / 1000.0 AS minus1_InvoicesRaised,
					SUM(CASE WHEN MonthData = @Month - 2 THEN [Amtin loccur] / 1000.0 AS minus2_InvoicesRaised,
					SUM(CASE WHEN MonthData = @Month - 3 THEN [Amtin loccur] / 1000.0 AS minus3_InvoicesRaised,
					SUM(CASE WHEN MonthData = @Month - 4 THEN [Amtin loccur] / 1000.0 AS minus4_InvoicesRaised,
					SUM(CASE WHEN MonthData = @Month - 5 THEN [Amtin loccur] / 1000.0 AS minus5_InvoicesRaised,
					SUM(CASE WHEN MonthData = @Month - 6 THEN [Amtin loccur] / 1000.0 AS minus6_InvoicesRaised,
					SUM(CASE WHEN MonthData = @Month - 7 THEN [Amtin loccur] / 1000.0 AS minus7_InvoicesRaised
			FROM		zarinvreg
			WHERE		MonthData BETWEEN @Month - 7 AND @Month
			GROUP BY	CoCd
		) AS ZI ON ZI.CoCd = S.[Company Code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 11:44:27

With the suggestion above, you also should replace this section

		ROUND(ISNULL(ZI.InvoicesRaised,0),2) AS InvoicesRaised, 
		ROUND(ISNULL(ZIMinus1.InvoicesRaised,0),2) AS InvoicesRaisedMinus1, 
		ROUND(ISNULL(ZIMinus2.InvoicesRaised,0),2) AS InvoicesRaisedMinus2, 
		ROUND(ISNULL(ZIMinus3.InvoicesRaised,0),2) AS InvoicesRaisedMinus3, 
		ROUND(ISNULL(ZIMinus4.InvoicesRaised,0),2) AS InvoicesRaisedMinus4, 
		ROUND(ISNULL(ZIMinus5.InvoicesRaised,0),2) AS InvoicesRaisedMinus5, 
		ROUND(ISNULL(ZIMinus6.InvoicesRaised,0),2) AS InvoicesRaisedMinus6, 
		ROUND(ISNULL(ZIMinus7.InvoicesRaised,0),2) AS InvoicesRaisedMinus7,

with this piece of code

		ROUND(ISNULL(ZI.InvoicesRaised,0),2) AS InvoicesRaised, 
		ROUND(ISNULL(ZI.minus1_InvoicesRaised,0),2) AS InvoicesRaisedMinus1, 
		ROUND(ISNULL(ZI.minus2_InvoicesRaised,0),2) AS InvoicesRaisedMinus2, 
		ROUND(ISNULL(ZI.minus3_InvoicesRaised,0),2) AS InvoicesRaisedMinus3, 
		ROUND(ISNULL(ZI.minus4_InvoicesRaised,0),2) AS InvoicesRaisedMinus4, 
		ROUND(ISNULL(ZI.minus5_InvoicesRaised,0),2) AS InvoicesRaisedMinus5, 
		ROUND(ISNULL(ZI.minus6_InvoicesRaised,0),2) AS InvoicesRaisedMinus6, 
		ROUND(ISNULL(ZI.minus7_InvoicesRaised,0),2) AS InvoicesRaisedMinus7,

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources