Subqueries and case statements

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)

Subqueries and case statements

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-23 : 03:41:40

hi guys,
There is huge performance difference between this 2 queries
I have 2 queries one with a sub query & another one with a case statement.But the query with sub query runs much faster as compared to the case statements.
First Query(With Sub query)


SELECT
                       Cn.ProgramID, Cn.ContractID, Cn.SaleDate, Cn.DealerProviderID AS DealerID, Cn.ContractTermMonthCnt, Cn.ContractIntervalMileage, 
                      PgC.ProgramFamily, PgmProd.ProductKey, PgmProd.SelectNbr, CM.ProductCommissionKey AS CM_ProductCommissionKey, 
                      CM.ProgramProductKey AS CM_ProgramProductKey, CM.ProviderProductKey AS CM_ProviderProductKey, 
                      CM.ProviderProductKey AS CM_ProviderProductKey4like, 
DealerFIIncentiveAmt = COALESCE
                          ((SELECT     ReferentAmt
                              FROM         DBAmt_HistoricVW AS DBc
                              WHERE     (DBc.ReferentType = 'Program' + cast(PP.ProgramID AS varchar) + '/' + cast(PgmProd.ProductTermKey AS varchar) 
                                                    + PP.ProviderType + 'F&IIncentive') AND (DBc.ReferentID = Cn.DealerProviderID) AND (Cn.SaleDate BETWEEN 
                                                    DBc.ReferentDateBGN AND DBc.ReferentDateEND)), COALESCE
                          ((SELECT     ReferentAmt
                              FROM         DBAmt_HistoricVW AS DBc
                              WHERE     (DBc.ReferentType = 'Program' + cast(PP.ProgramID AS varchar) + PP.ProviderType + 'F&IIncentive') AND 
                                                    (DBc.ReferentID = Cn.DealerProviderID) AND (Cn.SaleDate BETWEEN DBc.ReferentDateBGN AND DBc.ReferentDateEND)), 0))
                                                    FROM         Contracts_coreTBL AS Cn INNER JOIN
                      ContractMoneyVW AS CM ON (CM.ContractID = Cn.ContractID) INNER JOIN
                      Programs_coreTBL AS PgC ON (PgC.ProgramID = Cn.ProgramID) AND ((PgC.ProgramFamily != 1) AND (PgC.ProgramFamily != 2) AND 
                      (PgC.ProgramFamily != 8)) INNER JOIN
                      Programs_providersPastPresentVW AS PP ON (PP.ProviderID = Cn.DealerProviderID) AND (PP.ProgramID = Cn.ProgramID) AND 
                      (CM.ProductCommissionKey = PP.ProductCommissionKey) INNER JOIN
                      Providers_coreTBL AS Pc ON ((PP.ProviderType = 'Dealer') OR
                      (PP.ProviderType = 'Broker')) AND (Pc.isActive = 1) AND ((Pc.ProviderType IN ('Dealer', 'Broker'))) AND (Pc.ProviderID = PP.ProviderID) INNER JOIN
                      Program_ProductsVW AS PgmProd ON (PgmProd.ProgramID = Cn.ProgramID) AND (((PP.ProductCommissionKey LIKE PgmProd.ProductKey) AND 
                      (CM.ProductCommissionKey LIKE PgmProd.ProductKey)) OR
                      (PgmProd.ProductTermKey = PP.ProductCommissionKey))
                      order by Cn.ContractID

Another with a case statement


SELECT
                      DISTINCT Cn.ProgramID, Cn.ContractID, Cn.SaleDate, Cn.DealerProviderID AS DealerID, Cn.ContractTermMonthCnt, Cn.ContractIntervalMileage, 
                      PgC.ProgramFamily, PgmProd.ProductKey, PgmProd.SelectNbr, CM.ProductCommissionKey AS CM_ProductCommissionKey, 
                      CM.ProgramProductKey AS CM_ProgramProductKey, CM.ProviderProductKey AS CM_ProviderProductKey, 
                      CM.ProviderProductKey AS CM_ProviderProductKey4like,
                   CASE WHEN DBc.ReferentType='Program' + CAST(PP.ProgramID AS VARCHAR) + '/' + CAST(PgmProd.ProductTermKey AS VARCHAR) 
                                                    + PP.ProviderType + 'F&IIncentive' AND  Cn.SaleDate BETWEEN 
                                                    DBc.ReferentDateBGN AND DBc.ReferentDateEND  THEN ReferentAmt
                      ELSE 
                       CASE WHEN DBc.ReferentType IS NULL THEN
                             CASE WHEN  DBc.ReferentType = 'Program' + CAST(PP.ProgramID AS VARCHAR) + PP.ProviderType + 'F&IIncentive' AND 
                                                         Cn.SaleDate BETWEEN DBc.ReferentDateBGN AND DBc.ReferentDateEND THEN ReferentAmt
                                          ELSE 0
                               END
                                       
                        END
                END
