April 3, 2008 at 12:58 pm
I have a query from my main server to a remote server in our NY office. Both queries are pretty simple, but the time difference is amazing. I'm trying to figure out what should I be looking at next to figure out why it is performing so radically different.
Sql Information:
local sql server 2000 8.00.760
remote sql server 2000 8.00.760
Query1
select ID from RemoteSvr.TestDb.dbo.TestTbl where ID = 'TEST'
Query2
select ID from RemoteSvr.TestDb.dbo.TestTbl where Status = 2
ID = varchar(25)
Status = smallint
Est Execution Plan query1 query2
cpu cost 348.6106567 0.010333
i/o cost 0 0
executions 1 1
rows 1045802 1
operator cost 348.6106567(100%) 0.010333(100%)
row size 23b 24b
subtree cost 348.6106567 0.010333
operation Remote Query Remote Query
id 2 4
output list Expr1002 Expr1003
parallel False False
physical operation Remote Query Remote Query
(sorry I couldn't format it better above)
I have a nonclustered covering index on Status.
I have a nonclustered index on ID
I have a clustered, unique, primary key on ID
My biggest concern of course is the first query of course. You'll notice that it returns an estimate row count of 1045802. This is returning the whole table!!! Well close anyways. The actual table count is 1045888.
Thanks in advance!
April 10, 2008 at 2:39 pm
this is pretty well known - remote queries can do some horrible things - Alan Mitchell wrote an excellent paper on this, he had the same problem. Can't remember the solution other than don't. It's in the way you write the query - I always prefer rpc calls when i can.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 10, 2008 at 2:54 pm
Now, you may not be able to do this, but I have found that writing the queries on the linked server as a stored procedure and calling the procedure has drastically reduced time. I mean from minutes to seconds. I think that is what Colin means when he says he prefers RPC calls as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 11, 2008 at 11:01 am
Thanks for the reply. Yeah, i had to resort to a RPC call using openquery. The link server then used the where statement and passed back the appropriate records. I gotta think this is a sql bug, but what do i know.
What system doesn't do remote calls for data anymore or is it just me? :w00t:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply