In a test case, the temp table has for example 7500 rows, but: Now i see that there is no 1 to 1 matching, not in the original update and not in the inner join in my example because the join (either explicit or implicit) hasn't all the necessary fields to create a 1 to 1 match between tables, result: 55 million records are checked againt these WHERE conditions, it's like when joinning tables the wrong way you usually get all the rows and: 7500 * 7500 = 56.25 million, but it is ok in this case, the programmer that did this in the past wanted to do it like this and the thing is working fine because that UPDATE in my first post is working like if it were 2 nested Cursors (or 1 + more code inside) making conditional accumulative operations inside, now the question is how to make this faster? Or even better what could be happening in that client that's making this process take 4 or 5 hours (I tested this with many records and it never took more than 3 minutes). These are temp tables so it cannot be a dropped index o something like that.

Now i think if actually using real cursors won't be faster than this, i don't think it will but i think i'll test anyway.