FROM         Contracts_coreTBL AS Cn INNER JOIN
                      ContractMoneyVW AS CM ON (CM.ContractID = Cn.ContractID) INNER JOIN
                      Programs_coreTBL AS PgC ON (PgC.ProgramID = Cn.ProgramID) AND ((PgC.ProgramFamily != 1) AND (PgC.ProgramFamily != 2) AND 
                      (PgC.ProgramFamily != 8)) INNER JOIN
                      Programs_providersPastPresentVW AS PP ON (PP.ProviderID = Cn.DealerProviderID) AND (PP.ProgramID = Cn.ProgramID) AND 
                      (CM.ProductCommissionKey = PP.ProductCommissionKey) INNER JOIN
                      Providers_coreTBL AS Pc ON ((PP.ProviderType = 'Dealer') OR
                      (PP.ProviderType = 'Broker')) AND (Pc.isActive = 1) AND ((Pc.ProviderType IN ('Dealer', 'Broker'))) AND (Pc.ProviderID = PP.ProviderID) INNER JOIN
                      Program_ProductsVW AS PgmProd ON (PgmProd.ProgramID = Cn.ProgramID) AND (((PP.ProductCommissionKey LIKE PgmProd.ProductKey) AND 
                      (CM.ProductCommissionKey LIKE PgmProd.ProductKey)) OR
                      (PgmProd.ProductTermKey = PP.ProductCommissionKey))
                      INNER JOIN  DBAmt_HistoricVW AS DBc ON DBc.ReferentID=Cn.DealerProviderID
                      ORDER BY Cn.SaleDate

Is there any way I can improve the second query to make it run faster?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 04:15:05

Any chance you can rewrite the query as suggested here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127984

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-23 : 06:00:35

quote:
Originally posted by Peso

Any chance you can rewrite the query as suggested here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127984

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

I tried it is not possible.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 06:54:50

Well, if you say so.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:08:26

I'll guess you are not interested in this then?

SELECT		Cn.ProgramID,
		Cn.ContractID,
		Cn.SaleDate,
		Cn.DealerProviderID AS DealerID,
		Cn.ContractTermMonthCnt,
		Cn.ContractIntervalMileage,
		PgC.ProgramFamily,
		PgmProd.ProductKey,
		PgmProd.SelectNbr,
		CM.ProductCommissionKey AS CM_ProductCommissionKey,
		CM.ProgramProductKey AS CM_ProgramProductKey,
		CM.ProviderProductKey AS CM_ProviderProductKey,
		CM.ProviderProductKey AS CM_ProviderProductKey4like,
		COALESCE(g.ReferentAmt, 0) AS DealerFIIncentiveAmt
FROM		Contracts_coreTBL AS Cn
INNER JOIN	ContractMoneyVW AS CM ON CM.ContractID = Cn.ContractID
INNER JOIN	Programs_coreTBL AS PgC ON PgC.ProgramID = Cn.ProgramID
			AND PgC.ProgramFamily NOT IN (1, 2, 8)
INNER JOIN	Programs_providersPastPresentVW AS PP ON PP.ProviderID = Cn.DealerProviderID
			AND PP.ProgramID = Cn.ProgramID
			AND PP.ProviderType IN ('Dealer', 'Broker')
INNER JOIN	Providers_coreTBL AS Pc ON Pc.ProviderID = PP.ProviderID
			AND Pc.isActive = 1
			AND Pc.ProviderType IN ('Dealer', 'Broker')
INNER JOIN	Program_ProductsVW AS PgmProd ON PgmProd.ProgramID = Cn.ProgramID
			AND (
				PP.ProductCommissionKey LIKE PgmProd.ProductKey
				AND CM.ProductCommissionKey LIKE PgmProd.ProductKey
				OR PgmProd.ProductTermKey = PP.ProductCommissionKey
			)
OUTER APPLY	(
			SELECT	DBc.ReferentAmt
			FROM	DBAmt_HistoricVW AS DBc
			WHERE	Dbc.ReferentID = Cn.DealerProviderID
				AND Cn.SaleDate BETWEEN DBc.ReferentDateBGN AND DBc.ReferentDateEND
				AND DBc.ReferentType IN ('Program' + CAST(PP.ProgramID AS VARCHAR(300)) + '/' + CAST(PgmProd.ProductTermKey AS VARCHAR(300)) + PP.ProviderType + 'F&IIncentive', 'Program' + CAST(PP.ProgramID AS varchar(300)) + PP.ProviderType + 'F&IIncentive')
		) AS g(ReferentAmt)
WHERE		CM.ProductCommissionKey = PP.ProductCommissionKey
ORDER BY	Cn.ContractID

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

ayamas
Aged Yak Warrior

552 Posts

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

No man.why do u say that?
Your query as always works much much better & also it looks better to be maintained.
Thanks once again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources