This question is so silly, that I must hang my head in shame and admit it's
a novice question.
Frequently on our DB we need to run large updates on a table, typically
involving over 100K rows. These nearly ALWAYS involve values from other
tables. But the updates take so long that we think the process has stalled
on some sort of deadly-embrace locking scenario and we cancel it.
Is there anything wrong with this UPDATE statement?
update mdx_core.provider
set
middle_name = fix.middle_name,
display_name = fix.display_name
from mdx_import._fix_mnames as fix
where provider.provider_id = fix.provider_id;
Even though _fix_mnames has an index on provider_id, it took so long that I
cancelled it and rewrote it, replacing it with this absolutely silly-looking
statement:
update mdx_core.provider
set
middle_name = (
select middle_name
from mdx_import._fix_mnames as fix
where fix.provider_id = provider.provider_id),
display_name = (
select display_name
from mdx_import._fix_mnames as fix
where fix.provider_id = provider.provider_id)
where provider.provider_id in (
select provider_id from mdx_import._fix_mnames)
This worked, after running for 25 minutes on 276K rows. TWENTY FIVE MINUTES?
The performance of our server on other operations are great, but this can't
be normal for an enterprise-scale SQL server, can it? What am I doing wrong?
TIA
Carlo
