February 2, 2009 at 2:54 pm
select M.*
from DEV1.StatePA.dbo.Revenue as M , DEV1.Budget.dbo.PayPa as PP
where M.RevID = PP.RevID collate Latin1_General_CI_AI
and M.servppate between PP.startDate and PP.endDate
when i run the above query from Dev1 Server directly it takes 5 min to pull 1 million records but when i run the same query cross over through Link Server it never gets any result set. I tried selecting just top 10 but still it doesnt shoe up any result.
where should i troubleshoot?
February 2, 2009 at 3:01 pm
Not sure, but it might be trying to pull all the data from each table over to the server you are running the query from before doing the join.
FYI, I'd rewrite the query with the join criteria in on ON clause in the FROM clause instead of the WHERE clause as it is right now.
February 2, 2009 at 5:56 pm
Lynn
Do u mind to put your code here as you said.
February 2, 2009 at 6:05 pm
Mike Levan (2/2/2009)
LynnDo u mind to put your code here as you said.
What code? My last comment was based solely on observation.
Your code:
select M.*
from DEV1.StatePA.dbo.Revenue as M , DEV1.Budget.dbo.PayPa as PP
where M.RevID = PP.RevID collate Latin1_General_CI_AI
and M.servppate between PP.startDate and PP.endDate
My rewrite:
select
M.* -- really should list the columns, not use *
from
DEV1.StatePA.dbo.Revenue as M
inner join DEV1.Budget.dbo.PatPa as PP
on (M.RevID = PP.RevID collate Latin1_General_CI_AI
and M.servppate between PP.startDate and PP.endDate)
February 2, 2009 at 6:08 pm
The change in formating the code may not affect performance. You may want to consider using a view on the linked server and see if that improves performance.
February 2, 2009 at 6:13 pm
Lynn
I tried with join as you said. If i run within the same server i see some gud performance than earlier but when i run cross over server still its the same. I am not getting any result set, the query is running for ever without giving any result even just for 10 records.
thanks
February 2, 2009 at 6:28 pm
As I said earlier, don't be surprised if it is pulling the entire data set over to your server and then performing the join locally on the server with the linked server defined. That's why I suggested you might want to create a view and query the view over the linked server.
February 2, 2009 at 6:31 pm
Lynn
In the query above Revenue is view actually.
Could you tell how view will improve the performance.
February 2, 2009 at 6:37 pm
It would be, my hope, that the join between the two "tables" would occur on the linked server instead of the linking server.
February 2, 2009 at 6:45 pm
Couple of things:
1.) Do some research:
a. Can you ping the servers to check if they're alive.
b. Check the resources available on the linked server, maybe you're overburdening it\them
c. How many rows are you joining on and can you limit the number of rows further in the WHERE caluse.
d. When bringing across huge datasets, Tempdb also has to grow, what does that look like and is it not perhaps in need of some maintenance.
e. What do the indexes look like, for example, are there any.
4.) What I've seen peeps do in the past is to insert the first record into a temp table, then populate the temp table and the select from the temp table.
5.) Order a pizza and watch re-runs of the superbowl (don't quote me on that - doh! I did myself).
It's difficult to performance tune queries and servers from afar, if you grant us access, we may be able to sort out your REVENUE problems...:D
Look at some of the other great articles on performance tuning:
http://www.sqlservercentral.com/articles/Linked+Servers/62246/
http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/
http://www.sqlservercentral.com/articles/Performance+Tuning/2764/
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/2648/
Max
February 3, 2009 at 6:07 am
With linked servers I almost always do one of two things. Put the entire load on the linked server, meaning, perform all the joins, etc. on that machine and get the result set. If that's not possible, then I use as much filtering as I possibly can to reduce the data that comes across, as was mentioned before, load this into a temp table where you can get an index and statistics if needed. Then join it to your local data. As Lynn has already pointed out, several times, trying to do the whole thing in one query is likely to be an issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply