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 bearing in mind seconds

hi,
How could I order by datetime type including the seconds?
Now I am using order by <field> and it' getting the values of this way:
2005-08-04 03:03:42.000
2005-08-04 04:00:33.000
2005-08-04 07:31:20.000
Instead of:
2005-08-04 07:31:20.000
2005-08-04 04:00:33.000
2005-08-04 03:03:42.000
Best wishes,Hi Enric,
I'm not sure what you think is wrong with the sorting that you get. It seems
perfectly valid.
Sorting by datetime includes ALL datetime elements (even the milisecconds).
If you want to sort ONLY by seconds, ingnoring the other parts, use:
order by datepart(second, dt)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:F722D128-297D-4DD9-A8DC-05F34F714B87@.microsoft.com...
> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||I don't understand the question. The first example IS ordered by time
including the seconds. Ordering by a DATETIME column always takes
account of the whole date and time value, unless you perform some other
manipulation of the value.
David Portas
SQL Server MVP
--|||In order to accomplished you goad you presented you should just change the
sort order for that ?!
order by <col> DESC
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> hi,
> How could I order by datetime type including the seconds?
> Now I am using order by <field> and it' getting the values of this way:
> 2005-08-04 03:03:42.000
> 2005-08-04 04:00:33.000
> 2005-08-04 07:31:20.000
> Instead of:
> 2005-08-04 07:31:20.000
> 2005-08-04 04:00:33.000
> 2005-08-04 03:03:42.000
> Best wishes,
>
>
>
>|||Thanks a lot to all,
"Jens Sü?meyer" wrote:
> In order to accomplished you goad you presented you should just change the
> sort order for that ?!
> order by <col> DESC
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Enric" wrote:
>

Oracle query is SLOW, why ?

