August 13, 2002 at 2:29 pm
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. 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.
Server, SQL 7, SP2
linked server SQL 2000, SP2.
Steve Jones
August 13, 2002 at 3:00 pm
Update:
Nothing between the servers except a switch. They may be on separate VLans, so it's possible it is multiple switches.
However, I can run these queries together in one batch, with some getdate()s and the times are consistent. Doesn't matter which order I run them in.
The showplans:
SELECT *
FROM
OPENQUERY(csc,
'SELECT * FROM csc..audit where username = ''xx''')
|--Remote Scan(SELECT * FROM csc..audit where username = 'xx')
select * from csc.csc.dbo.audit where username = 'xx'
|--Filter(WHERE:([csc].[csc].[dbo].[audit].[username]='xx'))
|--Remote Query(SELECT Remote1001."record_id" Col1002,Remote1001."object_type" Col1003,...
Steve Jones
August 13, 2002 at 5:04 pm
There was an article about this a while back, I think it's on the SQL part of microsoft.com as a white paper. Basically, because you're offloading the processing of the query in the first example, then the load on your local server is virtually nil; the second example is almost working like an ISAM database, the local server is monitoring the connection to the remote until it gets an answer. I believe the gist of the white paper said that it is better to execute the query where the majority of the data resides - this was in reference in particular to where an OpenQuery() was used in a JOIN. I also think you can only do this with an OLE DB compliance datasource?
Sorry to be vague, but it's a classic case of having read it somewhere...but where?
August 13, 2002 at 5:04 pm
Interesting. Hope you'll dig some more, I use linked servers a lot, just so much easier to admin than openquery, but in some cases it might be worth it.
Andy
August 13, 2002 at 9:33 pm
Some notes from SQL-Server-Performance.com:
http://www.sql-server-performance.com/linked_server.asp
Paragraphs 2 and 3 talk about using OPENQUERY.
K. Brian Kelley
http://www.truthsolutions.com/
K. Brian Kelley
@kbriankelley
August 23, 2002 at 10:00 am
Thanks. I suspected as much. We've seen some other similar issues since then.
The linked server is an AS/400 with millions of rows, so I'm not surprised the linked server is an issue. The developer worked around with dynamic sql to run through OpenQuery.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply