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%' 
  • 2
    Ah, no index in phone column, what did you expect? – ypercubeᵀᴹ Mar 24 '14 at 14:35
  • 1
    Yeah this is a table scan, no way around it if the column isn't indexed. – JNK Mar 24 '14 at 14:38
  • 1
    Yes, why do you think it would not? – Mark Sinkinson Mar 24 '14 at 14:53
  • 4
    You're also wasting time by setting phone = '' where phone = '' – Mark Sinkinson Mar 24 '14 at 14:54
  • 2
    Actually thanks to Mark Sinkinson for pointing out that the ='' is not needed. It was actually updating 17 million records in that part of the where clause. So if I take that out it returns very few records quickly. I think I will still add an index to the column, but removing that will make it much quicker. Thanks for all the suggestions. – user761786 Mar 25 '14 at 16:11

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.

| improve this answer | |

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.

| improve this answer | |
  • Except the question is about SQL Server, not Oracle. – Mark Sinkinson Mar 31 '14 at 8:06
  • Oh yes, missed the tag. Still, in my opinion indexes should be avoided. – ddaniel Mar 31 '14 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.