How To Speed Up Update Query On Massive Table

I am currently in the process of transferring one of our existing databases onto a new ontology. The database follows a star schema with observation_fact being the center of the st

Solution 1:

Instead of updating the table you should try to create a new table with the logic you have in the SQL, it will be faster. After the new table created you can rename the old table and rename the new table as observation_fact

To reiterate 1. Create new table

insertinto observation_fact_new 
select ...
from observation_fact

2. Rename old table do the sanity checks

alter table observation_fact rename to observation_fact_old

3. Rename new table as observation_fact

alter table observation_fact_new rename to observation_fact

After your checks and tests are done drop the old table

drop table observation_fact_old

Solution 2:

First, updating all rows in the table is going to take time. Sometimes, it is faster to create a new table with all the modified data, truncate the original table, and re-load it.

Second, you are referencing observation_fact twice, but that doesn't seem necessary. I think this does what you want:

update observation_fact ofact
    set concept_cd = (casewhensplit_part(ofact.concept_cd, ':', 1) = 'ICD10-CM'then replace(ofact.concept_cd, 'ICD10-CM:', 'ICD10CM:')
                           whensplit_part(ofact.concept_cd, ':', 1) = 'ICD10-PCS'then replace(ofact.concept_cd, 'ICD10-PCS:', 'ICD10PCS:')
                           whensplit_part(ofact.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Diagnoses\\%'then replace(ofact.concept_cd, 'ICD9:', 'ICD9CM:')
                           whensplit_part(ofact.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Procedures\\%'then replace(ofact.concept_cd, 'ICD9:', 'ICD9PROC:')
                      end) as cd
from concept_dimension_bak cdim
where ofact.concept_cd = cdim.concept_cd;

You may need to set the unmatched values to NULL.