Force ORDER BY after inner select

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)

Force ORDER BY after inner select

Author

Topic

WaterWolf
Starting Member

24 Posts

Posted - 2010-07-29 : 07:20:41

I have a view, vMyView which is doing a complex pivot select.

If I run

SELECT * FROM vMyView WHERE id = 7

the result returns in under a second.

If however I run

SELECT * FROM vMyView WHERE id = 7 ORDER BY PersonName

the result returns in about 5 minutes!

Then if I run

SELECT * INTO #temp FROM FROM vMyView WHERE id = 7

SELECT * FROM
#temp
ORDER BY PersonName

DROP TABLE #temp

the result will again return in under a second.

It would appear that the order by is being applied to the whole view rather than just the subset I am selecting. I want to be able to do this in one select statement without using a temporary table. How can I force sql server to only apply the order by clause after it has selected the subset of information?

I've tried

SELECT * FROM 
(SELECT * FROM vMyView WHERE id = 7) AS InnerSelect 
ORDER BY PersonName

but that didn't work - it still took 5 minutes.

WaterWolf
Starting Member

24 Posts

Posted - 2010-07-29 : 08:49:34

Okay, I seem to have sorted the issue by applying an index to the base table that the view was working off. I'm not sure if it is still sorting the whole view or not but it runs a lot quicker now. It would still be nice to get a reply to my original question though...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources