3

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%' 
            )
13
  • 2
    Ah, no index in phone column, what did you expect? Mar 24, 2014 at 14:35
  • 1
    Yeah this is a table scan, no way around it if the column isn't indexed.
    – JNK
    Mar 24, 2014 at 14:38
  • 1
    Yes, why do you think it would not? Mar 24, 2014 at 14:53
  • 4
    You're also wasting time by setting phone = '' where phone = '' Mar 24, 2014 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. Mar 25, 2014 at 16:11

2 Answers 2

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.

-1

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.

2
  • Except the question is about SQL Server, not Oracle. Mar 31, 2014 at 8:06
  • Oh yes, missed the tag. Still, in my opinion indexes should be avoided.
    – ddaniel
    Mar 31, 2014 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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