Rewriting Query to avoid Correlated subquery

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)

Rewriting Query to avoid Correlated subquery

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-17 : 02:28:24

Hi Team,

I have the following tables for school evaluation system. For upgrading the class level of the school, the Center of the school must have been renovated recently. I have written the following query which meets the requirement. However, it uses a co-related subquery. I would like to get rid of the co-related subquery.

Other tw0 constraints are - 1)WHILE loop or cursor should not be used. 2) User defined Functions should not be used.

Could you please help to re-write the query?

DECLARE @ApplicationToUpgrade TABLE (ApplicationID INT,CenterID INT)
DECLARE @Center TABLE (CenterID INT, CenterName VARCHAR(100))
DECLARE @Renovation TABLE(RenovationID INT, CenterID INT, RenovationDt SMALLDATETIME)

INSERT INTO @ApplicationToUpgrade (ApplicationID ,CenterID ) VALUES (1,107)
INSERT INTO @Center (CenterID , CenterName ) VALUES (107,'DF Scool of higher studies')
INSERT INTO @Renovation (RenovationID,CenterID,RenovationDt)VALUES (501,107,'1/1/2007')
INSERT INTO @Renovation (RenovationID,CenterID,RenovationDt)VALUES (502,107,'5/5/2008')
INSERT INTO @Renovation (RenovationID,CenterID,RenovationDt)VALUES (503,107,'8/8/2009')

SELECT U.ApplicationID,C.CenterName, R.RenovationID,R.RenovationDt
FROM @ApplicationToUpgrade U
INNER JOIN @Center C
ON U.CenterID = C.CenterID
INNER JOIN @Renovation R
ON R.CenterID = C.CenterID
WHERE R.RenovationID = (SELECT TOP 1 RenovationID
FROM @Renovation CR
WHERE CR.CenterID = C.CenterID --Correlation
ORDER BY RenovationDt DESC
)

Thanks
Lijo Cheeran Joseph

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 02:53:42

Is this what you're looking for?

;WITH cte AS
	(
	SELECT 
		U.ApplicationID,
		C.CenterName, 
		R.RenovationID,
		R.RenovationDt,
		RowNumber = ROW_NUMBER() OVER (PARTITION BY U.ApplicationID ORDER BY RenovationDt DESC)
	FROM @ApplicationToUpgrade U
		INNER JOIN @Center C 
			ON U.CenterID = C.CenterID
		INNER JOIN @Renovation R
			ON R.CenterID = C.CenterID
	)
SELECT ApplicationID, CenterName, RenovationID, RenovationDt 
FROM cte
WHERE RowNumber = 1

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 02:57:02

You might have to modify the partition and order by...the provided sample data was a bit limited.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-17 : 05:28:53

Why do you want to remove the correlated subquery?

--
Gail Shaw
SQL Server MVP

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-17 : 16:04:36

Just because, many people has the perception that - correlated subquery cause performance hits

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-17 : 16:25:36

Does it? Have you tested and verified that the correlated subquery has an unacceptable impact on the query's performance?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources