Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: Performance Issues with Linked Server's with OLEDB & ODBC

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    14

    some progress

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •