troubleshooting Question

Linked Server Update query running very slow

Avatar of newoffice
newoffice asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
2 Comments1 Solution1333 ViewsLast Modified:
I have a 2005 sql server which is linked to 2000 sql server. I am trying to run an update query on the 2000 database. it does get updated but it takes a long time to run even one single row. I have defined a primary key on the destination table. I checked the collation too. I have the same problem as this post, only that the solutions there is not solving mine.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23301337.html?sfQueryTermInfo=1+join+link+server+updat+us

I had the same error. Now I am able to get rid of the error, but the query to update a single row is 4 minutes.

declare @tblSource table
(intapplicantid int primary key,
txtstatus varchar(200))

insert into @tblSource
select intapplicantid, txtstatus from tblapplicants
where intapplicantid = 124963

update dest
set dest.txtstatus = src.txtstatus
from @tblSource src
inner join
[mssql.new-hire1.com].[newhirever5].[newhirever5].[tblapplicants] dest
      on dest.intapplicantid = src.intapplicantid
collate SQL_Latin1_General_CP1_CI_AS

I am using temp table just to check the performance.

Is anything obvious in the above query? Please help!
ASKER CERTIFIED SOLUTION
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros