6

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?

Muthukumar
  • 8,679
  • 17
  • 61
  • 86
  • 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. – EastOfJupiter Sep 05 '12 at 16:00
  • I have no control over the link server. I cannot create an SP in that server. – Muthukumar Sep 05 '12 at 16:05
  • SELECT * FROM OPENQUERY(TREPO, 'SELECT ID, Name FROM Products') A INNER JOIN ORDERED_PRODUCTS B ON A.ID = B.ID try this – Sankara Sep 05 '12 at 16:11
  • @sankaras : The query you have mentioned is almost same as my approach. Will it increase performance.? – Muthukumar Sep 05 '12 at 16:18
  • 2
    @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 – Sankara Sep 05 '12 at 16:22

2 Answers2

19

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

EastOfJupiter
  • 781
  • 5
  • 11
  • 4
    @Muthukumar, did you notice an increase in performance? How about the result set? – EastOfJupiter Sep 05 '12 at 21:18
  • 1
    For me, using with OPENQUERY and without it took about the same amount of time. – eaglei22 May 22 '19 at 14:52
  • 2
    @eaglei22 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. – Tom Lint Nov 07 '19 at 13:26
  • @TomLint Ah okay, that makes sense. Thank you. – eaglei22 Nov 27 '19 at 14:13
0

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
)
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Wilfredo Colon
  • 1