Order by Clause : No difference in SQL Query Plan.

We are in the process of fine tuning a few stored procedures.
In one situation - the order by clause increased the duration of an SP.
This is expected behaviour.
We removed the order by clause and got a marginal gain.
Examined the query plan of both the statements (with orderBy and without
OrderBy).
There was no difference.
Why?
The order by clause causes the query optimizer to do quite a few more
additional steps (internally) before returning the final result set.
Should'nt this displayed in the graphical query plan?
Cheers!
SQLCatz
The answer of a midpaid consultant would be: "It depends" ;-)
It depends on your query you are going to run to order, if you order a
indexbased column and it is clustered it is already ordered. Perhaps you can
post your DDL or/and query here.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"SQLCatz" <SQLCatz@.discussions.microsoft.com> schrieb im Newsbeitrag
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>
|||Hi SQLCatz
How do you know that SQL Server is doing quite a few more additional steps
if you don't see it in the query plan? It may be taking advantage of an
ordering that is already available and not having to do anything extra.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:5EEF62B3-568C-4031-B1C2-C0A6DAE1D658@.microsoft.com...
> We are in the process of fine tuning a few stored procedures.
> In one situation - the order by clause increased the duration of an SP.
> This is expected behaviour.
> We removed the order by clause and got a marginal gain.
> Examined the query plan of both the statements (with orderBy and without
> OrderBy).
> There was no difference.
> Why?
> The order by clause causes the query optimizer to do quite a few more
> additional steps (internally) before returning the final result set.
> Should'nt this displayed in the graphical query plan?
> Cheers!
> SQLCatz
>
sql