求下面这段SQL语句的优化

 update #temp set 
    res  = 
    (
        select top 1 [Desc] from Response nolock 
        where req_no = 
        (
            select top 1 req_no from Request nolock 
            where id = #temp.id
            order by update_date desc
        ) order by update_date desc
    ),
    [pro_status] = 
    (
        select top 1 [Status] from Response nolock 
        where req_no = 
        (
            select top 1 req_no from Request nolock 
            where id = #temp.id
            order by update_date desc
        ) order by update_date desc
    )

贴出所有表的表结构吧， 这样不好弄