On Tue, Mar 20, 2012 at 12:48 PM, Tomasz Tyrakowski <
t.tyrakowski@...> wrote:

> > How to best implement ORDER BY of a query when the field by which to
> > order, is given as stored procedure input parameter?
>
> If the data set returned by the procedure is not very large, return all
> relevant fields from the procedure and use order by in the query
> selecting from the procedure (it won't use indices, so it's only
> suitable for reasonably small number of records). O

Returning the results and sorting them in the query is good advice,
regardless of the size of the result set. In general, even though sort is
an NLogN operation and random access to a data page is a KN operation, the
size of K completely overwhelms the LogN. Accessing data in storage order
then sorting in memory (generally) is faster than bouncing all over the
disk to find records in index order. The exceptions are when data is
stored in index order or when you have a LIMIT n (First n) clause that
causes the query to return a small fraction of the records that would be
sorted.

Good luck,

Ann


[Non-text portions of this message have been removed]