woodsmailbox wrote:

>
> not sure i understand.

First issue is the one explained by Ann: the engine always prefers two
sequential (i.e. storage order) scans for a sort instead of a mixed
sequential / random approach. This is by design and it cannot be turned
off. The problem you experience is caused by the fact that the sort
records are stored expanded, while they're compressed on data pages.
This is why you see the big I/O difference. This could be improved via a
proper tuning of the SortMemUpperLimit / TempCacheLimit parameters,
provided that you have plenty of RAM on board. This is a design
limitation and not a bug.

The second issue is that the optimizer is not clever enough to apply the
sort to the deepest stream possible and only then join the other
streams. It's not always possible and sometimes it could perform worse
than sorting the entire resulting row set, but sometimes it could be
very useful. Strictly speaking, this isn't a bug either, but an
improvement request could be evaluated by the team.

> w/derived table it works if I place the order-by clause on the inner
> select, even if not correct SQL, it gives the right result and no sort
> table gets created... until the optimizer will get smart enough and
> inline the subselect too... then i'll make a sp and for-select the
> darn thing.. u get the picture :)

Start with a sp w/ for-select loop inside and you'll be happy forever ;-)


Dmitry