|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > Linked Server Performance Issue
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Linked Server Performance Issue
|
|
| Steve Jones 2002-08-14, 11:23 am |
| Someone brought me an interesting performance issue.
I have these two queries:
SELECT *
FROM
OPENQUERY(csc,
"SELECT * FROM csc_local..audit where username = 'xx'")
and
select * from csc.csc_local.dbo.audit where username = 'xx'
The first runs in about 2-3 seconds consistently. The second takes 25-30+
consistently and dramatically increases the CPU load. CPU Q Length is
consistently near 2, so performance on all queries is an issue.
Anyone else seen this? Anyone noticed a performance difference in these
types of queries or have an explanation?
I did check the query plans and it appears the openquery submits the query
to the remote server (SQL in this case) and gets back the result set which
is passed to the client. The linked server query gets everything from the
remote server and then applies the filter (where clause). However, this
seems like a large difference for this query.
Server, SQL 7, SP2
linked server SQL 2000, SP2.
| |
| Russell Fields 2002-08-14, 12:23 pm |
| Steve,
If you run SQL Profiler on this to see what is going on I believe that you
will find that the slow query is opening a cursor and bringing back groups
of rows. You can double check this.
In that case, you are actually having several roundtrips between the
servers, rather than just one roundtrip. That dramatically expands the time
needed.
Russell Fields
"Steve Jones" <mspublic@dkranch.net> wrote in message
news:3D5A8E06.3010903@dkranch.net...
> Someone brought me an interesting performance issue.
>
> I have these two queries:
>
> SELECT *
> FROM
> OPENQUERY(csc,
> "SELECT * FROM csc_local..audit where username = 'xx'")
>
> and
>
> select * from csc.csc_local.dbo.audit where username = 'xx'
>
> The first runs in about 2-3 seconds consistently. The second takes 25-30+
> consistently and dramatically increases the CPU load. CPU Q Length is
> consistently near 2, so performance on all queries is an issue.
>
> Anyone else seen this? Anyone noticed a performance difference in these
> types of queries or have an explanation?
>
> I did check the query plans and it appears the openquery submits the query
> to the remote server (SQL in this case) and gets back the result set which
> is passed to the client. The linked server query gets everything from the
> remote server and then applies the filter (where clause). However, this
> seems like a large difference for this query.
>
> Server, SQL 7, SP2
> linked server SQL 2000, SP2.
>
>
|
|
|
|
|