Re: Linked server for Oracle on SQL server - query performance

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Kass (skass_at_drew.edu)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 09:13:12 -0400

Depending on the capabilities of the data provider used for the linked
server and various run-time settings or details of your query, such as
collations, it may not be possible for the SQL Server query processor to
send the relevant part of the WHERE condition to the Oracle server.
Instead, the SQL Server may be requesting an entire table from the
Oracle server and filtering the rows afterwards without the benefit of
the Oracle index. There is no one way to resolve this. The semantics
of the query may mean it is simply wrong to filter the query on the
Oracle side, but if there is a solution, look at data provider settings,
ANSI compliance settings, collations, and exact matching of data types.
You might also post your question in an Oracle group, since Oracle users
might be able to help.

Steve Kass
Drew University

ETT wrote:

>Hi everybody,
>
>I am experiencing the same problems as explained below. Help is appriciated.
>
>ETT
>
>"Sat" wrote:
>
>
>
>>Hi folks,
>>
>>I have created a linked server on SQL server 2000 for an Oracle 8i
>>database using Microsoft's OLE provider for ORACLE. The connection
>>works fine and I am able to run queries (select queries) agaist the
>>Oracle table from SQL query analyzer.
>>
>>But the problem is, the query is miserably slower than if I run the
>>query directly on the Oracle table using either SQL*plus or TOAD. In
>>the former case (linked table) the query run time is upwards of 7
>>seconds whereas, in the latter case (directly using SQL*plus) the
>>query runs in 200 milli seconds. The table is indexed and I am hitting
>>the index columns.
>>
>>Any ideas or suggestions? As always thanks in advance and your help
>>will be much appreciated.
>>
>>Appu.
>>
>>
>>

Relevant Pages

  • Re: MSDAORA was unable to begin a distributed transaction - why?
    ... settings described for Oracle Client 8.1 in article ... 'OraOLEDB.Oracle' was unable to begin a distributed transaction. ... The linked server is set up with RPC and RPC OUT toggled on and as far as I ... can tell all the registry entries are in place (although they use Oracle 8.1 ...
    (microsoft.public.sqlserver.security)
  • RE: Oracle linked server trouble
    ... will post as soon as we have updates for you. ... Microsoft SQL Server Support Professional ... server to an Oracle database (OLE DB connection, ... The equivalent query still ...
    (microsoft.public.sqlserver.connect)
  • Re: nested jdbc (select) queries
    ... > a huge chunk of data from an oracle 8i server. ... SQL joins? ... As you're using Oracle there seems to be at least three obvious solutions ... you can simplify how you make the query from Java in the first place. ...
    (comp.lang.java.programmer)
  • Re: ODBC cannot access Oracle tables under different schemas in one session
    ... >Access / Oracle problem, ... >referring to the proper server / SID of every branch. ... >table, when i try to query the second table under another schema, ODBC ... >shift to another schema and query their tables, ...
    (comp.databases.oracle.misc)
  • RE: Oracle linked server trouble
    ... a Microsoft Support Professional through Microsoft Product Support ... Microsoft SQL Server Support Engineer ... I have a query that runs against a linked ... server to an Oracle database (OLE DB connection, ...
    (microsoft.public.sqlserver.connect)