October 8, 2009 at 7:39 pm
Dear All Experts...
Now this is a silly problem, I may be missing out something, but.. I just cannot imagine..
I have a stored procedure as:
ALTER Procedure Proc1
@Variable1 NVARCHAR(MAX)
AS
BEGIN
SELECT ....
FROM DBO.TableA AS A
LEFT OUTER JOIN LinkedServer1.DB1.dbo.TableB AS B ON A.Col1 = B.Col1
LEFT OUTER JOIN LinkedServer1.DB1.dbo.TableC AS C ON A.Col1 = C.Col1
WHERE B.Col1 = @Variable
END
The above query gives expected results but takes 30 seconds.
If I pull that query out and run it in a seperate query window replacing @Variable1 with a value, it gives me results within 2 seconds
I don't understand this mechanism.. Can anyone help me out. I am in the middle of something and need to sort this out first.
Thanks.
Regards,
Nayan
October 9, 2009 at 7:28 am
There are a couple of issues. One is that by putting a filter on table B in the WHERE clause you essentially changing your LEFT OUTER JOIN to an INNER JOIN. Also I believe in this case ALL the data is being brought across from the linked server and then being filtered on the local server. You would probably be better off using OPENQUERY or OPENROWSET to bring only the rows you need back from the linked server and then JOIN to it. Then the processing will be done on the linked server.
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply