Distributed queries

From: KH (KH_at_discussions.microsoft.com)
Date: 12/01/04 

Date: Wed, 1 Dec 2004 10:09:06 -0800

As always the answer to these Q's will depend on a number of factors, but in
general...

- For a distributed query, is a plan cached on the local machine? The remote
machine? Both or neither? My guess is that it depends on whether the query
references only the remote server(s) or both local and remote.

Examples - If executed on "LocalServer", where is the query plan saved (if
at all)?

-- 1 :: Reference remote server only
SELECT x FROM RemoteServer.SomeDB.dbo.SomeTable WHERE x > @var

-- 2 :: Join local and remote tables
SELECT a.whatever
FROM dbo.SomeTable AS a
JOIN RemoteServer.SomeDB.dbo.SomeTable AS b ON a.whatever = b.whatever
WHERE b.whatever > @var

- In having to reconcile data between multiple servers my experience has
been that running a simple remote query and saving the data in a temp table
(or other method) and working with it locally has better performance than
joining across the network.

My guess is that with a join between tables on networked servers, SQL Server
copies data to the local machine then goes about its business. That's fine
for a single query but if you need to use the same data multiple times I'm
guessing doing only one remote query is better?

Anyone care to elaborate?

Thanks -- Ken

Relevant Pages

  • Distributed queries
    ... My guess is that it depends on whether the query ... references only the remote serveror both local and remote. ... My guess is that with a join between tables on networked servers, ... copies data to the local machine then goes about its business. ...
    (microsoft.public.sqlserver.programming)
  • Re: Query In BE database
    ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ... queries and that is remote queries. ...
    (microsoft.public.access.queries)
  • Re: Strange perf.
    ... HASH JOIN 72 6 K 132 ... REMOTE 30 K 2 M 72 CBDSRV_SRVCNS.WORLD SERIAL ... In the quick query the join is a hash join, ... will be executing about 30,000 queries ...
    (comp.databases.oracle.server)
  • Re: Strange perf.
    ... HASH JOIN 72 6 K 132 ... REMOTE 30 K 2 M 72 CBDSRV_SRVCNS.WORLD SERIAL ... In the quick query the join is a hash join, ... will be executing about 30,000 queries ...
    (comp.databases.oracle.server)
  • Re: Query In BE database
    ... In the table is a list of all the queries that are available to be selected. ... When selected from the drop-down list box, I what the the query that is being ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
Loading