How to join linked server table and sql server table while using openquery

Question

I use openquery syntax to read the data from a linked server.

SELECT * FROM OPENQUERY(LinkServer, 'SELECT * FROM Product')

I want to join this link server table with an Sql server table to get my final results. For now I do it by, having a temp table.

SELECT * 
INTO #Temp_Products
FROM OPENQUERY(TREPO, 'SELECT ID, Name FROM Products')

SELECT * FROM #TEMP_PRODUCTS A
INNER JOIN ORDERED_PRODUCTS B
ON A.ID = B.ID

But as the link server product table contains huge records, it takes time to get filled into the temp table. So I think instead of pulling all product information, If I join both the tables before hand, it could increase the performance.

Can this be done.? Can someone help?

Walli

Try writing a stored procedure and applying it on the linked server. From the other server, call the stored procedure, and pass it's results into a table variable (DECLARE vTable Table). You can then use the power of cached execution plans for the stored procedure as a way to boost speed, as well as a reduced result set. Joining on the table variable is largely the same process as joining to a temp table. – Deathday

I have no control over the link server. I cannot create an SP in that server. – Walli

SELECT * FROM OPENQUERY(TREPO, 'SELECT ID, Name FROM Products') A INNER JOIN ORDERED_PRODUCTS B ON A.ID = B.ID try this – Cherin

@sankaras : The query you have mentioned is almost same as my approach. Will it increase performance.? – Walli

@muthukumar: in order to improve performance you don't bring lot of data over the network that will be very slow instead you do as TexasTubbs described. that will improve performance – Cherin

Answer 1

I don't have the ability to test this, but it does offer an opportunity to bypass the #tempTable option by directly joining to the remote server (if such connection is possible)

SELECT  A.* 
  FROM  OPENQUERY(TREPO, 'SELECT ID, Name FROM Products') A
 INNER 
  JOIN  ORDERED_PRODUCTS B
    ON  A.ID = B.ID

Here is a link to some information about linked server queries, and some pitfalls that can be encountered: http://sqlblog.com/blogs/linchi_shea/archive/2009/11/06/bad-database-practices-abusing-linked-servers.aspx

Deathday

@Muthukumar, did you notice an increase in performance? How about the result set? – Deathday

For me, using with OPENQUERY and without it took about the same amount of time. – Ortrude

@Ortrude The problem is that you're still fetching all of the records from the remote server. OPENQUERY really shines when you can already filter your result set on the remote server, meaning there's less data to be transferred. – Antagonism

@TomLint Ah okay, that makes sense. Thank you. – Ortrude

Answer 2

You can use this:

Select * from Openquery
( select table1.col1
        ,table1.col2
        ,table2.col1
from mydb."table1",mydb."table2"
where table1.id = table2.issueid --as example
)

Demineralize