[T-SQL] How to boost 'joined' Update Performance

Time for some real boring stuff.

The first thing people are tempted to write when they have to run an update on a table picking stuff from another table is probably something like this:

--method 1: nested select
--around 15 mins to execute on 100.000 records
UPDATE myTable
SET myTable_field = (SELECT myOtherTable_field
                   FROM myOtherTable
                   WHERE myOtherTable_someID = myTable_someID)

This way the nested select will run once for each record you're updating (100k times in my example) and you'll get shit performance.

Alternative to this are:

--method 2: update FROM more than one table
--around 45 secs to execute on 100.000 records
UPDATE myTable
SET myTable_field = b.cache_VmiID
FROM myTable a, myOtherTable b
WHERE b.myOtherTable_someID = a.myTable_someID

or, more intuitive:

--method 3: JOINED UPDATE
--42 secs to execute on 100.000 records
UPDATE myTable
SET a.myTable_field = b.myOtherTable_field
FROM myTable a join myOtherTable b
on b.myOtherTable_someID = a.myTable_someID

Execution times assume you're clearing SQLServer cache with fucking DROPCLEANBUFFERS (without clearing cache method 2 and 3 take about 4-5 secs).