Hello All,

I have a SQL Server Database (2000) that pulls data from Oracle (9i). Currently I am using a 'Linked Server' for Oracle, as I am passing values from SQL Server to Oracle in a 'Where Clause' to retrieve the data that is of interest. The problem with this is that the package is taking over 2 hours to run, whereas if I just 'hard-coded' the values in the SQL Statment getting passed to Oracle (without the use of Linked Servers), the entire package runs in under 15 minutes.

Here is the environment:
SQL Server has a table called Emp_Data, which has a primary key field called EMP_NBR, and an additional field called EMP_ACTIVE.

Oracle has a table called HR_TABLE which also has a field called EMP_NBR.

The thing is, I am only interested in the records that are in (or have recently been added in) the EMP_DATA where the EMP_ACTIVE field = 'YES', and those values (EMP_NBR) are what I am interested in passing into and receiving from Oracle.

Is there a better way of implementing this, without the use of 'Linked Servers'?

Any help would be great!!!

Thanks,

Tom.

Is this possible to use OPENROWSET or OPENQUERY statements?

"Tom" <anonymous (AT) discussions (DOT) microsoft.com> wrote

from SQL Server to Oracle in a 'Where Clause' to retrieve the data that is
of interest. The problem with this is that the package is taking over 2
hours to run, whereas if I just 'hard-coded' the values in the SQL Statment
getting passed to Oracle (without the use of Linked Servers), the entire
package runs in under 15 minutes.
those values (EMP_NBR) are what I am interested in passing into and
receiving from Oracle.

Do mean that you do not know what OPENROWSET and OPENQUERY are?

Of so, try looking them up in the index of SQL Server Books Online, and
this should give you a start, including examples.


In message <9343B13F-A221-48D9-84A4-A6802A1E8909 (AT) microsoft (DOT) com>, Tom
<anonymous (AT) discussions (DOT) microsoft.com> writes
Currently I am using a 'Linked Server' for Oracle, as I am passing
values
from SQL Server to Oracle in a 'Where Clause' to retrieve the data
that is
of interest. The problem with this is that the package is taking
over 2
hours to run, whereas if I just 'hard-coded' the values in the SQL
Statment
getting passed to Oracle (without the use of Linked Servers), the
entire
package runs in under 15 minutes.
called EMP_NBR, and an additional field called EMP_ACTIVE.
recently been added in) the EMP_DATA where the EMP_ACTIVE field =
'YES', and
those values (EMP_NBR) are what I am interested in passing into and
receiving from Oracle.
Servers'?



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

What is the driver you are using to connect? Choosing either the MS
or Oracle provided is sigificantly slower than a 3rd party one.
(Disclaimer: I work for a vendor that supplies ODBC drivers). MS
linked servers (and the oracle equivalent Heterogenous Services)
spend a lot of time querying the drivers and parse and restructuring
the statements. This can result in many trips between the DB's and
wasted cycles

2 suggestions:

1) Test some newer drivers. Openlink has a free 30 day trial

2) Make sure that you are not sending too many seperate SQL
Statements. Try 'batch' them if possible.

If you have any questions, Feel free to contact me


"Ilya Margolin" <ilya (AT) unapen (DOT) com> wrote