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?First, why are you using linked servers. In most cases this is not necessary
with RS. You can have multiple datasets against multiple data sources.
SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
I learned this while working with loading a datamart. If you must use linked
servers then you need to be using Openquery to use it. Do not use the 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.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?|||The report I am producing combines data from 3 different servers. I
typically use linked servers so I don't have to put usernames and passwords
into my query strings (not all users have access to all databases so I have
to use a different account). I have usually had good results with Linked
servers (slight performance hit but never this bad). The other linked query
(to the 3rd server) runs in about 2 seconds and returns approximately the
same number or results.
My confusion I guess is why does it not run the same as a query analyzer
client connecting to the server?
"Bruce L-C [MVP]" wrote:
> First, why are you using linked servers. In most cases this is not necessary
> with RS. You can have multiple datasets against multiple data sources.
> SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
> I learned this while working with loading a datamart. If you must use linked
> servers then you need to be using Openquery to use it. Do not use the 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.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?
>
>|||Are you doing this in a stored procedure?
If not, I have seen people post about issues where the query plan created
when a query is executed from RS is different than from query analyzer. As I
said, I haven't seen this but I have seen posts about that.
If you are using a stored procedure then that would not be an issue. If not,
try moving this to a stored procedure and see if that helps.
I have thought of one other thing. Depending on the parameterization, I have
seen in 2000 based on the parameters and the where clause where SQL Server
will decide to bring over all the data from the remote table and process it
locally on the server rather than having the query executed remotely and
bringing back the result. Based on 11 seconds when hitting the server
directly going to 100 seconds, I bet that is what is happening. If you use
openquery this will not occur. With 4 part naming you have to really be
careful.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> The report I am producing combines data from 3 different servers. I
> typically use linked servers so I don't have to put usernames and
> passwords
> into my query strings (not all users have access to all databases so I
> have
> to use a different account). I have usually had good results with Linked
> servers (slight performance hit but never this bad). The other linked
> query
> (to the 3rd server) runs in about 2 seconds and returns approximately the
> same number or results.
> My confusion I guess is why does it not run the same as a query analyzer
> client connecting to the server?
> "Bruce L-C [MVP]" wrote:
>> First, why are you using linked servers. In most cases this is not
>> necessary
>> with RS. You can have multiple datasets against multiple data sources.
>> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> better.
>> I learned this while working with loading a datamart. If you must use
>> linked
>> servers then you need to be using Openquery to use it. Do not use the 4
>> part
>> naming.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.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?
>>|||Currently this is in query form. I tried the openquery and got the same
results as the 4 part linked query. I also tried the openrowset and the time
came down to 57 seconds, but nowhere near the 10-15 seconds I would expect.
Also, the problem with openquery is that I need to pass parameters used by
the query. I didn't see a way to get those dynamically into the query.
I went ahead and created the sp and executed that against the linked server
and it ran in 50 seconds. Better, but still not what I was expecting.
"Bruce L-C [MVP]" wrote:
> Are you doing this in a stored procedure?
> If not, I have seen people post about issues where the query plan created
> when a query is executed from RS is different than from query analyzer. As I
> said, I haven't seen this but I have seen posts about that.
> If you are using a stored procedure then that would not be an issue. If not,
> try moving this to a stored procedure and see if that helps.
> I have thought of one other thing. Depending on the parameterization, I have
> seen in 2000 based on the parameters and the where clause where SQL Server
> will decide to bring over all the data from the remote table and process it
> locally on the server rather than having the query executed remotely and
> bringing back the result. Based on 11 seconds when hitting the server
> directly going to 100 seconds, I bet that is what is happening. If you use
> openquery this will not occur. With 4 part naming you have to really be
> careful.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> > The report I am producing combines data from 3 different servers. I
> > typically use linked servers so I don't have to put usernames and
> > passwords
> > into my query strings (not all users have access to all databases so I
> > have
> > to use a different account). I have usually had good results with Linked
> > servers (slight performance hit but never this bad). The other linked
> > query
> > (to the 3rd server) runs in about 2 seconds and returns approximately the
> > same number or results.
> >
> > My confusion I guess is why does it not run the same as a query analyzer
> > client connecting to the server?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> First, why are you using linked servers. In most cases this is not
> >> necessary
> >> with RS. You can have multiple datasets against multiple data sources.
> >>
> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
> >> better.
> >> I learned this while working with loading a datamart. If you must use
> >> linked
> >> servers then you need to be using Openquery to use it. Do not use the 4
> >> part
> >> naming.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.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?
> >>
> >>
> >>
>
>|||The only way to get a parameter to openquery (that I know of) is to
dynamically create the sql string. You end up having to do lots of messing
with single quotes.
In your stored procedure, are you able to insert into a temp table the
results from each individual query and then join the temp tables?
Also, just so you can see how it is done, here is a an example of
dynamically
select @.SQL = 'insert ' + @.TABLENAME + ' select * from
openquery(linkedservername,''' + 'SELECT * from ' + @.TABLENAME + ' where ' +
@.SYNCDATEFIELD + '> '' + convert(varchar(30),@.STARTDATE,9) + '' and '
+ @.SYNCDATEFIELD +' < '' + convert(varchar(30),@.ENDDATE,9) + '')'
execute (@.SQL)
Note all the single quotes mess. Not too friendly but it is the fastest way
to work with linked tables. In the above I am inserting into a real table
but you could easily have a temp table created that you insert into.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:EDE9A4A4-0992-4B9B-ACA3-2E6A0FB1645B@.microsoft.com...
> Currently this is in query form. I tried the openquery and got the same
> results as the 4 part linked query. I also tried the openrowset and the
> time
> came down to 57 seconds, but nowhere near the 10-15 seconds I would
> expect.
> Also, the problem with openquery is that I need to pass parameters used by
> the query. I didn't see a way to get those dynamically into the query.
> I went ahead and created the sp and executed that against the linked
> server
> and it ran in 50 seconds. Better, but still not what I was expecting.
> "Bruce L-C [MVP]" wrote:
>> Are you doing this in a stored procedure?
>> If not, I have seen people post about issues where the query plan created
>> when a query is executed from RS is different than from query analyzer.
>> As I
>> said, I haven't seen this but I have seen posts about that.
>> If you are using a stored procedure then that would not be an issue. If
>> not,
>> try moving this to a stored procedure and see if that helps.
>> I have thought of one other thing. Depending on the parameterization, I
>> have
>> seen in 2000 based on the parameters and the where clause where SQL
>> Server
>> will decide to bring over all the data from the remote table and process
>> it
>> locally on the server rather than having the query executed remotely and
>> bringing back the result. Based on 11 seconds when hitting the server
>> directly going to 100 seconds, I bet that is what is happening. If you
>> use
>> openquery this will not occur. With 4 part naming you have to really be
>> careful.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
>> > The report I am producing combines data from 3 different servers. I
>> > typically use linked servers so I don't have to put usernames and
>> > passwords
>> > into my query strings (not all users have access to all databases so I
>> > have
>> > to use a different account). I have usually had good results with
>> > Linked
>> > servers (slight performance hit but never this bad). The other linked
>> > query
>> > (to the 3rd server) runs in about 2 seconds and returns approximately
>> > the
>> > same number or results.
>> >
>> > My confusion I guess is why does it not run the same as a query
>> > analyzer
>> > client connecting to the server?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> First, why are you using linked servers. In most cases this is not
>> >> necessary
>> >> with RS. You can have multiple datasets against multiple data sources.
>> >>
>> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> >> better.
>> >> I learned this while working with loading a datamart. If you must use
>> >> linked
>> >> servers then you need to be using Openquery to use it. Do not use the
>> >> 4
>> >> part
>> >> naming.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.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?
>> >>
>> >>
>> >>
>>