sql - Improve performance of cascading update statements? -

i'm trying populate table i've created fundamental data present in different table.

i have number of ids present on join, number of reasons, no single id work records. such, i've set series of cascading update statements: inner join first using id1 fundamental table , populate table records present; inner join next using id2 fundamental table records not populated, again populating table records match; etc.

this allows me match pretty close of records present in initial table (~99.5% have match after cascading updates, @ 78% have match single id), great. problem need populate number of different fields in table , processing gets sluggish repeat procedure each data item.

1. is there better way achieve objective using cascading updates? perhaps akin coalesce function i'm not aware of (coalesce won't work, iterative in nature)?

2. if not, within query below, there obvious way speed up?

   update #temp    set avg_divyld_5yr = d.value_ / avgprice       #temp b       join qai.dbo.secmstr s on s.cusip = substring(b.cusip,1,8)        --on s.sedol = substring(b.sedolcode,1,6)                             , b.avg_divyld_5yr null       join qai.dbo.secmap m on m.seccode = s.seccode                             , m.ventype = 7                            , ((m.exchange = 0 , m.rank = 1) or m.exchange = 2)       join qai.dbo.wsndata d on d.code = m.vencode                             , d.item = 5140                           --and d.freq = 'a'                             , d.year_ = @year_       join (select code, sum(value_) avgprice               qai.dbo.wsndata d2              year_ between @year_-4 , @year_                , item = 5001                , freq = 'a'           group code) d2 on d2.code = m.vencode 

the thing jumps out @ me without seeing execution plan is:

join qai.dbo.secmstr s on s.cusip = substring(b.cusip,1,8) 

on 4th line. guess use of substring blowing index usage particular piece. believe means performance hit of having update indexes when update records, won't performance gain of index usage when trying find records (assuming have indexes).

kind of "worst of both worlds" situation.