OPENROWSET query same server or different server

Question

I try to query a result which is combine 3 table in other server. My query is working but it take long to get the result (2 minutes). Can anyone help me to restructure the query to make it fast. I cannot use linked server for this. Below is my query:

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

I suspect using OPENROWSET and joining few table is the cause of this slow, because I using this method calling 1 table only, the query result is pretty fast compare to this. Please help.

Answer 1

Insert the Openrowset query results into temp tables and use the temp tables in 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 

or if all the 3 tables are from same server then try this as well

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 ')