?
Solved

Oracle 8 Linked Server - Extremely Slow Performance

Posted on 2004-11-11
12
Medium Priority
?
4,984 Views
Last Modified: 2007-12-19
We have a Oracle 8 database set up as a Linked Server in our installation of SQL Server 2000.

When running simple "SELECT * FROM WHERE" queries it's running very slow.

For example, the query "SELECT * FROM Commitment WHERE Week = 200430" takes between 17 and 23 seconds to run on SQL Server.  The same query using a linked table via Access 97 takes milliseconds to run.

Has anyone any ideas as to what is slowing the performance?

[SQL Server 2000
Windows NT4
Oracle 8]
0
Comment
Question by:joboy
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 12

Expert Comment

by:ill
ID: 12553557
please, provide more datails
query is running using openquery?
connection is of type ?  ado, ole, odbc ?
0
 
LVL 5

Author Comment

by:joboy
ID: 12553672
Query that creates the view from the Linked Server

CREATE VIEW dbo.v_MARS_COMMITMENT
AS
SELECT     CMMT.*
FROM         OPENQUERY(MISPRD, "SELECT * FROM COMMITMENT WHERE REVISED_WEEK='200434'") CMMT

Query that is taking approx 17secs is executed using Query Analyser with a simple "SELECT * FROM v_MARS_COMMITMENT".
0
 
LVL 12

Expert Comment

by:ill
ID: 12553845
what's the connection type of MISPRD?
there are speed problems with odbc connections .
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 5

Author Comment

by:joboy
ID: 12553901
The MISPRD connection is setup using ODBC on the SQL Server box.  The connection uses the Oracle 8 driver.  I have tried changing the driver to the Microsoft Oracle ODBC driver, but this had no impact on performance.
0
 
LVL 12

Expert Comment

by:ill
ID: 12553982
i suggest ,change to Microsoft OLE DB Provider for Oracle.
perhaps you're getting all rows from oracle server to mssql and condition runs afterwards on your sql box only.
0
 
LVL 5

Author Comment

by:joboy
ID: 12554115
>i suggest ,change to Microsoft OLE DB Provider for Oracle.

I have set up a new ODBC connection to MISPRD (now know as MS_MISPRD) using the Microst Oracle driver and created a new Linked Server to the new ODBC connection using the "Microsoft OLE DB Provider for Oracle".  This has made no difference in speed.

>perhaps you're getting all rows from oracle server to mssql and condition runs afterwards on your sql box only.

I have already tried to eliminate this possibility.  Originally the query creating the view did not have a WHERE clause.  Without the WHERE clause the query took minutes to run.  The WHERE clause was added to see if it increased performance, implying the reduction of results returned by the Oracle server.  The increased performance seen would suggest that the condition is not being run on the SQL box.
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 12557749
For this particular pattern, it may be that you have a communications protocol set up as the default protocol on your WINDOWS box that is not shared with the Oracle server.  Does a simple OPENQUERY run in Query Analyzer have the same issue?
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 2000 total points
ID: 12560594

Just Try this for adding a link server

EXEC sp_addlinkedserver
   @server = '<UR Linked Server Name>',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'TNSName'
GO

Melih SARICA
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 12560596

ODBC always slow down the performance

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question