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...
>> 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
>
>.
>|||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.
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.
> 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
> >
> >|||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:
> >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
> >
> >
> >
>

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
>.
>

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
RemiHi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
Remi
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>
|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

Oracle Listener stops frequently

Hi,
I have Oracle 9i. Oracle listener is aborting by itself after every few minutes giving TNSLSNR.exe failed. While listener is running I can do all the activity on the database.
Please let me know if there is any solution.
Thanks in Advance.
PushpamDear this is a SQL server related and keep your questions pertaining to SQL server, kindly post in Oracle newsgroups.