Query Performance - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query Performance

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-22 : 06:39:28

Can anyone suggets any performance improvement in the following query or anyway I can write it a little better.


SELECT Cn.ContractID, Cn.ProgramID, 
                      Cn.AgentProviderID AS AgentID, Cn.SaleDate, Cn.AgreementType, Cn.ProgramProductKey, Cn.ProviderProductKey, Cn.ProviderProductKey4like, 
                      AgentCostFMbreakdown = CASE WHEN (Cn.ProgramProductKey = 'None') THEN
                          (SELECT     sum(ItemAmt)
                            FROM          Programs_breakdownTBL AS Pb
                            WHERE      (Pb.EntityType = 'Agent') AND (Pb.ItemType = 'Cost') AND (Pb.ProgramID = Cn.ProgramID)) ELSE
                          (SELECT     sum(ItemAmt)
                            FROM          Programs_breakdownTBL AS Pb
                            WHERE      (Pb.ProductKey = Cn.ProgramProductKey) AND (Pb.EntityType = 'Agent') AND (Pb.ItemType = 'Cost') AND (Pb.ProgramID = Cn.ProgramID)) 
                      END, AgentFeeFMbreakdown = CASE WHEN (Cn.ProgramProductKey = 'None') THEN
                          (SELECT     sum(ItemAmt)
                            FROM          Programs_breakdownTBL AS Pb
                            WHERE      (Pb.EntityType = 'Agent') AND (Pb.ItemType = 'Fee') AND (Pb.ProgramID = Cn.ProgramID)) ELSE
                          (SELECT     sum(ItemAmt)
                            FROM          Programs_breakdownTBL AS Pb
                            WHERE      (Pb.ProductKey = Cn.ProgramProductKey) AND (Pb.EntityType = 'Agent') AND (Pb.ItemType = 'Fee') AND (Pb.ProgramID = Cn.ProgramID)) 
                      END, AgentCommissionAmt = CAD.AgentCommissionAmt, CAD.MGACommissionAmt
FROM         ContractMoneyVW AS Cn, Programs_coreTBL AS PG, Contract_AgentDBAmtsVW AS CAD
WHERE     (Cn.ProgramID = PG.ProgramID) AND (PG.IfAgentCommissionVaries = 1) AND (CAD.ContractID = Cn.ContractID) 
AND      (CAD.ProductCommissionKey LIKE Cn.ProviderProductKey4like)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 07:05:36

Something like this?

SELECT		cn.ContractID,
		cn.ProgramID, 
		cn.AgentProviderID AS AgentID,
		cn.SaleDate,
		cn.AgreementType,
		cn.ProgramProductKey,
		cn.ProviderProductKey,
		cn.ProviderProductKey4like, 
		t.Cost AS  AgentCostFMbreakdown
		t.Fee AS AgentFeeFMbreakdown,
		cad.AgentCommissionAmt,
		cad.MGACommissionAmt
FROM		ContractMoneyVW AS cn
INNER JOIN	Programs_coreTBL AS pg ON pg.ProgramID = cn.ProgramID 
			AND pg.IfAgentCommissionVaries = 1
INNER JOIN	Contract_AgentDBAmtsVW AS cad ON cad.ContractID = cn.ContractID
			AND cad.ProductCommissionKey LIKE cn.ProviderProductKey4like
LEFT JOIN	(
			SELECT		ProgramID,
					COALESCE(ProductKey, 'NONE') AS ProductKey,
					SUM(CASE WHEN ItemType = 'Cost' THEN ItemAmt ELSE 0 END) AS Cost,
					SUM(CASE WHEN ItemType = 'Fee' THEN ItemAmt ELSE 0 END) AS Fee
			FROM		Programs_breakdownTBL
			WHERE		EntityType = 'Agent'
					AND ItemType IN ('Cost', 'Fee')
			GROUP BY	ProgramID,
					COALESCE(ProductKey, 'NONE')
			WITH ROLLUP
		) AS t ON t.ProgramID = cn.ProgramID
			AND t.ProductKey = cn.ProgramProductKey

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-22 : 07:23:09

quote:

Originally posted by Peso

Something like this?

SELECT		cn.ContractID,
		cn.ProgramID, 
		cn.AgentProviderID AS AgentID,
		cn.SaleDate,
		cn.AgreementType,
		cn.ProgramProductKey,
		cn.ProviderProductKey,
		cn.ProviderProductKey4like, 
		t.Cost AS  AgentCostFMbreakdown
		t.Fee AS AgentFeeFMbreakdown,
		cad.AgentCommissionAmt,
		cad.MGACommissionAmt
FROM		ContractMoneyVW AS cn
INNER JOIN	Programs_coreTBL AS pg ON pg.ProgramID = cn.ProgramID 
			AND pg.IfAgentCommissionVaries = 1
INNER JOIN	Contract_AgentDBAmtsVW AS cad ON cad.ContractID = cn.ContractID
			AND cad.ProductCommissionKey LIKE cn.ProviderProductKey4like
LEFT JOIN	(
			SELECT		ProgramID,
					COALESCE(ProductKey, 'NONE') AS ProductKey,
					SUM(CASE WHEN ItemType = 'Cost' THEN ItemAmt ELSE 0 END) AS Cost,
					SUM(CASE WHEN ItemType = 'Fee' THEN ItemAmt ELSE 0 END) AS Fee
			FROM		Programs_breakdownTBL
			WHERE		EntityType = 'Agent'
					AND ItemType IN ('Cost', 'Fee')
			GROUP BY	ProgramID,
					COALESCE(ProductKey, 'NONE')
			WITH ROLLUP
		) AS t ON t.ProgramID = cn.ProgramID
			AND t.ProductKey = cn.ProgramProductKey

E 12°55'05.63"
N 56°04'39.26"

Wow Wow..!
My original query used to take around 2.10 mins but urs tks jst 40 seconds.Man u r great.
Should I take the credit for it if my boss asks it?I will do what u say.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 07:45:46

It's a free forum with public answers.
You do what you seem fit.

Thank you for the feedback, both social and technical.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 07:48:15

You can try replacing the LEFT JOIN with an INNER JOIN if you are sure there will always be matching records in Programs_breakdownTBL
table.
That can speed things up a little more.

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-22 : 08:02:15

quote:
Originally posted by Peso

It's a free forum with public answers.
You do what you seem fit.

Thank you for the feedback, both social and technical.

E 12°55'05.63"
N 56°04'39.26"

Thank you very much for your kind words.You are technically as well as an individual a great person.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources