remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remote
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUERY
but still takes ages to run.
Thanks
Open query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks
|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>