January 6, 2004 at 5:14 pm
Hello,
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
this.
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
string:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oberon;uid=mfs;pwd=mfs;"
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
going on.
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?
Anybody have any insight as to whats going on?
January 6, 2004 at 6:52 pm
Well I pressed ctrl-L and looked at Estimated Execution Plan and I determined that the pass-through openquery method used a remote scan.
select <--RemoteScan
The linked server method uses a Remote Query which then points to a Filter and then points back to the select.
SELECT<--FILTER<==RemoteQuery
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?
January 7, 2004 at 3:39 am
I've stuck with the pass-through approach. That way you can send in the SQL statement best tuned for whatever RDBMS you are querying against. I use this approach a lot with Informix and Oracle by constructing a query via dynamic sql to get the syntax and variables in it and then executing the constructed statement. Performance is decent. The dynamic SQL is a little tricky at first but not too bad.
January 7, 2004 at 6:05 am
There are a number of issues involved in allow the linked server connection to work as fast as the passthrough (openquery) connection.
1) Make sure that the sql server collation is compatible with oracle. For US and Western Europe SQL_LATIN_BIN for sql server will be compatibility with oracles default characterset of ISO8859P1.
2) Change the properties of the linked server to be collation compatible. Without this, sql server is spending a lot of time converting each row from Oracles collation to the sql server collation.
3) On sql server, you may want to tweak the registry entries involving connection pooling. The default installation of MDAC will be to disconnect from Oracle after 60 seconds. You may want to keep the connections around much longer (I usually prefer infinite).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply