The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Ordering views?  Bad?

Howdy, all.

Why is it considered "bad" to want to order an SQL view?  Or, better yet, why do some people think it's considered "bad"? :-)

ICCO
ICCO
Friday, August 22, 2008
 
 
I think it's unnecessary, given the nature of what a view does. Think of a view like shorthand for a subquery (avoiding the discussion of materialized views). The ordering of a subquery will almost certainly be destroyed by the processing of an outer query, and thus the work will be lost. It's best to keep ordering in the outermost part of a query, just before data is to be returned - that way you avoid doing work that's either unnecessary (if the user doesn't care) or redundant (if you sort twice in the query).
BrotherBeal Send private email
Friday, August 22, 2008
 
 
If you have a view 'MyView' which is ordered by 'Column1', and you decide to order by 'Column2', it's equivalent to writing:

SELECT * FROM
(
SELECT *
FROM MyView
ORDER BY Column1
) ORDER BY Column2

The ordering operation is being done twice. This will result in poor performance. It's better to think or sorting as a "presentation" issue, and save it 'til last.
Chris McKenzie Send private email
Friday, August 22, 2008
 
 
Hi, all.

Thanks for the info!  I guess I didn't think about "redundant" sorting, because the views I'm constructing are actually being fed directly into my display logic.

Regards,
ICCO
ICCO
Friday, August 22, 2008
 
 
It usually *is* a presentation-issue, so +1 to the previous poster. Most grids are sortable by clicking on a column-header. Now, you could make different views for each sortable column, but it would just litter the database.

You don't want a "View1_Sortedby_Col1" and a second "View2_Sortedby_Col2".
Eddy Vluggen Send private email
Friday, August 22, 2008
 
 
A few DBMS builders have supported views with an order by clause. Most do not.

A view is a virtual table,  and a table is the same table, regardless of its order.  This makes it "bad" to want, from a certain point of view. 

The argument about sorting twice has merit, but a really good optimizer could bypass the redundant sort.  Still,  optimizer writers might have better fish to fry.

In the usage you contemplate, you are using a view not as a virtual table, but as a virtual data source.  There's nothing "bad" about wanting a data source to be ordered. 

In most situations,  it's possible to specify a query in the application that will order the data before passing it on to the engine that processes it,  and this makes supporting ordered views superfluous.

Bottom line:  most DBMS engineers don't consider ordered views to be worth the effort of implementing.
Walter Mitty Send private email
Saturday, August 23, 2008
 
 
==> The ordering operation is being done twice.

Only if your DBMS has a BrainDead(tm) optimizer.
Sgt.Sausage
Monday, August 25, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz