Unanswered: Performance Issues with Linked Server's with OLEDB & ODBC
I have a SQL Server instance on my local computer and an Oracle
Database on a remote server. I want to run queries from tables
within both databases and am using linked servers to accomplish
I configure my linked server in SQL Server using the Microsoft
OLE DB Provider for Oracle and can run queries using sql server
tables and oracle tables. However, even the simplest queries
take more than 10 minutes to run.
I have the Oracle 9 Client Installed and MDAC 2.7. I configured
my registry settings to match oracle 9's settings.
However nothing i do improves the performance of the queries
through the Microsoft OLEDB Provider for Oracle.
When I use MS Access, or use an ASP page with the following
Set objConn = Server.CreateObject("ADODB.Connection")
I implement the ODBC driver that I configured in my system DSN
and both run the same queries very fast. The data comes back
without a problem.
So i believe I have narrowed down my problem to the OLEDB
Provider. However, SQL Server does not give me a choice to use
the ORACLE native ODBC Provider.
So then I tried using Pass-Through Queries and this worked alot
faster in SQL Server...I am completely confused as to whats
Linked Server Query that takes over 10 minutes:
SELECT * FROM OBERON..LOGS.DATA_PHOTO ldp where Machine=301 AND C3='I051097';
Pass-Through Query that works faster:
select * from Openquery(OBERON, 'SELECT * FROM LOGS.DATA_PHOTO ldp where MACHINE=301 AND C3=''I051097''')
From researching pass through queries, my understanding is that
it actually uses ODBC to give the whole query to the remote
database where the query is then run and the results are passed
back as a table, thats why you say select * from (query)...however if my understanding is correct, then you cant combine tables in different databases very easily. And will it work with .asp and .aspx pages?
Well I pressed ctrl-L and looked at Estimated Execution Plan and I determined that the pass-through openquery method used a remote scan.
The linked server method uses a Remote Query which then points to a Filter and then points back to the select.
obviously this difference is what is making my linked server query run so slow. 19 minutes vs 3 seconds.
the '<==' is a fat arrow and when i move my cursor over it, it says it returns 580000 rows...so whats happeneing is that all hte rows are coming back and then hitting the filter and my one row returns.
This is definitely the problem of why the query is taking forever...but how do i fix it?