Namely, when querying against a linked server in a join situation the ENTIRE table from the linked server will likely be downloaded to the server the query is executing from in order to do the join operation. In the OP's case, both table1 from DB1 and table1 from DB2 will be transferred in their entirety to the server executing the query, presumably named DB3.
If you have large tables, this may result in an operation that takes a long time to execute. After all it is now constrained by network traffic speeds which is orders of magnitude slower than memory or even disk transfer speeds.
If possible, perform a single query against the remote server, without joining to a local table, to pull the data you need into a temp table. Then query off of that.
如果可能的话，对远程服务器执行单个查询，而不加入本地表，以便将所需的数据拉到临时表中。然后查询。
It that's not possible then you need to look at the various things that would cause SQL server to have to load the entire table locally. For example using GETDATE() or even certain joins. Others performance killers include not giving appropriate rights.
select foo.id
from databaseserver1.db1.dbo.table1 foo
inner join databaseserver2.db1.dbo.table1 bar
on foo.name = bar.name
#4
8
If you still find issue with ...
如果你还觉得……
Enclose server name in []
在[]中包含服务器名
#5
5
For those having trouble with these other answers , try OPENQUERY
对于那些对其他答案有困难的人，请尝试OPENQUERY
Example:
例子:
SELECT * FROM OPENQUERY([LinkedServer], 'select * from [DBName].[schema].[tablename]')
#6
3
select * from [Server].[database].[schema].[tablename]
This is the correct way to call. Be sure to verify that the servers are linked before executing the query!
这是正确的呼叫方式。在执行查询之前，一定要验证服务器是否链接!
To check for linked servers call:
检查连接的服务器调用:
EXEC sys.sp_linkedservers
#7
2
Usually direct queries should not be used in case of linked server because it heavily use temp database of SQL server. At first step data is retrieved into temp DB then filtering occur. There are many threads about this. It is better to use open OPENQUERY because it passes SQL to the source linked server and then it return filtered results e.g.
京公网安备 11010802041100号