Hey All,
Very simple one...how do I simply change the default from 20 seconds to 60 in the sp_config table...
Thanks,
DJLook up sp_configure in Books Online and you will be well on your way to answering your question.|||The bible? Read it. i'll go back and look. Is it as easy as this?
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'remote login timeout', 60;
GO
RECONFIGURE;
GO
Showing posts with label seconds. Show all posts
Showing posts with label seconds. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
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?
>> >>
>> >>
>> >>
>>
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?
>> >>
>> >>
>> >>
>>
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
>
>
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
>
>
Subscribe to:
Posts (Atom)