Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlslick
Yak Posting Veteran
83 Posts |
 Posted - 2012-03-02 : 15:54:58
|
| Hello SQL Gods!We have created a linked Server to an Oracle 10g Database using the following parameters:Provider: Microsoft OLE DB Provider for Oracle (MSDAORA)Product Name: OracleWe are able to establish a connection but are having performance issues with a script, which takes about 6 hours to execute! The script is nothing crazy. There are a couple of UNION queries and a couple of sub-queries, but no looping or complex syntax. The exact same script takes less than 5 seconds to execute on the Oracle DB using Oracle SQL Syntax. When we initially setup the Linked Server we tried using the Oracle Provider for OLE DB (OraOLEDB.Oracle) instead of the MSDAORA but we had a difficult time even establishing a connection so we settled for the MSDAORA Provider.Any idea how we can get the OraOLEDB.Oracle Provider to work so we can compare performance or if there is additional configuration required when using the MSDAORA Provider?Thanks so much!J |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-02 : 17:00:40
|
| can you try making a procedure using the current query in Oracle and calling it from SQL through linked server?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2012-03-05 : 08:38:31
|
| Unfortunately, I only have READ-ONLY access to the Oracle database. Can't even create temporary tables.Any other suggestions? |
|
|
|
|
|
|
Script through Oracle Linked Server