Query that immobilizes the dev server

Please help figure out why query is making server "non-responsive" extreemly slow, have to reboot everytime I run this update stmt that a developer is running in Dev.


--
Facts: # of recs in tables
select * from themls_offices -- 93414
--
SELECT COUNT(*) -- 69684
from [devdb.helios.themls.com].[mls].[dbo].[all_carets_offices]  ACO

--
Is the update below checking/reading 6,557,849,628
(69684 X 70202 recs)??
I say this because it appears to be the case from the query and when run, it brings down the dev server (extreemly slow, non responsive)

update themls_offices
set CARETS_OfficeID = AgentOfficeID
from [devdb.helios.themls.com].[mls].[dbo].[all_carets_offices] as ACO
where firmCode = cast(ACO.MlsOfficeid as varchar(30))
--
--**I replaced above query by one below - is it equivalent? (FirmCode and CARETS_OfficeID  belong to themls_offices ...)
update themls_offices
set CARETS_OfficeID = aco.AgentOfficeID
from [devdb.helios.themls.com].[mls].[dbo].[all_carets_offices]  ACO
inner join themls_offices MO
on MO.firmCode = cast(ACO.MlsOfficeid as varchar(30))
--*
I expected  69684 recs to be update (see below)
However, the query is taking over 30 min to run. still running
(maybe becasue it is going accross servers - linked server ??)

SELECT COUNT(*) -- 69684
from [devdb.helios.themls.com].[mls].[dbo].[all_carets_offices]  ACO
inner join themls_offices MO
on MO.firmCode = cast(ACO.MlsOfficeid as varchar(30))


--*
dteshomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Appart from making sure you have the right indexes in place, I suggest you avoid JOINing a local table with a remote table else your performance will be highly network dependent.  Also avoid running JOINs conditions on CAST values as it creates a lot of overhead: instead , either create additional column in the correct data type.  

Finally, I suggest you replicate your remote table on you local and run it purely locally.  A wild guess but I think that is what is killing your performance, especially if you have poor network.  updating 69000 rows with right indexes in place should not take more than 1-2 minutes.

Please post the showplan so that we can help further...

Hope this helps...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
Besides all from the previous posting you ahould do an UPDATE STATISTICS on the tables and and always use WITH (NOLOCK) hint in all SELECTs / JOINs to avoid blocking. Do not use it if you need some sort of atomic transaction dealing with money but I can't see that hapening at least in dev environment. Also consider using WITH (ROWLOCK) or (UPDLOCK) fro the update statement.
dteshomeAuthor Commented:
Thank you both (Racimo and Lcohan).  I have been sidtracked with Prod issues; I will do what you sugegsted and report back .
Tx
Dan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.