Improve performance of cascading update statements?

I'm trying to populate a table I've created with fundamental data present in a different table.

I have a number of IDs present on which to join, but for a number of reasons, no single ID will work for all records. As such, I've set up a series of cascading UPDATE statements: inner join first using ID1 to the fundamental table and populate table for records present; inner join next using ID2 to the fundamental table for records not previously populated, again populating table for records with a match; etc.

This allows me to get a match for pretty close to all of the records present in my initial table (~99.5% have a match after the cascading updates, where at most 78% have a match with any single ID), which is great. Problem is I need to do this to populate a number of different fields in my table and processing gets sluggish as I repeat the procedure for each data item.

1. Is there a better way to achieve my objective than using cascading updates? Perhaps something akin to the COALESCE function that I'm simply not aware of (COALESCE won't work, b开发者_如何学Cut something similarly iterative in nature)?

2. If not, within the query below, is there any obvious way to speed this up?

   UPDATE #temp
      SET Avg_DivYld_5yr = d.value_ / AvgPrice
         FROM #temp b
         JOIN qai.dbo.secmstr s ON s.cusip = substring(b.cusip,1,8)
          --on s.sedol = substring(b.sedolcode,1,6)
                               AND b.Avg_DivYld_5yr IS NULL
         JOIN qai.dbo.secmap m ON m.seccode = s.seccode 
                              AND m.ventype = 7
                              AND ((m.exchange = 0 AND m.rank = 1) OR m.exchange = 2)
         JOIN qai.dbo.wsndata d ON d.code = m.vencode
                               AND d.item = 5140
                             --and d.freq = 'A'
                               AND d.year_ = @year_
         JOIN (SELECT code, SUM(value_) AS AvgPrice
                 FROM qai.dbo.wsndata d2
                WHERE year_ BETWEEN @year_-4 and @year_
                  AND item = 5001
                  AND freq = 'A'
             GROUP BY code) d2 ON d2.code = m.vencode
   

The only thing that jumps out at me without seeing an execution plan is:

JOIN qai.dbo.secmstr s ON s.cusip = substring(b.cusip,1,8)

on your 4th line. I would guess that the use of substring is blowing your index usage for this particular piece. I believe this means you will get the performance hit of having to update your indexes when you update your records, but you won't get the performance gain of index usage when trying to find records (assuming you have indexes).

Kind of a "worst of both worlds" situation.