SQL Server Performance

OPENQUERY with JOIN problem

Discussion in 'T-SQL Performance Tuning for Developers' started by korssane, Oct 16, 2009.

  1. korssane New Member

    Hi all,

    Cansome one help me on an openquery join syntax using a local table1 ( SQL 2005 server) with a table in Oracle that is accessible via the Openquery statement.



    **What i want basically is to pull (ct.TRID,fe.RE_ID, fe.EVME) fields from anOracle table using openquery where the (ct.TRID = AEA.KEYID)...
    ** AEA.KEYID is a field in a local Table in a SQL2005 server.

    Any help will be more than appreciated.. thanks


    here is my code :


    SELECT columns...
    FROM
    (
    SELECT * FROM OPENQUERY(LINKED_ORA,
    'SELECT
    ct.TRID,
    fe.RE_ID,
    fe.EVME


    FROM ORA_TABLE1@db5 fe,
    ORA_TABLE2@db5 ct

    WHERE

    ...........

    ')

    ) AS OPQ

    JOIN SQLTABLE1 AEA ON AEA.KEYID= OPQ.TRID

    GO

  2. Adriaan New Member

    Depends on the number of rows you'll be retrieving from the Oracle database, filtering on the local KEYID values, compared to the total number of rows in the remote table.
    If it's nearly the complete data, then perhaps just download the records into a local temp table (with index on KEYID), and do the join on that.
    If the KEYID join will filter the data considerably, then you might download the records into a local temp tables in batches, spelling out the KEYID values from your local table in the criteria.
    Problem with that in SQL 2000 and before was that you could only use a fixed query statement with OPENQUERY (no dynamic SQL). The workaround for that was to do the whole query, containing OPENQUERY, as dynamic SQL.

Share This Page