Performance issues with linked Oracle database

Performance issues with linked Oracle database

Post by Aaron Rollof » Tue, 22 Aug 2000 04:00:00



We've created a link to an Oracle 7.3 server using sp_addlinkedserver,
but are having performance issues with the connection.  We anticipated
some loss of speed with the link, but queries that would run almost
instantaneously on local SQL Server tables are taking up to 15 minutes
to execute across the link.  Creating indexes on the Oracle tables
hasn't seemed to help.

Could anyone provide some suggestions for improving the response time
when accessing data over the link?  Are there any additional
configuration options that might help our cause?

Thanks in advance,
--
Aaron Rolloff

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Performance issues with linked Oracle database

Post by <Contac.. » Thu, 24 Aug 2000 04:00:00


Greetings,

Have you tried looking at the webpage
http://support.microsoft.com/support/SQL/Content/inprodhlp/_sp_addlin...
er.asp?LN=EN-US&SD=gn&FR=0 for different ways to set up your connection?

We are using the Oracle 7.3 and SQL 7 on an NT4.0 system.  I will do some
testing over this weekend to see if there is any performance issues.  Let's
keep each other advised of the progress if you don't mind.

Does anyone else have some insight?

Thanks,
Streamliners


Quote:> We've created a link to an Oracle 7.3 server using sp_addlinkedserver,
> but are having performance issues with the connection.  We anticipated
> some loss of speed with the link, but queries that would run almost
> instantaneously on local SQL Server tables are taking up to 15 minutes
> to execute across the link.  Creating indexes on the Oracle tables
> hasn't seemed to help.

> Could anyone provide some suggestions for improving the response time
> when accessing data over the link?  Are there any additional
> configuration options that might help our cause?

> Thanks in advance,
> --
> Aaron Rolloff

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

Performance issues with linked Oracle database

Post by Aaron Rollof » Fri, 25 Aug 2000 04:00:00


We are using SQL 7.0 on an NT4.0 system as well - sorry, I forgot to
include that in the original message.

I've looked at the sp_addlinkedserver help documentation, without too
much luck, but I did notice a "see also" link at the bottom of the URL
you sent, and found the help docs for sp_serveroption which might prove
helpful:
http://support.microsoft.com/support/SQL/Content/inprodhlp/_sp_serveropt
ion.asp

I'm working with our dba's to see about adding the 'Value collation
compatible', 'rpc, and 'rpc out' options.  When enabled, the first
allows character comparisons to be made on the linkserver's character
columns - and possibly take advantage of the linkedserver's indexes as
well(?).  Without it, the SQL Server must bring the data across and do
the comparisons locally.  This in itself could help our performance
greatly.  I will keep you posted when we find out more.

Thanks,
Aaron



> Greetings,

> Have you tried looking at the webpage

http://support.microsoft.com/support/SQL/Content/inprodhlp/_sp_addlinked
serv

> er.asp?LN=EN-US&SD=gn&FR=0 for different ways to set up your
connection?

> We are using the Oracle 7.3 and SQL 7 on an NT4.0 system.  I will do
some
> testing over this weekend to see if there is any performance issues.
Let's
> keep each other advised of the progress if you don't mind.

> Does anyone else have some insight?

> Thanks,
> Streamliners



> > We've created a link to an Oracle 7.3 server using
sp_addlinkedserver,
> > but are having performance issues with the connection.  We
anticipated
> > some loss of speed with the link, but queries that would run almost
> > instantaneously on local SQL Server tables are taking up to 15
minutes
> > to execute across the link.  Creating indexes on the Oracle tables
> > hasn't seemed to help.

> > Could anyone provide some suggestions for improving the response
time
> > when accessing data over the link?  Are there any additional
> > configuration options that might help our cause?

> > Thanks in advance,
> > --
> > Aaron Rolloff

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

--
Aaron Rolloff

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Performance issues with linked Oracle database

Post by Aaron Rollof » Fri, 25 Aug 2000 04:00:00


Found another little tidbit that might be of use.  The SQL Server
system administrator can configure the OLE DB provider to set a couple
of options.
1. In Enterprise Manager, right-click on the server definition that
uses the OLE DB provider you want to set the properties.
2. On the General tab, click Options...then set the properties

The settings that we found that might help our performance are:
1. NestedQueries -  If nonzero, indicates that the provider allows
nested SELECT statements in the FROM clause.  Setting this option would
allow SQL Server to delegate certain queries to the provider that
nesting SELECT statements in the FROM clause

