order by slowing me down

sql2k sp3
This query takes 14 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? Some fun facts:
I didnt design this table. I know it has too many NULLs.
Something I noticed in the output of showplan_text is that
its sorting by customerkey.
Below is the DDL, query, and output from set
showplan_text on:
CREATE TABLE [dbo].[TransDtl0] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_TransDtl0_TransDate] ON
[dbo].[TransDtl0]([TransDate]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_PostDate] ON [dbo].[TransDtl0]
([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_CustomerKey] ON [dbo].
[TransDtl0]([CustomerKey]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_RefNo] ON [dbo].[TransDtl0]
([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl0_SerialNbr] ON [dbo].
[TransDtl0]([SerialNbr]) WITH FILLFACTOR = 100 ON
[PRIMARY]
GO
/****** The index created by the following statement is
for internal use only. ******/
/****** It is not a real index but exists as statistics
only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
[TransDtl0] ([MerchSIC]) ')
GO
CREATE INDEX [IX_TransDtl0] ON [dbo].[TransDtl0]
([MerchName]) ON [PRIMARY]
GO
select
CustomerKey
from transdtl0 t
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode <> 7008 and trancode <> 7023
Group By t.CustomerKey
order by min(t.transdate)
StmtText
---
---
---
---
--
select
CustomerKey
from transdtl0 t
where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
SERVICER39')
and TranCode <> 7008 and trancode <> 7023
Group By t.CustomerKey
order by
min(t.transdate)
--t.customerkey,
--truncate table tmpdtl
(1 row(s) affected)
StmtText
---
---
---
---
----
|--Parallelism(Gather Streams, ORDER BY:([Expr1001] ASC))
|--Sort(ORDER BY:([Expr1001] ASC))
|--Stream Aggregate(GROUP BY:([t].
[CustomerKey]) DEFINE:([Expr1001]=MIN([t].[TransDate])))
|--Sort(ORDER BY:([t].[CustomerKey] ASC))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([t].[CustomerKey]))
|--Clustered Index Scan(OBJECT:
([MatViewTest].[dbo].[TransDtl0].[IX_TransDtl0_TransDate]
AS [t]), WHERE:((([t].[MerchName]='DTV*DIRECTV SERVICER39'
OR [t].[MerchName]='DTV*DIRECTV SERVICE') AND Convert([t].
[TranCode])<>7008) AND Convert([t].[TranCode])<>7023))
(6 row(s) affected)
TIA, ChrisRI just found some stuff out. As a result, this question
doesnt matter. Im going to repost a similar question
shortly. Hopefully nobody has spent time on this. Sorry.
>--Original Message--
>sql2k sp3
>This query takes 14 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? Some fun facts:
>I didnt design this table. I know it has too many NULLs.
>Something I noticed in the output of showplan_text is
that
>its sorting by customerkey.
>Below is the DDL, query, and output from set
>showplan_text on:
>CREATE TABLE [dbo].[TransDtl0] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO
> CREATE CLUSTERED INDEX [IX_TransDtl0_TransDate] ON
>[dbo].[TransDtl0]([TransDate]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_PostDate] ON [dbo].
[TransDtl0]
>([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_CustomerKey] ON [dbo].
>[TransDtl0]([CustomerKey]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_RefNo] ON [dbo].[TransDtl0]
>([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl0_SerialNbr] ON [dbo].
>[TransDtl0]([SerialNbr]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
>/****** The index created by the following statement is
>for internal use only. ******/
>/****** It is not a real index but exists as statistics
>only. ******/
>if (@.@.microsoftversion > 0x07000000 )
>EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
>[TransDtl0] ([MerchSIC]) ')
>GO
> CREATE INDEX [IX_TransDtl0] ON [dbo].[TransDtl0]
>([MerchName]) ON [PRIMARY]
>GO
>
>select
>CustomerKey
>from transdtl0 t
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode <> 7008 and trancode <> 7023
>Group By t.CustomerKey
>order by min(t.transdate)
>
>StmtText
>
>
>
>----
-
>----
-
>----
-
>----
-
>--
>select
>CustomerKey
>from transdtl0 t
>where MerchName in ('DTV*DIRECTV SERVICE','DTV*DIRECTV
>SERVICER39')
>and TranCode <> 7008 and trancode <> 7023
>Group By t.CustomerKey
>order by
>min(t.transdate)
>--t.customerkey,
>--truncate table tmpdtl
>(1 row(s) affected)
>StmtText
>
>
>
>----
-
>----
-
>----
-
>----
-
>----
> |--Parallelism(Gather Streams, ORDER BY:([Expr1001]
ASC))
> |--Sort(ORDER BY:([Expr1001] ASC))
> |--Stream Aggregate(GROUP BY:([t].
>[CustomerKey]) DEFINE:([Expr1001]=MIN([t].[TransDate])))
> |--Sort(ORDER BY:([t].[CustomerKey] ASC))
> |--Parallelism(Repartition Streams,
>PARTITION COLUMNS:([t].[CustomerKey]))
> |--Clustered Index Scan(OBJECT:
>([MatViewTest].[dbo].[TransDtl0].[IX_TransDtl0_TransDate]
>AS [t]), WHERE:((([t].[MerchName]='DTV*DIRECTV
SERVICER39'
>OR [t].[MerchName]='DTV*DIRECTV SERVICE') AND Convert([t].
>[TranCode])<>7008) AND Convert([t].[TranCode])<>7023))
>(6 row(s) affected)
>
>TIA, ChrisR
>.
>