Hi,
Have the following sql analyzer query which takes 55 seconds to return
data.
SELECT i.TRANSACTION_DATE,
i.TYPE,
i.PART_ID,
i.WAREHOUSE_ID,
i.TRANSACTION_ID,
i.WORKORDER_BASE_ID,
i.QTY,
i.CLASS
FROM VMFG..SYSADM.INVENTORY_TRANS i
WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
order by i.TRANSACTION_ID
Remove the and's and the response goes down to 1 second.
SELECT i.TRANSACTION_DATE,
i.TYPE,
i.PART_ID,
i.WAREHOUSE_ID,
i.TRANSACTION_ID,
i.WORKORDER_BASE_ID,
i.QTY,
i.CLASS
FROM VMFG..SYSADM.INVENTORY_TRANS i
WHERE i.TRANSACTION_ID = 526123
order by i.TRANSACTION_ID
The question is why ? If you look at the data in the single row returned...
2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
17408 1.0000 R
the record clearly contains the correct data. Any insight on this GREATLY
appreciated because we have a reporting services query based on a date range
which also has the same poor response time. If you put the same query into an
Oracle sql Plus query the data returns instantly.
Thanks, Steve.How do you connect...?
In the dts-newsgroup heard some discussions to use text file when
transporting data from oracle to sql - so perhaps you need to transport
these using some kind of bulk export and query on top of an sql table
instead.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> Hi,
> Have the following sql analyzer query which takes 55 seconds to return
> data.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> order by i.TRANSACTION_ID
> Remove the and's and the response goes down to 1 second.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123
> order by i.TRANSACTION_ID
> The question is why ? If you look at the data in the single row
returned...
> 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> 17408 1.0000 R
> the record clearly contains the correct data. Any insight on this GREATLY
> appreciated because we have a reporting services query based on a date
range
> which also has the same poor response time. If you put the same query into
an
> Oracle sql Plus query the data returns instantly.
> Thanks, Steve.
>|||You mention query analyzer and Oracle sql plus. OK, that makes me think you
are using linked servers. Try the query plan with the query analyzer and I
bet you find out that it is pulling all the data locally and then applying
the where clause. You have several options. One is to use Openquery instead
of the 4 part syntax. The other is to not use linked database. I only use
linked databases as a last resort. I suggest using a shared datasource in RS
to Oracle instead. For straight reporting I never use linked databases. It
complicates matters and buys you very little. What I do is have a shared
datasource that has a readonly user credential stored by RS. This takes
advantage of connection pooling and will be either much faster or much
easier or both than using linked databases.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> Hi,
> Have the following sql analyzer query which takes 55 seconds to return
> data.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> order by i.TRANSACTION_ID
> Remove the and's and the response goes down to 1 second.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123
> order by i.TRANSACTION_ID
> The question is why ? If you look at the data in the single row
returned...
> 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> 17408 1.0000 R
> the record clearly contains the correct data. Any insight on this GREATLY
> appreciated because we have a reporting services query based on a date
range
> which also has the same poor response time. If you put the same query into
an
> Oracle sql Plus query the data returns instantly.
> Thanks, Steve.
>|||Through a linked server
"Michael Vardinghus" wrote:
> How do you connect...?
> In the dts-newsgroup heard some discussions to use text file when
> transporting data from oracle to sql - so perhaps you need to transport
> these using some kind of bulk export and query on top of an sql table
> instead.
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Hi Bruce,
Right you are !! Will research creating a "shared data source".
Do not see a ton of info in help on this. Are you able to reference this
shared data source from your stored procedures just as you do the linked
server ?
Many thanks, Steve.
"Bruce L-C [MVP]" wrote:
> You mention query analyzer and Oracle sql plus. OK, that makes me think you
> are using linked servers. Try the query plan with the query analyzer and I
> bet you find out that it is pulling all the data locally and then applying
> the where clause. You have several options. One is to use Openquery instead
> of the 4 part syntax. The other is to not use linked database. I only use
> linked databases as a last resort. I suggest using a shared datasource in RS
> to Oracle instead. For straight reporting I never use linked databases. It
> complicates matters and buys you very little. What I do is have a shared
> datasource that has a readonly user credential stored by RS. This takes
> advantage of connection pooling and will be either much faster or much
> easier or both than using linked databases.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Hi Bruce,
Many thanks for your reply ! Absolutely correct on all accounts
(linked server, plan). Am now researching the "shared datasource" option. Do
you still reference it from your stored procedures ? That probably does not
change.
Any tech article tips or links on this greatly appreciated. Steve.
"Bruce L-C [MVP]" wrote:
> You mention query analyzer and Oracle sql plus. OK, that makes me think you
> are using linked servers. Try the query plan with the query analyzer and I
> bet you find out that it is pulling all the data locally and then applying
> the where clause. You have several options. One is to use Openquery instead
> of the 4 part syntax. The other is to not use linked database. I only use
> linked databases as a last resort. I suggest using a shared datasource in RS
> to Oracle instead. For straight reporting I never use linked databases. It
> complicates matters and buys you very little. What I do is have a shared
> datasource that has a readonly user credential stored by RS. This takes
> advantage of connection pooling and will be either much faster or much
> easier or both than using linked databases.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Shared datasources are a RS thing, not a SQL Server thing. If what you are
doing is creating a query for the dataset in RS then you can use a shared
data source that the report is based on that goes to Oracle instead of to
SQL Server. If you have to use a stored procedure that resides in SQL Server
then you have to use OpenQuery AND you have to assemble the string to use
with it since you can not use a parameter with openquery. So, if you can get
away with either not using the stored procedure in SQL Server OR if you can
put the stored procedure in Oracle instead then that would be better. So
your options are:
1. Put the query in Report Services dataset and use a data source that goes
directly against Oracle
2. Use the SQL Server stored procedure with OpenQuery assembling the query
string (which gets to be a real pain with single quotes)
3. Use Oracle Stored predures.
Here is an example of some OpenQuery (I happen to be hip deep in this right
now).
select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
select @.SQL = 'insert collect_values select * from openquery(linktest,''' +
'SELECT * from collect_values where time_tag > ' + @.FROM + ' and time_tag <=' + @.TO + ''')'
execute (@.SQL)
Notice the wonderful messing with single quotes.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> Hi Bruce,
> Many thanks for your reply ! Absolutely correct on all
accounts
> (linked server, plan). Am now researching the "shared datasource" option.
Do
> you still reference it from your stored procedures ? That probably does
not
> change.
> Any tech article tips or links on this greatly appreciated. Steve.
> "Bruce L-C [MVP]" wrote:
> > You mention query analyzer and Oracle sql plus. OK, that makes me think
you
> > are using linked servers. Try the query plan with the query analyzer and
I
> > bet you find out that it is pulling all the data locally and then
applying
> > the where clause. You have several options. One is to use Openquery
instead
> > of the 4 part syntax. The other is to not use linked database. I only
use
> > linked databases as a last resort. I suggest using a shared datasource
in RS
> > to Oracle instead. For straight reporting I never use linked databases.
It
> > complicates matters and buys you very little. What I do is have a shared
> > datasource that has a readonly user credential stored by RS. This takes
> > advantage of connection pooling and will be either much faster or much
> > easier or both than using linked databases.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > Hi,
> > > Have the following sql analyzer query which takes 55 seconds to
return
> > > data.
> > > SELECT i.TRANSACTION_DATE,
> > > i.TYPE,
> > > i.PART_ID,
> > > i.WAREHOUSE_ID,
> > > i.TRANSACTION_ID,
> > > i.WORKORDER_BASE_ID,
> > > i.QTY,
> > > i.CLASS
> > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > order by i.TRANSACTION_ID
> > >
> > > Remove the and's and the response goes down to 1 second.
> > > SELECT i.TRANSACTION_DATE,
> > > i.TYPE,
> > > i.PART_ID,
> > > i.WAREHOUSE_ID,
> > > i.TRANSACTION_ID,
> > > i.WORKORDER_BASE_ID,
> > > i.QTY,
> > > i.CLASS
> > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > WHERE i.TRANSACTION_ID = 526123
> > > order by i.TRANSACTION_ID
> > >
> > > The question is why ? If you look at the data in the single row
> > returned...
> > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
526123
> > > 17408 1.0000 R
> > > the record clearly contains the correct data. Any insight on this
GREATLY
> > > appreciated because we have a reporting services query based on a date
> > range
> > > which also has the same poor response time. If you put the same query
into
> > an
> > > Oracle sql Plus query the data returns instantly.
> > >
> > > Thanks, Steve.
> > >
> > >
> >
> >
> >|||Would you use this approach if you were to transport data every night from
oracle to sql or
would you use some kind of bulk export instead ?
/Michael V.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Shared datasources are a RS thing, not a SQL Server thing. If what you are
> doing is creating a query for the dataset in RS then you can use a shared
> data source that the report is based on that goes to Oracle instead of to
> SQL Server. If you have to use a stored procedure that resides in SQL
Server
> then you have to use OpenQuery AND you have to assemble the string to use
> with it since you can not use a parameter with openquery. So, if you can
get
> away with either not using the stored procedure in SQL Server OR if you
can
> put the stored procedure in Oracle instead then that would be better. So
> your options are:
> 1. Put the query in Report Services dataset and use a data source that
goes
> directly against Oracle
> 2. Use the SQL Server stored procedure with OpenQuery assembling the query
> string (which gets to be a real pain with single quotes)
> 3. Use Oracle Stored predures.
> Here is an example of some OpenQuery (I happen to be hip deep in this
right
> now).
> select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> select @.SQL = 'insert collect_values select * from openquery(linktest,'''
+
> 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and time_tag
<=> ' + @.TO + ''')'
> execute (@.SQL)
> Notice the wonderful messing with single quotes.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > Hi Bruce,
> > Many thanks for your reply ! Absolutely correct on all
> accounts
> > (linked server, plan). Am now researching the "shared datasource"
option.
> Do
> > you still reference it from your stored procedures ? That probably does
> not
> > change.
> > Any tech article tips or links on this greatly appreciated. Steve.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You mention query analyzer and Oracle sql plus. OK, that makes me
think
> you
> > > are using linked servers. Try the query plan with the query analyzer
and
> I
> > > bet you find out that it is pulling all the data locally and then
> applying
> > > the where clause. You have several options. One is to use Openquery
> instead
> > > of the 4 part syntax. The other is to not use linked database. I only
> use
> > > linked databases as a last resort. I suggest using a shared datasource
> in RS
> > > to Oracle instead. For straight reporting I never use linked
databases.
> It
> > > complicates matters and buys you very little. What I do is have a
shared
> > > datasource that has a readonly user credential stored by RS. This
takes
> > > advantage of connection pooling and will be either much faster or much
> > > easier or both than using linked databases.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > Hi,
> > > > Have the following sql analyzer query which takes 55 seconds to
> return
> > > > data.
> > > > SELECT i.TRANSACTION_DATE,
> > > > i.TYPE,
> > > > i.PART_ID,
> > > > i.WAREHOUSE_ID,
> > > > i.TRANSACTION_ID,
> > > > i.WORKORDER_BASE_ID,
> > > > i.QTY,
> > > > i.CLASS
> > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > order by i.TRANSACTION_ID
> > > >
> > > > Remove the and's and the response goes down to 1 second.
> > > > SELECT i.TRANSACTION_DATE,
> > > > i.TYPE,
> > > > i.PART_ID,
> > > > i.WAREHOUSE_ID,
> > > > i.TRANSACTION_ID,
> > > > i.WORKORDER_BASE_ID,
> > > > i.QTY,
> > > > i.CLASS
> > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > WHERE i.TRANSACTION_ID = 526123
> > > > order by i.TRANSACTION_ID
> > > >
> > > > The question is why ? If you look at the data in the single row
> > > returned...
> > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> 526123
> > > > 17408 1.0000 R
> > > > the record clearly contains the correct data. Any insight on this
> GREATLY
> > > > appreciated because we have a reporting services query based on a
date
> > > range
> > > > which also has the same poor response time. If you put the same
query
> into
> > > an
> > > > Oracle sql Plus query the data returns instantly.
> > > >
> > > > Thanks, Steve.
> > > >
> > > >
> > >
> > >
> > >
>|||Hmmm, this has nothing to do with Reporting Services but that doesn't mean I
don't have an opinion <g>.
It depends on what you are doing and how much data you have plus how complex
is the extraction you are doing. You should look at DTS if you have
transformations you are doing. If all you are doing is pulling and loading
data again it depends on the quantity. It is very easy to do a quick test. I
just happen to be working on a datamart that I am keeping in sync every 5
minutes. There are some tables that I just do once a night and they are more
like bulk load. What I do is I have a database in SQL Server that is for
syncing. It has just the tables with no indexes, pk or anything, just the
columns. I use the openquery technique and insert the data into the table (I
add other error checking, for instance if the table already has data in it
then it means the process failed and I don't do anything). This comes over
very fast. Then I move the data from that table to the target table in the
other database (which is on the same server). I would do a test, you might
be surprised how fast this technique is.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> Would you use this approach if you were to transport data every night from
> oracle to sql or
> would you use some kind of bulk export instead ?
> /Michael V.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > Shared datasources are a RS thing, not a SQL Server thing. If what you
are
> > doing is creating a query for the dataset in RS then you can use a
shared
> > data source that the report is based on that goes to Oracle instead of
to
> > SQL Server. If you have to use a stored procedure that resides in SQL
> Server
> > then you have to use OpenQuery AND you have to assemble the string to
use
> > with it since you can not use a parameter with openquery. So, if you can
> get
> > away with either not using the stored procedure in SQL Server OR if you
> can
> > put the stored procedure in Oracle instead then that would be better. So
> > your options are:
> > 1. Put the query in Report Services dataset and use a data source that
> goes
> > directly against Oracle
> > 2. Use the SQL Server stored procedure with OpenQuery assembling the
query
> > string (which gets to be a real pain with single quotes)
> > 3. Use Oracle Stored predures.
> >
> > Here is an example of some OpenQuery (I happen to be hip deep in this
> right
> > now).
> >
> > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > select @.SQL = 'insert collect_values select * from
openquery(linktest,'''
> +
> > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
time_tag
> <=> > ' + @.TO + ''')'
> > execute (@.SQL)
> >
> > Notice the wonderful messing with single quotes.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > Hi Bruce,
> > > Many thanks for your reply ! Absolutely correct on all
> > accounts
> > > (linked server, plan). Am now researching the "shared datasource"
> option.
> > Do
> > > you still reference it from your stored procedures ? That probably
does
> > not
> > > change.
> > > Any tech article tips or links on this greatly appreciated. Steve.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > You mention query analyzer and Oracle sql plus. OK, that makes me
> think
> > you
> > > > are using linked servers. Try the query plan with the query analyzer
> and
> > I
> > > > bet you find out that it is pulling all the data locally and then
> > applying
> > > > the where clause. You have several options. One is to use Openquery
> > instead
> > > > of the 4 part syntax. The other is to not use linked database. I
only
> > use
> > > > linked databases as a last resort. I suggest using a shared
datasource
> > in RS
> > > > to Oracle instead. For straight reporting I never use linked
> databases.
> > It
> > > > complicates matters and buys you very little. What I do is have a
> shared
> > > > datasource that has a readonly user credential stored by RS. This
> takes
> > > > advantage of connection pooling and will be either much faster or
much
> > > > easier or both than using linked databases.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > Hi,
> > > > > Have the following sql analyzer query which takes 55 seconds to
> > return
> > > > > data.
> > > > > SELECT i.TRANSACTION_DATE,
> > > > > i.TYPE,
> > > > > i.PART_ID,
> > > > > i.WAREHOUSE_ID,
> > > > > i.TRANSACTION_ID,
> > > > > i.WORKORDER_BASE_ID,
> > > > > i.QTY,
> > > > > i.CLASS
> > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > > order by i.TRANSACTION_ID
> > > > >
> > > > > Remove the and's and the response goes down to 1 second.
> > > > > SELECT i.TRANSACTION_DATE,
> > > > > i.TYPE,
> > > > > i.PART_ID,
> > > > > i.WAREHOUSE_ID,
> > > > > i.TRANSACTION_ID,
> > > > > i.WORKORDER_BASE_ID,
> > > > > i.QTY,
> > > > > i.CLASS
> > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > order by i.TRANSACTION_ID
> > > > >
> > > > > The question is why ? If you look at the data in the single row
> > > > returned...
> > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > 526123
> > > > > 17408 1.0000 R
> > > > > the record clearly contains the correct data. Any insight on this
> > GREATLY
> > > > > appreciated because we have a reporting services query based on a
> date
> > > > range
> > > > > which also has the same poor response time. If you put the same
> query
> > into
> > > > an
> > > > > Oracle sql Plus query the data returns instantly.
> > > > >
> > > > > Thanks, Steve.
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>|||Hi Bruce,
Thanks for your reply !!! Mission accomplished. Really just a
matter of selectinng the oracle driver (from oracle) when configuring the
linked server. Performance is instant ! Details are in Metalink doc
Note:191368.1.
Thanks again. Steve.
"Bruce L-C [MVP]" wrote:
> Hmmm, this has nothing to do with Reporting Services but that doesn't mean I
> don't have an opinion <g>.
> It depends on what you are doing and how much data you have plus how complex
> is the extraction you are doing. You should look at DTS if you have
> transformations you are doing. If all you are doing is pulling and loading
> data again it depends on the quantity. It is very easy to do a quick test. I
> just happen to be working on a datamart that I am keeping in sync every 5
> minutes. There are some tables that I just do once a night and they are more
> like bulk load. What I do is I have a database in SQL Server that is for
> syncing. It has just the tables with no indexes, pk or anything, just the
> columns. I use the openquery technique and insert the data into the table (I
> add other error checking, for instance if the table already has data in it
> then it means the process failed and I don't do anything). This comes over
> very fast. Then I move the data from that table to the target table in the
> other database (which is on the same server). I would do a test, you might
> be surprised how fast this technique is.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
> news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> > Would you use this approach if you were to transport data every night from
> > oracle to sql or
> > would you use some kind of bulk export instead ?
> >
> > /Michael V.
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > Shared datasources are a RS thing, not a SQL Server thing. If what you
> are
> > > doing is creating a query for the dataset in RS then you can use a
> shared
> > > data source that the report is based on that goes to Oracle instead of
> to
> > > SQL Server. If you have to use a stored procedure that resides in SQL
> > Server
> > > then you have to use OpenQuery AND you have to assemble the string to
> use
> > > with it since you can not use a parameter with openquery. So, if you can
> > get
> > > away with either not using the stored procedure in SQL Server OR if you
> > can
> > > put the stored procedure in Oracle instead then that would be better. So
> > > your options are:
> > > 1. Put the query in Report Services dataset and use a data source that
> > goes
> > > directly against Oracle
> > > 2. Use the SQL Server stored procedure with OpenQuery assembling the
> query
> > > string (which gets to be a real pain with single quotes)
> > > 3. Use Oracle Stored predures.
> > >
> > > Here is an example of some OpenQuery (I happen to be hip deep in this
> > right
> > > now).
> > >
> > > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > > select @.SQL = 'insert collect_values select * from
> openquery(linktest,'''
> > +
> > > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
> time_tag
> > <=> > > ' + @.TO + ''')'
> > > execute (@.SQL)
> > >
> > > Notice the wonderful messing with single quotes.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > > Hi Bruce,
> > > > Many thanks for your reply ! Absolutely correct on all
> > > accounts
> > > > (linked server, plan). Am now researching the "shared datasource"
> > option.
> > > Do
> > > > you still reference it from your stored procedures ? That probably
> does
> > > not
> > > > change.
> > > > Any tech article tips or links on this greatly appreciated. Steve.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > You mention query analyzer and Oracle sql plus. OK, that makes me
> > think
> > > you
> > > > > are using linked servers. Try the query plan with the query analyzer
> > and
> > > I
> > > > > bet you find out that it is pulling all the data locally and then
> > > applying
> > > > > the where clause. You have several options. One is to use Openquery
> > > instead
> > > > > of the 4 part syntax. The other is to not use linked database. I
> only
> > > use
> > > > > linked databases as a last resort. I suggest using a shared
> datasource
> > > in RS
> > > > > to Oracle instead. For straight reporting I never use linked
> > databases.
> > > It
> > > > > complicates matters and buys you very little. What I do is have a
> > shared
> > > > > datasource that has a readonly user credential stored by RS. This
> > takes
> > > > > advantage of connection pooling and will be either much faster or
> much
> > > > > easier or both than using linked databases.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > > Hi,
> > > > > > Have the following sql analyzer query which takes 55 seconds to
> > > return
> > > > > > data.
> > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > i.TYPE,
> > > > > > i.PART_ID,
> > > > > > i.WAREHOUSE_ID,
> > > > > > i.TRANSACTION_ID,
> > > > > > i.WORKORDER_BASE_ID,
> > > > > > i.QTY,
> > > > > > i.CLASS
> > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > > > order by i.TRANSACTION_ID
> > > > > >
> > > > > > Remove the and's and the response goes down to 1 second.
> > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > i.TYPE,
> > > > > > i.PART_ID,
> > > > > > i.WAREHOUSE_ID,
> > > > > > i.TRANSACTION_ID,
> > > > > > i.WORKORDER_BASE_ID,
> > > > > > i.QTY,
> > > > > > i.CLASS
> > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > > order by i.TRANSACTION_ID
> > > > > >
> > > > > > The question is why ? If you look at the data in the single row
> > > > > returned...
> > > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > > 526123
> > > > > > 17408 1.0000 R
> > > > > > the record clearly contains the correct data. Any insight on this
> > > GREATLY
> > > > > > appreciated because we have a reporting services query based on a
> > date
> > > > > range
> > > > > > which also has the same poor response time. If you put the same
> > query
> > > into
> > > > > an
> > > > > > Oracle sql Plus query the data returns instantly.
> > > > > >
> > > > > > Thanks, Steve.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
>
>|||You will still need to be watchful if you use the 4 part naming. The
decision on what you send to Oracle and what to process locally is done by
SQL Server. Looking at the query plan in query analyzer will always show you
when this has happened.
In particular, the where clause can all of a sudden cause a the processing
to occur locally.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:70A1E1CF-95A4-422B-9026-3837715525D0@.microsoft.com...
> Hi Bruce,
> Thanks for your reply !!! Mission accomplished. Really just
a
> matter of selectinng the oracle driver (from oracle) when configuring the
> linked server. Performance is instant ! Details are in Metalink doc
> Note:191368.1.
> Thanks again. Steve.
> "Bruce L-C [MVP]" wrote:
> > Hmmm, this has nothing to do with Reporting Services but that doesn't
mean I
> > don't have an opinion <g>.
> >
> > It depends on what you are doing and how much data you have plus how
complex
> > is the extraction you are doing. You should look at DTS if you have
> > transformations you are doing. If all you are doing is pulling and
loading
> > data again it depends on the quantity. It is very easy to do a quick
test. I
> > just happen to be working on a datamart that I am keeping in sync every
5
> > minutes. There are some tables that I just do once a night and they are
more
> > like bulk load. What I do is I have a database in SQL Server that is for
> > syncing. It has just the tables with no indexes, pk or anything, just
the
> > columns. I use the openquery technique and insert the data into the
table (I
> > add other error checking, for instance if the table already has data in
it
> > then it means the process failed and I don't do anything). This comes
over
> > very fast. Then I move the data from that table to the target table in
the
> > other database (which is on the same server). I would do a test, you
might
> > be surprised how fast this technique is.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
> > news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> > > Would you use this approach if you were to transport data every night
from
> > > oracle to sql or
> > > would you use some kind of bulk export instead ?
> > >
> > > /Michael V.
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > > Shared datasources are a RS thing, not a SQL Server thing. If what
you
> > are
> > > > doing is creating a query for the dataset in RS then you can use a
> > shared
> > > > data source that the report is based on that goes to Oracle instead
of
> > to
> > > > SQL Server. If you have to use a stored procedure that resides in
SQL
> > > Server
> > > > then you have to use OpenQuery AND you have to assemble the string
to
> > use
> > > > with it since you can not use a parameter with openquery. So, if you
can
> > > get
> > > > away with either not using the stored procedure in SQL Server OR if
you
> > > can
> > > > put the stored procedure in Oracle instead then that would be
better. So
> > > > your options are:
> > > > 1. Put the query in Report Services dataset and use a data source
that
> > > goes
> > > > directly against Oracle
> > > > 2. Use the SQL Server stored procedure with OpenQuery assembling the
> > query
> > > > string (which gets to be a real pain with single quotes)
> > > > 3. Use Oracle Stored predures.
> > > >
> > > > Here is an example of some OpenQuery (I happen to be hip deep in
this
> > > right
> > > > now).
> > > >
> > > > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > > > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > > > select @.SQL = 'insert collect_values select * from
> > openquery(linktest,'''
> > > +
> > > > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
> > time_tag
> > > <=> > > > ' + @.TO + ''')'
> > > > execute (@.SQL)
> > > >
> > > > Notice the wonderful messing with single quotes.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > > > Hi Bruce,
> > > > > Many thanks for your reply ! Absolutely correct on
all
> > > > accounts
> > > > > (linked server, plan). Am now researching the "shared datasource"
> > > option.
> > > > Do
> > > > > you still reference it from your stored procedures ? That probably
> > does
> > > > not
> > > > > change.
> > > > > Any tech article tips or links on this greatly appreciated. Steve.
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > You mention query analyzer and Oracle sql plus. OK, that makes
me
> > > think
> > > > you
> > > > > > are using linked servers. Try the query plan with the query
analyzer
> > > and
> > > > I
> > > > > > bet you find out that it is pulling all the data locally and
then
> > > > applying
> > > > > > the where clause. You have several options. One is to use
Openquery
> > > > instead
> > > > > > of the 4 part syntax. The other is to not use linked database. I
> > only
> > > > use
> > > > > > linked databases as a last resort. I suggest using a shared
> > datasource
> > > > in RS
> > > > > > to Oracle instead. For straight reporting I never use linked
> > > databases.
> > > > It
> > > > > > complicates matters and buys you very little. What I do is have
a
> > > shared
> > > > > > datasource that has a readonly user credential stored by RS.
This
> > > takes
> > > > > > advantage of connection pooling and will be either much faster
or
> > much
> > > > > > easier or both than using linked databases.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in
message
> > > > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > > > Hi,
> > > > > > > Have the following sql analyzer query which takes 55
seconds to
> > > > return
> > > > > > > data.
> > > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > > i.TYPE,
> > > > > > > i.PART_ID,
> > > > > > > i.WAREHOUSE_ID,
> > > > > > > i.TRANSACTION_ID,
> > > > > > > i.WORKORDER_BASE_ID,
> > > > > > > i.QTY,
> > > > > > > i.CLASS
> > > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE ='I'
> > > > > > > order by i.TRANSACTION_ID
> > > > > > >
> > > > > > > Remove the and's and the response goes down to 1 second.
> > > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > > i.TYPE,
> > > > > > > i.PART_ID,
> > > > > > > i.WAREHOUSE_ID,
> > > > > > > i.TRANSACTION_ID,
> > > > > > > i.WORKORDER_BASE_ID,
> > > > > > > i.QTY,
> > > > > > > i.CLASS
> > > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > > > order by i.TRANSACTION_ID
> > > > > > >
> > > > > > > The question is why ? If you look at the data in the single
row
> > > > > > returned...
> > > > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > > > 526123
> > > > > > > 17408 1.0000 R
> > > > > > > the record clearly contains the correct data. Any insight on
this
> > > > GREATLY
> > > > > > > appreciated because we have a reporting services query based
on a
> > > date
> > > > > > range
> > > > > > > which also has the same poor response time. If you put the
same
> > > query
> > > > into
> > > > > > an
> > > > > > > Oracle sql Plus query the data returns instantly.
> > > > > > >
> > > > > > > Thanks, Steve.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >