Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in 11
> seconds. When I execute the same query against a linked server it takes 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance issue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating on
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:
> Brandon Lunt wrote:
> > I have a query that when executed against the host server, it completes in 11
> > seconds. When I execute the same query against a linked server it takes 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query plan
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>