January 13, 2009 at 2:43 pm
I have a query that runs in a minute within the server but when i run the same query through link server from a different server it takes more than 10hrs. here is the query
Insert into Rev_Ref
select C.RevenueID, joinDate, MaxPay,MinPay,Gross, dept
from PA_REV.dbo.NewRev as C, SDBcohortBatchGA as CD
where C.RevenueID = CD.RevenueID
and C. joinDate between CD.startDate and CD.endDate
How would i troubleshoot this issue, is this something to do with SQL server or anything else.
January 13, 2009 at 2:59 pm
I have frequently seen situations where linked servers within joins (as you have here) do not perform the way that they "should". The optimizer (especially in SQL 2000) has a horrible time with them.
In some situations, I have run something like a
select * into #tempNewRev
from PA_REV.dbo.NewRev
--clustering keys are good
create clustered index pk_newrev on #tempNewRev(RevenueID, JoinDate) --depending on what you really need here
And then run
Insert into Rev_Ref
select C.RevenueID, joinDate, MaxPay,MinPay,Gross, dept
from #tempNewRev as C, SDBcohortBatchGA as CD
where C.RevenueID = CD.RevenueID
and C. joinDate between CD.startDate and CD.endDate
Frequently this runs much faster than the linked server within the join. It is probably worth a shot for you anyway, it has worked much better for me many times.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply