sql - OPENROWSET 查询相同服务器或不同服务器

Question

我尝试查询在其他服务器中组合 3 个表的结果。我的查询正在运行，但需要很长时间才能获得结果(2 分钟)。任何人都可以帮助我重组查询以使其快速。我不能为此使用链接服务器。以下是我的查询:

DECLARE @min_price decimal(38,4);
DECLARE @max_price decimal(38,4);
SET @min_price = 1.045;
SET @max_price = 13.855;

SELECT
  Image.filename,
  Sender.uploder_name,
  Receiver.company_name,
  Image.price_estimate,
  Image.size

FROM
  OPENROWSET('SQLNCLI', 'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password', 'SELECT * from dbName.dbo.image') Image
  INNER JOIN OPENROWSET('SQLNCLI', 'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password', 'SELECT * from dbName.dbo.sender') Sender
    ON Image.sender_username = Sender.username
  INNER JOIN OPENROWSET('SQLNCLI', 'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password', 'SELECT * from dbName.dbo.receiver') Receiver
    ON Image.receiver_username = Receiver.username

WHERE
  Receiver.min_price >= @min_price AND Receiver.max_price  <= @max_price

我怀疑使用 开放网络集 并且加入很少的表是导致这种缓慢的原因，因为我使用这种方法只调用1个表，查询结果与此相比相当快。请帮忙。

Answer 1

插入 Openrowset查询结果转化为temp表并使用 temp Join 中的表格

DECLARE @min_price DECIMAL(38, 4);
DECLARE @max_price DECIMAL(38, 4);

SET @min_price = 1.045;
SET @max_price = 13.855;

SELECT *
INTO   #Image
FROM   OPENROWSET('SQLNCLI',
                  'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password',
                  'SELECT *
                   FROM   dbName.dbo.image')

SELECT *
INTO   #Sender
FROM   OPENROWSET('SQLNCLI',
                  'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password',
                  'SELECT *
                   FROM   dbName.dbo.sender')

SELECT *
INTO   #Reciever
FROM   OPENROWSET('SQLNCLI',
                  'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password',
                  'SELECT *
                   FROM   dbName.dbo.receiver')
WHERE  min_price >= @min_price
       AND max_price <= @max_price 

或者如果所有 3 个表都来自同一台服务器，那么也试试这个

SELECT *
FROM   OPENROWSET('SQLNCLI',
                  'DRIVER={SQL Server};SERVER=ServerName;UID=userID;PWD=password',
                  'SELECT i.filename,
                           s.uploder_name,
                           r.company_name,
                           i.price_estimate,
                           i.size
                    FROM   Image i
                           INNER JOIN Sender s
                                   ON i.sender_username = s.username
                           INNER JOIN Receiver r
                                   ON i.receiver_username = r.username ')