使用openquery时如何加入链接服务器表和sql server表 [英] How to join linked server table and sql server table while using openquery

问题描述

我使用 openquery 语法从链接服务器读取数据.

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

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

我想将这个链接服务器表与一个 Sql 服务器表连接起来以获得我的最终结果.现在我通过临时表来完成.

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?

推荐答案

我没有能力对此进行测试，但它确实提供了通过直接加入来绕过 #tempTable 选项的机会到远程服务器(如果可能的话)

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

这里是链接服务器查询的一些信息的链接，以及可能遇到的一些陷阱:http://sqlblog.com/blogs/linchi_shea/archive/2009/11/06/bad-database-practices-abusing-linked-servers.aspx

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

这篇关于使用openquery时如何加入链接服务器表和sql server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！