Note To Self

Don't put ORDER BY Statements in Views.

Delay sorting until you're actually about to use the data.

If you end up sorting differently than the default sort specified by the ORDER BY in the view, it comes as an expensive performance hit. Witness a stored procedure that was breaking a unit test due to a SqlClient.SqlTimeout Exception (took 2.5 minutes to run). Removing the ORDER BY statements from the underlying views brought execution time down to 6 seconds.

If I ever catch me putting ORDER BY statements in views again, I grant myself permission to kill me :-).


It's hard being a self-taught programmer sometimes. Will they cover this issue when I get to my relational database courses in college?

Comments

# re: Note To Self

Friday, May 6, 2005 11:59 AM by Miller

No, they don't teach common sense in college courses because it is common sense.

# re: Note To Self

Friday, May 6, 2005 12:40 PM by Frans Bouma

"It's hard being a self-taught programmer sometimes. Will they cover this issue when I get to my relational database courses in college?"
No way! :)

I agree it's common sense to leave them out of the view. Once you understand how a view is used in a query, you'll understand putting them in the view is weird: select * from myview, where myview is select * from foo, does:
select * from (select * from foo) as Myview

so adding the order by in a view is not logical, normally you wouldn't put it there also if you would use a normal query.

# re: Note To Self

Friday, May 6, 2005 1:23 PM by Chris McKenzie

Follow up:
What you've said makes sense AFTER the fact, but hindsight is 20/20.

# re: Note To Self

Friday, May 6, 2005 1:49 PM by Michael Kaplan

There are actually ways to make it work, FWIW -- it is one of the major topics I will be dealing with at my TechEd talks in Orlando and Amsterdam (DAT290 and DBA319), for both Shiloh (SQL Server 2000) and Yukon (SQL Server 2005).

# re: Note To Self

Friday, May 6, 2005 1:52 PM by Chris McKenzie

Care to offer any spoilers?

# re: Note To Self

Friday, May 6, 2005 1:57 PM by Michael Kaplan

Heh heh heh -- after my blog is back up maybe I'll post some stuff on this issue -- it is an interesting one.

Right now the whole MSDN blog site seems to be down though. Hopefully they will fix that soon!

# re: Note To Self

Friday, May 6, 2005 2:01 PM by Michael Kaplan

Scratch the maybe -- I will post something this afternoon, after they get it back up. :-)

# re: Note To Self

Friday, May 6, 2005 2:33 PM by Wallym

Yeah, I've always felt that ORDER BY is bad anyway. The problem I've got is that the current project I am on won't let me do a sort any other way.

Wally

# re: Note To Self

Friday, May 6, 2005 2:35 PM by Chris McKenzie

Excellent! Thanks.

# re: Note To Self

Friday, May 6, 2005 2:36 PM by Chris McKenzie

In my project, I use the ORDER BY when returning the values from the procedure--I'm just taking care not to use ORDER BY before that point, i.e., in the views.

# re: Note To Self

Friday, May 6, 2005 5:11 PM by Michael Kaplan

There you go, Chris. Enjoy. :-)

# in reporting services pls help me

Wednesday, May 18, 2005 7:05 AM by jagan

hello sir,
i was strucked at form authentication in reproting services so pls help me
and my problem was when i am configuring the files, reportmanger in the browser is saying as error could not found so pls help and suggest me