Posted by RickW on 03/07/06 08:52

Serge - Thanks for the info. Very concise and relevant.

If you're asking me what's so bad about placing the ORDER BY in the
cursors selecting from the view, nothing really. I assume you're
referring to my Access queries in this case. I've moved about half the
Order By clauses and will finish the other half tomorrow.

I used SS to write out the view scripts, chucked the rows of text in a
table in Access, located the ones with ORDER BY clauses, started looking
in code for uses of those views, and pasted or typed the order by in.
Had to remove some table qualifiers, and in a couple of them had to sort
by a field returned in the view instead of a field available but not
returned in the view. (Trying to get the covering index to kick in
instead of sorting on a calculated column.)

At our level of data, I don't even see an appreciable performance hit.
We only have about 1.5 GB total, and we're never looking at a
significant portion of it at any one time.

The recovery itself has proven pretty painless.

And you're absolutely right, it's nothing compared to rewriting
everything as stored procedures etc etc etc.

On saving CPU cycles: I would absolutely agree that a view based on
other views should be parsed and processed so that the ordering in all
underlying views is ignored. Unquestionably beneficial. I just didn't
agree that the ability to retrieve an ordered set from a real-world tool
was an unforgiveable sin against the theoretical definition of the view.
Some do not share this libertine view.

Thanks again.


Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]