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