2. IndexAsAccessPath - If nonzero, SQL Server attempts to use indexes
of the provider to fetch data (by default, indexes are only used for
metadata and are never opened)

Enjoy,
Aaron



> Greetings,

> Have you tried looking at the webpage

http://support.microsoft.com/support/SQL/Content/inprodhlp/_sp_addlinked
serv

> er.asp?LN=EN-US&SD=gn&FR=0 for different ways to set up your
connection?

> We are using the Oracle 7.3 and SQL 7 on an NT4.0 system.  I will do
some
> testing over this weekend to see if there is any performance issues.
Let's
> keep each other advised of the progress if you don't mind.

> Does anyone else have some insight?

> Thanks,
> Streamliners



> > We've created a link to an Oracle 7.3 server using
sp_addlinkedserver,
> > but are having performance issues with the connection.  We
anticipated
> > some loss of speed with the link, but queries that would run almost
> > instantaneously on local SQL Server tables are taking up to 15
minutes
> > to execute across the link.  Creating indexes on the Oracle tables
> > hasn't seemed to help.

> > Could anyone provide some suggestions for improving the response
time
> > when accessing data over the link?  Are there any additional
> > configuration options that might help our cause?

> > Thanks in advance,
> > --
> > Aaron Rolloff

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

--
Aaron Rolloff

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Performance issues with linked Oracle database

Post by <Contac.. » Sat, 26 Aug 2000 04:00:00


Thanks for the info, I will look over this, this weekend.  I will let you
know if I found out anything else.


> Found another little tidbit that might be of use.  The SQL Server
> system administrator can configure the OLE DB provider to set a couple
> of options.
> 1. In Enterprise Manager, right-click on the server definition that
> uses the OLE DB provider you want to set the properties.
> 2. On the General tab, click Options...then set the properties

> The settings that we found that might help our performance are:
> 1. NestedQueries -  If nonzero, indicates that the provider allows
> nested SELECT statements in the FROM clause.  Setting this option would
> allow SQL Server to delegate certain queries to the provider that
> nesting SELECT statements in the FROM clause

> 2. IndexAsAccessPath - If nonzero, SQL Server attempts to use indexes
> of the provider to fetch data (by default, indexes are only used for
> metadata and are never opened)

> Enjoy,
> Aaron



> > Greetings,

> > Have you tried looking at the webpage

> http://support.microsoft.com/support/SQL/Content/inprodhlp/_sp_addlinked
> serv
> > er.asp?LN=EN-US&SD=gn&FR=0 for different ways to set up your
> connection?

> > We are using the Oracle 7.3 and SQL 7 on an NT4.0 system.  I will do
> some
> > testing over this weekend to see if there is any performance issues.
> Let's
> > keep each other advised of the progress if you don't mind.

> > Does anyone else have some insight?

> > Thanks,
> > Streamliners



> > > We've created a link to an Oracle 7.3 server using
> sp_addlinkedserver,
> > > but are having performance issues with the connection.  We
> anticipated
> > > some loss of speed with the link, but queries that would run almost
> > > instantaneously on local SQL Server tables are taking up to 15
> minutes
> > > to execute across the link.  Creating indexes on the Oracle tables
> > > hasn't seemed to help.

> > > Could anyone provide some suggestions for improving the response
> time
> > > when accessing data over the link?  Are there any additional
> > > configuration options that might help our cause?

> > > Thanks in advance,
> > > --
> > > Aaron Rolloff

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> --
> Aaron Rolloff

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. Oracle Linked Server Performance Issues

I have a SQL Server 2000 instance running on Windows 2000 with most recent
service packs.  I have a linked servers defined using both the OLE DB
provider for ODBC drivers and the OLE DB provider for Oracle.  The issue
with performance is that querys to the Oracle DB using the SQL server linked
server are extremely slow.  Returning very small datasets from simple querys
that return immediatly using straight ODBC take 3+ minutes to return using
the linked server through SQL Server.

Any input on this problem is appreciated.

Thanks,
Chris

2. ORA-07690 on OpenVMS 7.1

3. Performance issues with linked Oracle server

4. Problem with create temporary table in PL/SQL

5. Performance Issues with Oracle database and Web server

6. ejbCreate() fails using JDBC driver

7. Access ODBC Link to SQL2000 Performance Issues

8. Newbie: Managing Users

9. Poor performance issuing an UPDATE to a linked SQL 7 server

10. Informix linked server performance issue

11. Performance issue when connecting to a linked server

12. Linked Server Performance Issue

13. Performance issue when connecting to a linked server