SQL Server Performance Forum – Threads Archive

Join in distributed query

Hi all,
I have a problem using inner join in distributed query on two SQL servers 2000.
I have a table on local server containing list of variables VARS(ID, NAME, Date), where a date of last processed value for each variable is stored.
On remote server, there is a table containing values for these variables VALS (ID, Value and Date). There is approx. 6000 variables defined in VARS table and approx. 20 million values in VALS table. I need to run a query something like this: SELECT VARS.ID, VARS.Value, VARS.Date
FROM RemoteServer.Database.DBO.VALS INNER JOIN VARS ON VARS.ID = VALS.ID
WHERE
VALS.Date > VARS.DATE AND
VALS.DATE < GETDATE() and then process all values for each variable. When both tables are on local server, HASH join is used and the query takes something like 10 seconds.
When I use distributed query, SQL server "insists" on using LOOP for join, which takes more time, than I’m willing to wait.
I tried to use join hint to force the server to use HASH join, but in that case the join is processed on local server, which means this huge VALS table has to be transferred over the network each time I run the query.
Unfortunatelly, it is not allowed to use REMOTE HASH combination for JOIN hints. Does anyone see any solution please?
I really need to keep those two tables on separate servers and run the query as distributed.
Thanks a lot.
Tomas

---

This might be a little late, but… I haven’t worked much with distributed queries, but would it work to create the query as a view on the remote machine? The idea being that the query would be processed on the remote machine (and so only requiring the transfer of the small table) and the small result set returned back to the local machine. This will require setting up your local machine as a remote server on the remote box, which may or may not be allowed in your setup.
-Hartmut5

---

]]>