Replacing Case statement in UPDATE

Question

Replacing Case statement in UPDATE

dhanasekar.palani

SSC Eights!

Points: 841
More actions
December 18, 2008 at 7:41 am

#197331

This table has millions of record. so when i am using a case statement in the update statement it takes time can this be written in a more optimized way.
UPDATE table
SET AUTO_REIN_OPT_OUT = CASE WHEN PRODUCT_CODE = '00' AND COVERAGE_CODE = '01' AND SS_CODE_VALUE = '00'
THEN AUTO_REIN_OPT_OUT
ELSE ''
END
FROM table

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you post the whole statement? It's unlikely that the CASE construct - as you've posted it - will cause much of a performance hit.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dhanasekar.palani SSC Eights! Points: 841 More actions · Answer 2

That is the whole query. You are right it hits the performance. can you give some idea to write in the condition instead as a case statement.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

dhanasekar.palani (12/18/2008)
That is the whole query. You are right it hits the performance. can you give some idea to write in the condition instead as a case statement.

Yep, move the condition to the WHERE clause so you're only affecting the rows you mean to, instead of all of them...

UPDATE table

SET AUTO_REIN_OPT_OUT = ''

WHERE NOT (PRODUCT_CODE = '00' AND COVERAGE_CODE = '01' AND SS_CODE_VALUE = '00')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

dhanasekar.palani (12/18/2008)
This table has millions of record. so when i am using a case statement in the update statement it takes time can this be written in a more optimized way.
UPDATE table
SET AUTO_REIN_OPT_OUT = CASE WHEN PRODUCT_CODE = '00' AND COVERAGE_CODE = '01' AND SS_CODE_VALUE = '00'
THEN AUTO_REIN_OPT_OUT
ELSE ''
END
FROM table

You have no where clause in the query. According to your post the table has millions of records. It seems to me that he query is slow because you are updating the whole table and not because of the case statement in the query.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dhanasekar.palani SSC Eights! Points: 841 More actions · Answer 5

dhanasekar.palani

SSC Eights!

Points: 841

December 18, 2008 at 8:14 am

#914580

Thanks Chris.