Optimimize a update statement(Performance Issue)

[Mohit Agarwal]

dear all,

here in sql server, i found that an update statement takes much more time (30Min.) i want to optimize ..can someone help me ....the query is below.....

update SSS_RPT_ENTITY_COLL set --CONDITION CHANGED ON 08 MAR10
Final_elig_flag = case when (ISNULL(Asset_basel_elig_flag,'')= '' or Asset_basel_elig_flag='') then ''
when Asset_basel_elig_flag='Y' and Entity_basel_elig_flag='Y' and AssetEntity_Basel_Elig_Flag='Y' Then 'Y'
when Asset_basel_elig_flag='Y' and (ISNULL(Entity_basel_elig_flag,'Y')='Y' OR CASE WHEN Entity_basel_elig_flag='' THEN 'Y' END ='Y' ) and (ISNULL(AssetEntity_Basel_Elig_Flag,'Y')='Y' or CASE WHEN AssetEntity_Basel_Elig_Flag='' THEN 'Y' END='Y') Then 'Y'
when Asset_basel_elig_flag='Y' and (ISNULL(Entity_basel_elig_flag,'Y')='Y' OR CASE WHEN Entity_basel_elig_flag='' THEN 'Y' END ='Y' ) and AssetEntity_Basel_Elig_Flag='N' Then 'N'
when Asset_basel_elig_flag='Y' and Entity_basel_elig_flag='N' and (ISNULL(AssetEntity_Basel_Elig_Flag,'Y')='Y' or CASE WHEN AssetEntity_Basel_Elig_Flag='' THEN 'Y' END ='Y') Then 'N' end



Thanks

[rmiao]

Look at execution plan first to find out if optimizer uses proper index.

[rhettss]

Agree with above: reference proper indexes, possibly defining a sub-population.
t-sql also offers 10 levels of nesting in CASE statements. I've noticed a minor amount of duplicate logic in your case streams. (This may be a minor point, considering the size and organization of your data.)