Enhance performance of update query

Question

I have a query that is running against a table with 50 million rows. The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values. We are seeing poor performance of this query and was wondering if anyone has any suggestions on improving performance. No index currently exists with the phone number.

UPDATE Table SET phone='' WHERE ( phone IS NULL
              OR phone = ''
              OR phone = '0000000000' 
              OR phone = '0' 
              OR phone like '111%'
              OR phone like '222%'
              OR phone like '333%'
              OR phone like '444%'
              OR phone like '555%'
              OR phone like '666%'
              OR phone like '777%'
              OR phone like '888%'
              OR phone like '999%'
              OR phone like '800%'
              OR phone like '900%' 
            )

Answer 1

It depends on what you mean by improve performance. You can't make that query faster, it needs to scan the table. But you can keep it from using too many resources and blocking other processes by chunking it, which is essentially iterating through batches of rows to perform multiple smaller updates.

There must be some option to just write it to the target table instead of updating in place.

You could also forego the scrub altogether and use a non-persisted computed column in the target database to reformat the phone number if you don't need to index it.

Answer 2

Usually when performing DML operations, especially ones that affect a high percentage of rows, it is better to disable the indexes, and rebuild them afterwards. This is because most of them time, the engine is trying to reorganize the index after each DML operation. For INSERT and DELETE, it is obvious that a clustered index will be rebuilt after each row. With UPDATEs you might cause page splits, which again, will force an index rebuild.

Back to your issue, I don't think an index will solve the problem, as long as your condition is set on the updated column, so in the end, all your rows must be tested. In fact, it is better to work directly on the table rather than making roundtrips to the index.

Is there a way to do updating as a nightly job? I'm afraid to you might be limited by the hardware resources. If you are sure that that's not the case, please give me some more details.