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?

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