order by slowing me down X 40

sql2k sp3
This query takes 40 seconds to run in Query Analyzer. If I
run it without the Order By it takes 0 seconds. This in
itself isnt to bizarre. But heres the catch, the Clustered
Index is on the transdate coulmn. That being the case,
shouldnt the difference be less?
select top 100 CustomerKey,transdate
from transdtl
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode not in (7008,7023)
Group By CustomerKey,transdate
order by transdate
TIA, ChrisRDid you view the query plan with and without the order by? What did you
see?
http://www.aspfaq.com/
(Reverse address to reply.)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:21a7001c45afd$08f70a50$a501280a@.phx
.gbl...
> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Theres table scans either way. But the order by query has
sorts as well.

>--Original Message--
>Did you view the query plan with and without the order
by? What did you
>see?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
> news:21a7001c45afd$08f70a50$a501280a@.phx
.gbl...
If I[vbcol=seagreen]
Clustered[vbcol=seagreen]
>
>.
>|||try
Group By transdate, CustomerKey
I'm guessing that without the order by it just has to get the first 100 grou
ps. With the order by it has to get all the groups to sort.
Have a look at the number of rows in steps in the query plan.
"ChrisR" wrote:

> sql2k sp3
> This query takes 40 seconds to run in Query Analyzer. If I
> run it without the Order By it takes 0 seconds. This in
> itself isnt to bizarre. But heres the catch, the Clustered
> Index is on the transdate coulmn. That being the case,
> shouldnt the difference be less?
>
> select top 100 CustomerKey,transdate
> from transdtl
> where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
> SERVICER39')
> and TranCode not in (7008,7023)
> Group By CustomerKey,transdate
> order by transdate
>
> TIA, ChrisR
>|||Even just using Order By TramsDate, it still slows it down just the same.
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:1BB2D0D3-8C0D-4024-98C7-317014533102@.microsoft.com...
> try
> Group By transdate, CustomerKey
>
> I'm guessing that without the order by it just has to get the first 100
groups. With the order by it has to get all the groups to sort.[vbcol=seagreen]
> Have a look at the number of rows in steps in the query plan.
>
> "ChrisR" wrote:
>|||Chris,
The query is asking for different results depending on whether you
include the ORDER BY. Without the ORDER BY, you are asking for any 100
rows from among the (Customer, transdate) pairs appearing in rows that
satisfy your MerchName and TranCode criteria. With the ORDER BY, you
are asking for a specific 100 (Customer, transdate) pairs satisfying
your MerchName and TranCode criteria - the 100 pairs that have the
earliest transdate values.
There are various scenarios where the query will be slower with an
ORDER BY clause, although I'm not sure why you are seeing a table scan
(i.e., a clustered index scan) in both situations. In addition, "Table
Scan" should not appear as an operator when the table has a clustered
index, so do you think you could post the CREATE TABLE and CREATE INDEX
statements for these tables and the estimated execution plans obtained
with the SET SHOWPLAN_TEXT ON?
Steve Kass
Drew University
ChrisR wrote:

>sql2k sp3
>This query takes 40 seconds to run in Query Analyzer. If I
>run it without the Order By it takes 0 seconds. This in
>itself isnt to bizarre. But heres the catch, the Clustered
>Index is on the transdate coulmn. That being the case,
>shouldnt the difference be less?
>
>select top 100 CustomerKey,transdate
>from transdtl
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode not in (7008,7023)
>Group By CustomerKey,transdate
>order by transdate
>
>TIA, ChrisR
>
>|||Yes but Ill have to repost Monday. Have a great weekend.
"Steve Kass" <skass@.drew.edu> wrote in message
news:eWYRFh5WEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Chris,
> The query is asking for different results depending on whether you
> include the ORDER BY. Without the ORDER BY, you are asking for any 100
> rows from among the (Customer, transdate) pairs appearing in rows that
> satisfy your MerchName and TranCode criteria. With the ORDER BY, you
> are asking for a specific 100 (Customer, transdate) pairs satisfying
> your MerchName and TranCode criteria - the 100 pairs that have the
> earliest transdate values.
> There are various scenarios where the query will be slower with an
> ORDER BY clause, although I'm not sure why you are seeing a table scan
> (i.e., a clustered index scan) in both situations. In addition, "Table
> Scan" should not appear as an operator when the table has a clustered
> index, so do you think you could post the CREATE TABLE and CREATE INDEX
> statements for these tables and the estimated execution plans obtained
> with the SET SHOWPLAN_TEXT ON?
> Steve Kass
> Drew University
> ChrisR wrote:
>
>sql