Linked server for Oracle on SQL server - query performance

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.
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.
>
|||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:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated.
>ETT
>"Sat" wrote:
>

Linked server for Oracle on SQL server - query performance

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.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.
>|||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:
[vbcol=seagreen]
>Hi everybody,
>I am experiencing the same problems as explained below. Help is appriciated
.
>ETT
>"Sat" wrote:
>
>