We help IT Professionals succeed at work.

UPDATE statement in SQL SERVER 2008 is taking forever

answertime
answertime asked
on
I have a table called addresses with 16,200 rows of data. I have to update the phone and mobile fields to have a "+1" in front of their phone number when it is not null and the when the country is United States or Canada.  Simple update statements run well over a minute before I am stopping them:

UPDATE addresses
SET phone = '+1' + phone
WHERE
      phone IS NOT NULL
      AND country  IN ('United States', 'Canada')

I tried doing a while loop and this runs a long time as well:

DECLARE
@addressid INT
,@count INT

SET @addressid = 1
SET @count = (SELECT MAX(address_id) FROM addresses)

WHILE @addressid <= @count BEGIN

                  UPDATE addresses
                  SET phone = '+1' + phone
                  WHERE
                        address_id = @addressid
                        AND RTRIM(country) = 'United States'
                        AND phone IS NOT NULL

      SELECT @addressid = @addressid + 1

END


What is a quick and efficient way to do this?????

Comment
Watch Question

Your first update statement is the correct way to do it.  The only thing that might speed things up is if you add an index on the country column.  One-time queries that run over a minute (or several minutes) is not uncommon.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Yeah, UPDATE statement looks OK.

The only things that should normally come remotely close to making this take that long are insufficient log space or page splits for all/most of the UPDATEs.  As to splits, it seems unlikely that even at 100% fill factor, there's not enough space left in a page to add 2 bytes to each row.

So, make sure the log is *pre-allocated* with enough freespace to handle the whole UPDATE, and that the autogrowth on the log is not small and that it is NOT in a percentage but in fixed megabytes.

If the log is not large enough already, you can increase it thru SSMS or via an ALTER DATABASE command.

What does this command show as size and % free for the db you are running the UPDATE on?

DBCC SQLPERF ( LOGSPACE )

Author

Commented:
ScottPletcher:
This is what it shows:
Log Size(MB)     Log used %      Status
1642.93           1.675414                0
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
OK, you've got *LOTS* of free log space, so that's definitely not the problem.

Have you verified that there are no physical issues with the drive on which the db resides?

That seems pretty slow for UPDATEing only 16K rows.

Have you let the command finish and see what the total time is?

Maybe at 1 min it was almost done :-) .