order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...

quote:

---

> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

---

order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
--
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

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, ChrisR
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
>
|||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[vbcol=seagreen]
>news:21a7001c45afd$08f70a50$a501280a@.phx.gbl...
If I[vbcol=seagreen]
Clustered
>
>.
>
|||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.[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:
>

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

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

order by killing me

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Ive got a Clustered Primary Key on
the column that Im ordering by. Any ideas?
TIA, Chris"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
What does the output from Query Analyzer > Show Plan indicate? How many rows
do you have?
Steve|||Please post DDL including index and with Execution plan and Statistics IO.
--
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hi,
Do the select statement with out Order by clause,Since you have primary key
on that field , automatically data will be arranged in that order. In this
case you do not require a order by clause.
Thanks
Hari
MCDBA
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hari,
For your information: if you want the rows of the resultset in a
particular order, you must specify an ORDER BY clause. Without it,
SQL-Server is at liberty to return the rows in any order it likes. At
one occasion this might accidentally be the order you desired, but at
another occasions a different order might result.
Gert-Jan
Hari Prasad wrote:
> Hi,
> Do the select statement with out Order by clause,Since you have primary key
> on that field , automatically data will be arranged in that order. In this
> case you do not require a order by clause.
> Thanks
> Hari
> MCDBA
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> > sql2k sp2
> >
> > Im aware that it normally slows everone down. But Ive got
> > a view, and when I select top 1 from it without an order
> > by it returns in 1 second. With order by takes over 4
> > minutes. Quite a jump. Ive got a Clustered Primary Key on
> > the column that Im ordering by. Any ideas?
> >
> > TIA, Chris
> >

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using the Microsoft OLEDB Oracle driver I get the following error
Error 7399: OLE DB Provider 'MSDAORA' reported and error
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
The native Oracle driver seems to work ok, and I can also connect usine Orcale SQL+ untility just fine, but of course the native driver requires the older flavor of SQL which means that all of my currently coded SPs would have to be ported. The same setup works fine from another SQL2k server using the MS ODBC Oracle driver so I'd like to try using that, however the ODBC driver don't appear on the drop down list of driver in EM linked server screen! I tried installing MDAC 2.8 but it doesn't give me any options for installing the MS ODBC drivers?...Thanks in advance for any ideas on how to resolve this!..."Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in the
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using the Microsoft OLEDB Oracle driver I get the following error:
Error 7399: OLE DB Provider 'MSDAORA' reported and error.
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ]
The native Oracle driver seems to work ok, and I can also connect usine Orcale SQL+ untility just fine, but of course the native driver requires the older flavor of SQL which means that all of my currently coded SPs would have to be ported. The same setup
works fine from another SQL2k server using the MS ODBC Oracle driver so I'd like to try using that, however the ODBC driver don't appear on the drop down list of driver in EM linked server screen! I tried installing MDAC 2.8 but it doesn't give me any op
tions for installing the MS ODBC drivers?...Thanks in advance for any ideas on how to resolve this!...
"Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in the
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David

Oracle Linked Server Error & ODBC Drivers Missing

I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When using
the Microsoft OLEDB Oracle driver I get the following error:
Error 7399: OLE DB Provider 'MSDAORA' reported and error.
OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize r
eturned 0x80004005: ]
The native Oracle driver seems to work ok, and I can also connect usine Orca
le SQL+ untility just fine, but of course the native driver requires the old
er flavor of SQL which means that all of my currently coded SPs would have t
o be ported. The same setup
works fine from another SQL2k server using the MS ODBC Oracle driver so I'd
like to try using that, however the ODBC driver don't appear on the drop dow
n list of driver in EM linked server screen! I tried installing MDAC 2.8 but
it doesn't give me any op
tions for installing the MS ODBC drivers?...Thanks in advance for any ideas
on how to resolve this!..."Joe Loyd" <joe.loyd@.us.didata.com> wrote in message
news:570D6F0B-C76B-46FC-BF45-22D47A37128E@.microsoft.com...
> I'm trying to add a Oracle DB as a linked server in EM on SQL2K. When
using the Microsoft OLEDB Oracle driver I get the following error:
> Error 7399: OLE DB Provider 'MSDAORA' reported and error.
> OLE DB error trace[OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize[/vbc
ol]
returned 0x80004005: ][vbcol=seagreen]
> The native Oracle driver seems to work ok, and I can also connect usine
Orcale SQL+ untility just >fine, but of course the native driver requires
the older flavor of SQL which means that all of my >currently coded SPs
would have to be ported. The same setup works fine from another SQL2k
>server using the MS ODBC Oracle driver so I'd like to try using that,
however the ODBC driver don't >appear on the drop down list of driver in EM
linked server screen! I tried installing MDAC 2.8 but it >doesn't give me
any options for installing the MS ODBC drivers?...Thanks in advance for any
ideas >on how to resolve this!...
You want to use the MSDAORA OleDb driver, and MDAC installs it.
It looks like it's having trouble loading the Oracle OCI libraries. If the
Oracle Client is properly installed, this is usually either a permissions
problem or a path problem. Make sure that the [oracle home]\bin is in t
he
path. If you have to add it, or you installed the Oracle client since your
last reboot, you must reboot the server before the path change will take
effect for services (like SqlServer).
And make sure that the account running SQL has rights to the Oracle home.
David