Update statement takes too long

Question

Update statement takes too long

mohan.mariyappa

SSC Eights!

Points: 856
More actions
August 31, 2008 at 10:38 pm

#197717

I have below table with 10594358 records. When I tried to execute below update statment, it is executing for 9 mins and I stoped it. I have index on Mailing_Address_confidence_score, Mailing_DPID and Sourced_Addresses files seperatly. I appreciate if anyone help on this.
Updatetbl_TRU_Prospect_TEST
SetMailing_Address_confidence_score = Case
When Mailing_DPID Is Not Null
Then 1When Sourced_Addresses = 'MRD' And Mailing_DPID Is Null
Then 2
Else 3
End
I tried to write as a seperate statement, instead of case statements. Still performance is very low.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

The problem is that you've reached the undefined but omnipotent "tipping point". Try updating just a million rows and see how long (short) it takes. You'll need to write a loop to update just a million or two rows at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

mohan.mariyappa (8/31/2008)
I have below table with 10594358 records. When I tried to execute below update statment, it is executing for 9 mins and I stoped it. I have index on Mailing_Address_confidence_score, Mailing_DPID and Sourced_Addresses files seperatly. I appreciate if anyone help on this.
Updatetbl_TRU_Prospect_TEST
SetMailing_Address_confidence_score = Case
When Mailing_DPID Is Not Null
Then 1When Sourced_Addresses = 'MRD' And Mailing_DPID Is Null
Then 2
Else 3
End
I tried to write as a seperate statement, instead of case statements. Still performance is very low.

1) I see no WHERE clause on your statement, thus you are updating all 10.6M rows. I can easily see this taking > 9mins on poor hardware, esp if you have many indexes or if the update causes many page splits.

2) did you watch for blocking during this update? try sp_who2 active while the update is running.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service